使用安全凭证调用 SQL 命令 [英] Invoke SQL Command with Secure Creds

问题描述

我对如何在 Powershell 命令中使用安全字符串来运行远程 SQL 查询有点困惑.

A little stuck on how I use a secure string within a Powershell command to run a remote SQL Query.

以下命令在 powershell 中运行良好，因为它是在我自己的帐户下运行的 - 在提供用户名和密码的值时它也会返回结果.

The following command run's fine in a powershell, as this is running under my own account - it also returns results when providing the values for username and password.

 "SELECT COUNT(E.intEmployeeID) AS Count FROM Employees E WITH(NOLOCK)" -       ServerInstance "SERVERA\INSTANCEA" -Database "DATABASEA" -u USER1 -p SomePassword

我想自动化/安排这个脚本，因为我不想在我的脚本中使用 clear txt 中的密码，我正在寻找使它成为安全/加密字符串的方法.所以我使用以下创建了一个加密密码.问题是我不知道如何将此密码传回我的命令中..

I want to automate/schedule this script and as I don't want the password in clear txt in my script, I was looking at ways of making this a secure/encrypted string. So I have created an encrypted password using the below. The problem is I'm not sure how to pass this password back into my Command..

这将创建加密字符串并存储在文件中.这将是一个远程保护的文件.

This creates the encrypted string and stores in a file. This will be a file secured somewhere remotely.

$File = "C:\password.txt"
[Byte[]] $Key = (1..16)
$Password = "SomePassword" | ConvertTo-SecureString -AsPlainText -Force
$Password | ConvertFrom-SecureString -key $Key | Out-File $File

这将读取加密文件并存储在安全字符串中...但是如何让我的 Invoke SQL 命令使用此密码.

This then will read the encrypted file and store in secure string... But how do I get my Invoke SQL command to use this password.

$File = "C:\Cred.txt"
[Byte[]] $Key = (1..16)
$Password = Get-Content $File | ConvertTo-SecureString -Key $Key

$Password 的值是 System.Security.SecureString，如果我在原始命令中使用这个变量，命令会失败并显示用户登录失败"

The value for $Password is System.Security.SecureString, if I use this variable in the original command, the command fails with 'Login Failed for User'

用于执行 SQL 查询的帐户是 SQL 身份验证帐户，而不是域帐户..

The account being used to perform the SQL query is a SQL Authenticated account, not a Domain account..

欢迎任何建议谢谢.

推荐答案

创建凭据对象:

$cred = new-object -typeName System.Management.Automation.PSCredential -ArgumentList $user, $pass

然后，将密码转换为纯文本:

Then, convert password to plain text:

[string]$pass = $cred.GetNetworkCredential().Password
invoke-sqlcmd -UserName $user -Password $pass -Query 'select @@servername'

Ivoke-SqlCmd 只能使用纯文本密码.

Ivoke-SqlCmd can only use plain text passwords.

这篇关于使用安全凭证调用 SQL 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！