Encryption and Decryption of Passwords in SQL Server with PowerShell

By: Jeffrey Yao | Updated: 2021-02-25 | Comments (5) | Related: > Security

Problem

I am working on a project to replace some SQL Server Integration (SSIS) packages with PowerShell scripts (PS) as these SSIS packages basically read from some tables (including SQL Server and MySQL databases), do some transformations and export to CSV files. Some of the connections inside these SSIS packages use the typical User Name and Password pattern to access the database servers. I do not want to have the password in clear text appear in my PowerShell script, what can I do?

Solution

A typical .Net framework connection string can be like the following:

Server=myServerInstance;Database=myDatabase;User Id=myUsername;Password=myPassword;

The challenge here is that we cannot use an encrypted password in the connection string, meaning in the PS script when I compose the connection string, I have to use the plain text value of the password. But I do not want to show the password value in the PS script as the script may be reviewed by many people.

A common sense solution is to encrypt the password, and save the encrypted value somewhere, like in a file, in the registry or in a table. Then inside the PS script, we retrieve the encrypted value, decrypt it and put the value into the connection string. However, the concern here is, if another person, who can access the script, does a debug of the PS script line by line, this person can print out the connection string and thus retrieve the password.

In this tip, I will demonstrate a solution that will meet the following requirements

The password does not use a clear text value in the PS script.
The password is encrypted (assuming by my Windows account) and the value is stored in a SQL Server table.
Another person who has my PS script source code cannot reveal the password via debugging.
Even if my account is hacked and the hacker gets the PS source script, the hacker still cannot reveal the password via debugging unless the hacker happens to be on the same computer where I initially encrypted the password.

Let’s explore the solution now, we will assume that the PowerShell sqlserver module and PowerShell V5+ are installed.

String Encryption and Decryption Review

The key part of the solution is encryption and decryption, so we need to review this first.

In PowerShell, there are two key cmdlets that will be used in our solution

ConvertTo-SecureString: can convert plain text to a secure string. Here "secure string" is actually a .Net object of type System.Security.SecureString. Once the plain text is converted to a "secure string", the secure string exists in memory, however, we need to "harden" the object so we can port it somewhere else to be used. Fortunately, we can convert it back to an encrypted string (i.e. a .Net object of type System.String) by using the following cmdlet.
ConvertFrom-SecureString: converts a secure string to an encrypted string of System.String type, and so this string can be saved into a text file or any other way that a string can be saved (like in a table or in the registry) and be ported to other places.

With these two cmdlets, we will be able to encrypt a plain text string to an encrypted string and port it via a text file.

We can then decrypt this encrypted string back to the original plain text by using the following .Net function as shown below.

$p1 = ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString;
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($($p1 | Convertto-SecureString)));

Secure String Features

Let’s review some features of the secure string object and this will help us better understand the solution logic.

We will run the following PowerShell code.

ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString;

1. Each time we run the code, a different value will be generated. For example, if I run the same code twice:

"1st run"
ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString; 
"2nd run"
ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString;

We get the following and we can clearly see the value is different for each run. I highlighted just the last few characters to show the difference.

Different values with the same source string "hello world"

2. Even though the values are different, they can be decrypted to the same original string, i.e. "hello world".

"first run";
$p1 = ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString;
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($($p1 | Convertto-SecureString)))
 
"second run";
$p2 = ConvertTo-SecureString -String "hello world" -AsPlainText -Force | ConvertFrom-SecureString;
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($($p2| Convertto-SecureString)))

two runs with different encrypted strings yet still render the same original text

3. We can save the encrypted string converted from the secure string object into a text file and later read this text file and convert it back to a secure string. We can demonstrate this feature with the following code

#save secure string info into a file 
ConvertTo-SecureString -String "hello world" -AsPlainText -Force | 
ConvertFrom-SecureString | 
Out-File -FilePath "c:\temp\secure_str.txt" -Encoding ascii -Force;
 
#read the file and convert it back to a secure string
$secure_str = gc -Path "c:\temp\secure_str.txt";
 
#decrypt the original text, i.e. "hello world"
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::
SecureStringToBSTR($($secure_str| Convertto-SecureString)));

4. For decryption, the secure string file, i.e. "c:\temp\secure_str.txt" can only be processed by the account that creates it and on the same computer where the secure string is created, i.e. the decryption will fail if the decryption process is on other computers or the decryption is done by any accounts other than my own (which initially created the secure string file).

We can easily do a demo to prove this, as follows:

Using the previous script, I can create a secure string repository file "c:\temp\secure_str.txt" on computer [A] with my domain account [domain\x].
Copy this "secure_str.txt" to another computer [B] into B’s local "c:\temp\" folder.
Using account [domain\x] to log onto computer [B], run the decryption script and check the result.

#read the file and convert it back to a secure string
$secure_str = gc -Path "c:\temp\secure_str.txt";
 
#decrypt the original text, i.e. "hello world"
[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::
SecureStringToBSTR($($secure_str| Convertto-SecureString)));

Using another account [domain\y] and log on to computer [A] and run the same decryption script and check the result.

In both cases (iii and iv), we will get the following error:

When handling secure string repository file not by the same account that created the file or not on the same computer where the repository file was generated, the file cannot be decrypted

