How to Use an Encrypted Password Within a SQL Agent job to Access a Fileshare in an Untrusted Domain

Scenario

You have a SQL Server Agent job which needs to regularly access a remote file share in an untrusted domain. This requires you to provide the remote username and password while accessing the remote share. In addition, you do not wish to store the password of the remote share account in clear text.

Solution

• First, transform the clear text password into an encrypted string and store that string inside a SQL table. Note convertto/from-securestring uses DPAPI so it uses the USER and MACHINE  dependent key if a key is not specified.  You should encrypt and decrypt the password on the same machine and with the same credentials  or designate a specific key (beyond the scope of this article):
  • https://blogs.msdn.microsoft.com/timid/2014/07/21/storing-securestrings-machine-independently/
  • https://blogs.msdn.microsoft.com/sergey\_babkins\_blog/2015/11/06/certificates-part-3-encryption-and-decryption-by-hand-and-securestring/
  • https://msdn.microsoft.com/en-us/library/ms995355.aspx
  • https://powershell.org/2014/02/01/revisited-powershell-and-encryption/
• The SQL Agent PowerShell job step can now query the encrypted password string from SQL, create a PSCredential object using this password and then use this credential object to authenticate to the remote UNC share.
• Reference:
  • https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.security/convertto-securestring
  • https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.security/convertfrom-securestring

Sample PowerShell Script

#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------

#Part 1: Save the encrypted password to a SQL Server table. This needs to run ONCE.

# Two main ways to achieve this:

1) Designate a specific key during encryption and use the same key for decryption (out of scope of this article)

-or-

2)  Run on the same machine where SQL server resides and use the same credentials as the job in Part2. If you are unable to log on to the machine directly using those credentials,  you can create a temporary PowerShell subsystem job to achieve this. Don't forget to go back and delete this jobstep since it only needs to run once and it has the clear text password.

#Convert the clear text password to a securestring object
$secPwd='ENTER CLEAR TEXT PASSWORD HERE' | ConvertTo-SecureString -AsPlainText -Force

#Convert from a securestring object to an encrypted password string
$secPwdStr= $secpwd | convertfrom-securestring

#Insert the encrypted string into a SQL Server table
$qry="create table testPwdTable (testPwdColumn varchar(max));insert into testPwdTable values (`'$secPwdStr`')"
invoke-sqlcmd -ServerInstance . -Database testPwdConfigDB -Query $qry
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#-------------------------------------------------------------------------
#Part 2:  Authenticate to the UNC share using previously stored encrypted password. Run this within the SQL Agent PowerShell subsystem job step. If the password string was encrypted on a different machine or with different credentials you will need to specify a specific key during decryption.
$UserName='ENTER_REMOTE_DOMAIN_HERE\ENTER_REMOTE_USERNAME_HERE'

#Query the SQL Server table to get the saved encrypted password string
#Note ExpandProperty is used to get the raw string testPwdColumn value (instead of a sql column object)
$qry="select top 1 testPwdColumn from testPwdTable"
$secPwdStr= invoke-sqlcmd -ServerInstance . -Database testPwdConfigDB -Query $qry | select -ExpandProperty testPwdColumn

#Convert the encrypted password string to a SecureString object
$secpwd= $secPwdStr | ConvertTo-SecureString

#Create a PowerShell credential object with this username + password(securestring object)
$cr = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $UserName , $secpwd

#Create a mapped drive to the remote share, pass the credential object to the New-PSDrive commandlet
if (-not (test-path "y:"))
{
write-output ("Creating mapped drive y:")
New-PSDrive -Name y -PSProvider FileSystem -Root '\\ENTER_IP_ADDRESS_HERE\SQLBACKUPFOLDER' -Credential $cr -Description "Log Shipping source share"
}

$r=test-path "y:"
write-output ("Path status is " + $r)
#Expect a value of true to be returned when looking at the job history

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services