How to automate creating credentials in a secure way

Question

We have created an automated process to stand up a new SQL Server using TFS and powershell. As part of a new server build, we deploy a database and a set of jobs for dba use. As part of the install we need to create a credential for a sql agent proxy for some of our jobs. However, we don't want to store our passwords in code.

Is there a solution to storing passwords (or generating a password) where we aren't storing it in plain text or in line in the code?

Our servers are on-prim, not in Azure. We have access to Keepass, but that is being deprecated for Thycotic.

Answer 1

You can leverage PowerShell and the Thycotic REST API to fetch your credentials from Secret Server programmatically. Use "Windows Authentication" to avoid having to store credentials in the PowerShell scripts, or enter them during runtime.

If your deployments run under a build agent service account context, then you need to ensure that service account has access in Secret Server to the credentials you want to access.

Leveraging the API as a step in your release prior to deploying the SQL credential, you can fetch the credential and pass it to the PowerShell script that deploys the credential.

Answer 2

Try this:

$cred = get-credential
$cred | Export-CliXml -Path C:\Temp\Mycreds.xml

Get-Credential will prompt for the username & password & the Export-CliXml will export the results to an XML file. If you look at the username it will be in plaintext but the password will be encrypted.

When the username & password is needed, import the XML:

$cred = Import-CliXml -Path "C:\Temp\Mycreds.xml"

Then in your connection string you can use the username & password like this.

-Username $cred.GetNetworkCredential().Username 
-Password $cred.GetNetworkCredential().Password 

Another hacky, much less secure way to do this would be to write a secure string to a file.

## Create the secure string password file
$Sec = Read-Host -AsSecureString
$Enc = ConvertFrom-SecureString -SecureString $Sec -Key (1..16)
$Enc | Set-Content C:\Temp\Encrypted.txt
## When you need to use it...
$Sec2 = Get-Content C:\Temp\Encrypted.txt | ConvertTo-SecureString -Key (1..16)
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((($Sec2))))

if you leave the file extensions off (Encrypted.txt would be Encrypted), anyone browsing the folder the file is in wouldn't know it was a regular text file.

But this is really security through obscurity, anyone that can read the script could use what you are doing and collect the password.

• DForck42: So I read into export-clixml, and it says only I can use the XML on the same machine I created it on. There are 6 people on my team who would use this. Is there a workaround for that?
• DBADon: In your post you said you'd "create a credential for a sales agent proxy". Will not run under that account? Ideally you would setup a scheduled task with the "Run As" being that account. I'll add a hacky second option as well.