Invoke SQL Command with Secure Creds

Question

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

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

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

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

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'

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

Any advice would be Welcome Thanks.

score 6 · Answer 1 · answered Mar 18 '16 at 21:12

6

Create a Credential object:

$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 can only use plain text passwords.

answered Mar 18 '16 at 21:12

Valeriy Glushenkov

114
4

3

Using this approach you loose the security of the secure string – XtianGIS Jan 18 '19 at 18:10
3

Microsoft should really correct this problem and allow "Invoke-SQLCmd" to accept Secure-String object. This is ridiculous. – Eddie Kumar Nov 25 '19 at 15:21

score 1 · Answer 2 · answered Mar 16 '20 at 13:27

It is possible to use a secure string without storing the plain text in a variable. Here is an example.

$Server = Read-Host "Server"
$Database = Read-Host "Database"
$Username = Read-Host "User"
$Password = Read-Host "password for user $Username on $Server" -AsSecureString

Invoke-Sqlcmd -Database $Database -ServerInstance $Server -Verbose -Username $Username -Password (New-Object PSCredential "userDummy", $Password).GetNetworkCredential().Password -Query "SELECT table_catalog [database], table_schema [schema], table_name name, table_type type FROM INFORMATION_SCHEMA.TABLES GO"

Could this be done if you're splatting Invoke-Sqlcmd? – John Jun 17 '21 at 19:22 — John, Jun 17 '21 at 19:22

Invoke SQL Command with Secure Creds

2 Answers2

Linked