Using Secure Connections With SQLPS in Powershell

Quick note on how to use secure connections for Sql Server Authentication with SQLPS.  First step, you’ll need to create a text file to hold your encrypted password.  In this case, it’s just encrypted using the user key of the current logged in user (Windows Data Protection API).  Keep this in mind, as your sql server agent will (hopefully) be running under a different account, you’ll need to encrypt the file using that account if you’re planning on calling this via sql jobs.  Only the account that created this file will be able to decrypt and read it.

read-host -assecurestring | convertfrom-securestring | out-file C:\Powershell\Password.txt -Force

Next, you’ll need to create a ServerConnection object and pass in your credentials to it.  One caveat to note, is that you don’t pass in your encrypted password to the Password property, you have to pass it to the SercurePassword property of the ServerConnection.  The Password takes just a regular string as a parameter, whereas the SercurePassword property takes a securestring. 

Import-Module SQLPS -DisableNameChecking

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-Null

$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
$srvConn.ServerInstance = $InstanceName
$srvConn.Login = "LoginName"
$srvConn.SecurePassword = get-content "c:\Powershell\Password.txt" | convertto-securestring 

$RepInstanceObject = New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $srvConn
$RepInstanceStatus = New-Object "Microsoft.SqlServer.Replication.ReplicationMonitor" $srvConn

In this case I’m using the connection to connect to RMO for a script for monitoring replication, but you can use this same method to connect to anything that accepts a ServerConnection.