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


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.


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)


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.

#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