Challenge
Automating SQL Server tasks with PowerShell can increase productivity and save time, but how do use PowerShell encrypt password techniques to avoid saving them as plain text. This article will show you how.
The challenge for handling SQL credentials encryption is something that often needs to be dealt with when setting up SQL Server automated PowerShell tasks. On most occasions, credentials for SQL Server or a source control connection have to be provided and by default those credentials will remain in plain text. This is a non-option for most Database or IT administrators regarding the SQL Server security.
Example
Let’s take an example script:
|
1 2 3 4 5 6 |
& "c:\program files\ApexSQL\Apexsql diff\ApexSQLDiff.com" /sourcecontrol_type1:teamfoundationserver /sourcecontrol_server1:“https://apexsqlmr.visualstudio.com/DefaultCollection” /sourcecontrol_project1:”$/ADW_v2018” /sourcecontrol_user1:"SourceControlUser" /sourcecontrol_password1:"scpass" /scr2:”$/ProjectName” /s2:machine\sqlserver /d2:Prod_db /u2:"sa" /p2:”SQLpass” /ot:html /on: "C:\Reports\Comparison.html" /f |
This simple string is set to synchronize a database located on a source control repository with a target database on some SQL Server instance by executing ApexSQL Diff, a 3rd party SQL Server change management tool through the command line interface aka CLI. As for the question of SQL Server security it can be easily concluded that there is no SQL Server security, in this case. Anyone logged into Windows session on the machine that is set for automated task can read the script and saved SQL credentials.
Resolving this SQL Server security issue can be done within the possibilities of PowerShell with a few extra steps before setting the automated task. What will be explained in this text, is how to save SQL credentials in a file in encrypted form and use them instead of plain text credentials.
It will be necessary to save a separate file per username/password in order to properly use them as SQL credentials for authentication. For storing the credential files, a designated folder location should be prepared. To save the password (or username) in encrypted form, start the PowerShell console and use the following command:
|
1 |
Read-Host -AsSecureString |ConvertFrom-SecureString |Out-File C:\Credentials\SQLPassword.txt |
When executed, the command will prompt to type in the password:
The password will be saved in the location and file designated with this command after confirmation. The content of the file will be unreadable hash string.
Repeat that action for the source control authentication password and username, if needed, but use a different name for each saved file:
|
1 |
Read-Host -AsSecureString |ConvertFrom-SecureString |Out-File C:\Credentials\SourceControlPassword.txt |
This is a one-time operation to encrypt the SQL credentials until, at least, there is a change in the used password, at which point it will have to be repeated. In that case, using the same file name to store the password will overwrite the previous one.
Reading encrypted credentials
In order to use these saved encrypted passwords, it will be necessary to read them from the saved file and convert them back to readable form for PowerShell.
Reading the password from a file can be done using following command:
|
1 |
$EncryptedSQLPass = Get-Content -Path "C:\Credentials\SQLPassword.txt" |
This will read the hashed string from the saved password file and store it in PowerShell object. The command will have to be used as preamble to main command string that executes synchronization task.
A similar approach should be taken with the saved source control connection password. Another PowerShell object will be created with this command:
|
1 |
$EncryptedSCPass = Get-Content -Path "C:\Credentials\SourceControlPassword.txt" |
Since the passwords are still in its encrypted form it will require decryption to properly use it for connection SQL.
Decryption is done with this command:
|
1 |
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass))) |
Analogous to previous example, the source control password decryption should be executed only with the second PowerShell object:
|
1 |
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSCPass))) |
Putting it all together
With this conversion passwords can be used for SQL and source control connections. Our example script, including the reading preamble, will now look like this:
|
1 2 3 4 5 6 |
$EncryptedSQLPass = Get-Content -Path "C:\Credentials\SQLPassword.txt" $EncryptedSCPass = Get-Content -Path "C:\Credentials\SourceControlPassword.txt" & "c:\program files\ApexSQL\Apexsql diff\ApexSQLDiff.com" /sourcecontrol_type1:teamfoundationserver /sourcecontrol_server1:“https://apexsqlmr.visualstudio.com/DefaultCollection” /sourcecontrol_project1:”$/ADW_v2018” /sourcecontrol_user1:"SourceControlUser" /sourcecontrol_password1: ([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSCPass)))) /scr2:”$/ProjectName” /s2:machine\sqlserver /d2:Prod_db /u2:"sa" /p2:([Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR((ConvertTo-SecureString $EncryptedSQLPass)))) /ot:html /on:"C:\Reports\Comparison.html" /f |
Note that decryption string has to be bracketed in order to be used as argument defining SQL credentials.
PowerShell encrypt password (and decrypt as well) techniques exist to allow you to safely create and run un-compiled scripts without having to worry about compromising security.
Current occupation is with ApexSQL LCC as Support Engineer helping customers with technical issues and does quality assurance for a set of SQL database continuous integration and continuous delivery solutions in a form of ApexSQL DevOps toolkit product.
View all posts by Milan Rancic
- PowerShell encrypt password techniques for SQL Server - April 18, 2019
- How to setup image based SQL Server database provisioning with PSDatabaseClone - March 22, 2019
- SQL database provisioning via Database clone using PSDatabaseClone PowerShell module - February 4, 2019