Save Secure String DBA Way

If we have multiple secure strings to be saved, it may be not very convenient to save them into repository files. Instead, it may be better to save them to a table with proper columns indicating the two key properties of this secure string, i.e. by which account and from which computer the secure string is generated.

We will create the following table.

USE MSSQLTips;
go

drop table if exists [UserEncryptedPwd];
go

CREATE TABLE [dbo].[UserEncryptedPwd] (
   [UserName] varchar(30) 
  ,[EncryptedPWD] varchar(2000) -- secure string for the password
  ,[Creator] varchar(128) -- who created the secure string for the password
  ,[HostComputer] varchar(128) -- where the secure string was created
  , ID int identity primary key );

This table will save a user name and the corresponding password, which is encrypted into a secure string.

The beauty here is even if a DBA can query the table, the DBA cannot decrypt the password unless the DBA is the creator of the password secure string and the DBA tries to decrypt the password from the computer (as defined in [HostSvr] column).

We can use the following code to populate the table (we assume the PowerShell SQLServer module is installed).

#assume sqlserver PS module is installed
import-module sqlserver;
 
$db = 'mssqltips'; # the db hosting dbo.[UserEncryptedPwd]
$svr = 'localhost\sql2017' # the sql instance hosting database $db
$username = 'ABC'; #replace per your requirement
$userPwd ='hello world' # replace per your requirement
 
$pwd_ss = ConvertTo-SecureString -String $userPwd -AsPlainText -Force|ConvertFrom-SecureString;
 
$t = new-object System.Data.DataTable;
$col = new-object System.Data.DataColumn ('UserName', [system.string]);
$t.Columns.add($col);
$col = new-object System.Data.DataColumn ('EncryptedPwd', [system.string]);
$t.Columns.add($col);
$col = new-object System.Data.DataColumn ('Creator', [system.string]);
$t.Columns.add($col);
$col = new-object System.Data.DataColumn ('HostComputer', [system.string]);
$t.Columns.add($col);
$record = $t.NewRow();
$record.UserName = 'ABC'
$record.EncryptedPwd = $pwd_ss;
$record.Creator = $env:UserName;
$record.HostComputer = $env:COMPUTERNAME;
$t.Rows.add($record);
Write-SqlTableData -ServerInstance $svr -DatabaseName $db -SchemaName dbo -TableName 'UserEncryptedPwd' -InputData $t;

Most of time, we need to schedule and run some SQL Server Agent jobs in which we need to connect to other source data via user / password credentials and since the job is usually run under the SQL Server Agent service account, we can save the code into a PS file (let’s say c:\temp\insert_credential.ps), create a SQL job and run the PS file, like the following.

Then just run this job, it will create a record into the dbo.UserEncryptedPwd table, and you can see the [Creator] is the SQL Server Agent service account, i.e. SQLAgent$SQL2017.

With this encrypted password in the table, if we have any PS script that needs to retrieve the password of this user [ABC] for a connection, we can easily decrypt the password as long as the script is to be run by the [Creator] on the [HostComputer].

To decrypt it, we can use the following simple code to do it.

#assume sqlserver PS module is installed
import-module sqlserver;
 
$db = 'mssqltips'; # the db hosting dbo.[UserEncryptedPwd]
$svr = 'localhost\sql2017' # the sql instance hosting database $db
$username = 'ABC'; #replace per your requirement
$qry = @"
select EncryptedPwd from dbo.UserEncryptedPwd
where UserName='$($username)'
and Creator = '$($env:USERNAME)'
and HostComputer = '$($env:COMPUTERNAME)'
"@;
 
$rslt = invoke-sqlcmd -ServerInstance $svr -Database $db -Query $qry -OutputAs DataRows;
$secure_str = $rslt.EncryptedPwd;
 
#decrypt the original text, i.e. "hello world"
$orig_pwd=[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::
SecureStringToBSTR($($secure_str| Convertto-SecureString))); 
 
#for verification
write-host "The original password for user [$username] is: [$orig_pwd]";

Since the original password was encrypted by the SQL Server Agent service account, I need to save the script into a PS file and then create a SQL job and run the job to decrypt it (similar to the encryption method above). After the job runs, we can check the job history and I can see the following (which is the result of the write-host statement).

Summary

This tip provides a way to encrypt and decrypt a password, with PowerShell and SecureString object due to its unique features. This method will ensure the security of storing user / password credentials. No one can decrypt the password unless the creator’s domain account is compromised, plus the decryption process run on the same computer on which the initial encryption was done.

If we need to replace SSIS packages with PowerShell scripts, and the packages contain connection strings that need user / password credentials, using this method, we will ensure the passwords do not appear in PS script as plain text and also, more importantly, even if someone has this PowerShell script, there is no way for someone to use their own accounts to find out the passwords via debugging.

Next Steps

Actually, we can encrypt sensitive data, even a whole connection string itself. This will make the PS source code, which contains the connection string, even more "secure" from any malicious intentions.

There are other ways to store passwords for different scenarios, for example, using HashBytes() to encrypt a password, when there is no need to decrypt it. The following two tips are a good read.

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-02-25

Encrypt and Decrypt Passwords in SQL Server with PowerShell