PowerShell – Secure Strategy for Managing Login/Passwords

oracle-11g-r2powershell

I have several Powershell scripts that need to write data to an Oracle database. I do not want to hard-code the login credentials.

When connecting to SQL Server I rely on windows authentication and Active Directory, but that is not an option here because the Oracle database is on a separate network (Unix). The scripts will be executed on a SQL Server via SQL Server Agent.

I am hoping to come up with a strategy that will be secure and will minimize the work involved when it comes time to change the password.

Best Answer

Your best option would be to create the passwords in a file (or table) using ConvertTo-SecureString and ConcertFrom-SecureString. You will need to utilize the "-Key" or "-SecureKey" parameters to allow the service account with SQL Server Agent to read the password into your script.

The best write-up I've come across for this process is found here and here. Which the code snippets of interest from the articles:

#Encrypting password on machine 1
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
$Password = "P@ssword1" | ConvertTo-SecureString -AsPlainText -Force
$Password | ConvertFrom-SecureString -key $key | Out-File $File

#Accessing the password on machine 2
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
Get-Content $File | ConvertTo-SecureString -Key $key

The general premise being that you can use the first process to save your passwords to a file, or table. Then in your SQL Agent job read the password back in a semi-secure manner. It is not the most secure because if anyone finds the key, they can obviously read the password as well.

As well, in the end you are likely going to be passing it as plain text to the destination, unless it accepts use of a PSCredential object.

Related Question