PowerShell: Encrypting Password in Invoke-SQLcmd

Question

Forgive me if this is similar to another question - but I haven't found it after five days of looking so that's why I'm asking.

I don't like the idea of passing plain text passwords in my PowerShell scripts for obvious reasons, however, I have had a bear of a time finding help is encrypting my password in Invoke-SQLcmd. Can this even be done? If so, how?

Answer 1

I found a way to make it work. It's very convoluted and may have been what Andreas was trying to convey to me, but it took me a few more tries to get it to work. I've made the code generic but if it helps someone else, I'm glad to have done it.

# This creates the hashed password file and only needs to run once unless the password changes.
Read-Host -AsSecureString |ConvertFrom-SecureString |Out-File C:\directory1\directory2\hashedPasswordFile.txt

# This section reads the hashedPasswordFile.txt file from above, converts it back to plain text, and enters it into the -password parameter
$EncryptedSQLPass = Get-Content -Path "C:\directory1\directory2\hashedPasswordFile.txt"
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass)))
Invoke-Sqlcmd -ServerInstance '<connectionString,port>' -Database '<databaseName>' -Username '<userName>' -Password ([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass)))) -Query 'select <column1>, <column2> from <databaseTable> where condition = 1 order by <column1>' | Export-csv -NoTypeInformation -Path 'C:\directory\textFile.csv'

Answer 2

Hi @Michael Breen ,

Invoke-SQLcmd is supporting the -Credential parameter
https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

Maybe this helps to get it started (not tested by myself):

$Cred = Get-Credential -UserName domain\user -Message 'Enter Password'  
Invoke-Sqlcmd -Query "SELECT * FROM SomeThing" -ServerInstance "MySQLserver\MyDataBase" -Credential $Cred  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

Hi Andreas:

I read the article you listed and it didn't give much insight. I'd like not to have to manually enter the password every time I run the script. It's not going to be a daily event, but just often enough that having to go look up the password will make it inconvenient. I was thinking if there is a way to store the encrypted password in a file, then just call that file, revert the password back to its original state for the script and run the script. I will, however, noodle with your suggestion because that might be what I'm looking for.

Here is what I have so far and it works:

Invoke-Sqlcmd -ServerInstance '<connectionstring,port>' -Database '<databasename>' -Username 'Username' -Password 'complexPassword' -Query 'select <column1>, <column2> from <tableName> where <condition> = 1 order by <column1>' | Export-csv -NoTypeInformation -Path 'C:\Directory\filename.csv'

Answer 4

Hi @Michael Breen ,
instead of entering the password every time it's possible to store an aes encrypted password in a txt file.
https://www.pdq.com/blog/secure-password-with-powershell-encrypting-credentials-part-2/
In the section Creating PSCredential object you will find an example how to create a secure $MyCredential variable reading a password.txt file.

The $MyCredential can be used with the -Credential parameter of Invoke-Sqlcmd.

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 5

Hi @Michael Breen ,

please post your scripts/code as Code Sample here. The Q&A editor is cutting of characters if code is copy/pasted as "normal text".

Here I run this without an issue:

$KeyFile = ".\AES.key"  
$Key = New-Object Byte[] 16   # You can use 16, 24, or 32 for AES  
[Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($Key)  
$Key | out-file $KeyFile  
  
$PasswordFile = ".\Password.txt"  
$KeyFile = ".\AES.key"  
$Key = Get-Content $KeyFile  
$Password = "P@ssword1" | ConvertTo-SecureString -AsPlainText -Force  
$Password | ConvertFrom-SecureString -key $Key | Out-File $PasswordFile  
  
$User = "MyUserName"  
$PasswordFile = ".\Password.txt"  
$KeyFile = ".\AES.key"  
$key = Get-Content $KeyFile  
$MyCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key)  
  
$MyCredential  

You should just modify the UserName and the Password in the script above.

The next is this (you don't need the -username ..... $MyCredential contains the username and the password):

Invoke-Sqlcmd -ServerInstance '<instanceName,port' -Database '<instance>' -credential $MyCredential -Query 'select <column1>, <column2> from <tableName> where <condition> = 1 order by <column1>' | Export-csv -NoTypeInformation -Path 'C:\Directory\index.csv'  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten