One of the struggles I have with automating things with PowerShell is securely managing the passwords. Using Windows credentials from the service/automation account is probably the best option, but not always possible so the question becomes how to securely store the password somewhere that the script can use it but it's generally secure from anyone discovering the script.
The most practical way I've discovered so far is to encrypt the password using the account that will run the automation on the server where the automation will run, and then save this encrypted password in a config file that the script will read. For jobs run by the SQL Agent this would mean encrypting the password using the SQL Agent service account on the SQL Server.
Encrypting the Password
The password gets encrypted using the following basic format
Read-Host | ConvertTo-SeccureString -AsPlainText -Force | ConvertFrom-SecureString
instead of using Read-Host to read the string from the console you can also hard code the password into the command, e.g.
'password' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString
or read the password into a variable and then convert, e.g.
$pwd = Get-Content c:\temp\test.txt
$pwd | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString
The resulting long string of gibberish can be added into a file to be read in by the actual script at runtime.
Decrypting the Password
In order to use the password it has to be decrypted by the same user on the same machine as where the encryption was done. Using a different user or different machine will result in the password not being decrypted correctly.
Decrypting is a bit more complex, as follows
$password = (Get-Content $SecretFile) | ConvertTo-SecureString
$decrypted = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))
The first line reads in the encrypted password from the path in the variable $SecretFile, e.g. c:\temp\test_encrypted.txt and converts it back into a PowerShell SecureString object.
The next line uses the .Net Marshal class functions to convert the secure string object back into plain text.
Final Thoughts
This isn't perfect, as anyone that can access the server and user can decrypt the password so maintaining good security around admin permissions on servers and well protected service accounts is a must. But it's far better than having plain text passwords lying around in scripts.
