3

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.

3

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.

3
  • Just to add, if you can encrypt the string from the service account you don't need a key, calling that Cmdlet without arguments uses the native Windows Data Protection API (DAPI) so that only the original user on the original machine can decrypt the string
    – Tom V
    Sep 27 '16 at 12:37
  • @TomV That is an option but would require you to login as the service account (interactive) in order to do that, which in most cases service accounts shouldn't (or are not) allowed interactive login to the server.
    – user507
    Sep 27 '16 at 13:54
  • or encrypt it through an agent job and then delete that job
    – Tom V
    Sep 27 '16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.