Safely Enable SQL Server Agent MultiServer Administration using PowerShell

Update: You can’t even independently schedule slave jobs. Count my organization as yet another that won’t be implementing MultiServer Administration. Grrr.

I always forget about Multiserver Administration. I’ve actually never worked in an environment that uses it, even though it seems to have a lot of potential. I think one of the biggest reasons is that most organizations do not use SSL encryption between SQL Servers, yet out of the box, Multiserver Administration requires SSL encryption for communication between the master and the targets.

Want to change this option? You’ll have to modify the registry. Come on, Microsoft: nobody wants to touch a production SQL Server’s registry and I think this is the biggest roadblock to mass adoption of Multiserver Administration.

The registry subkey that needs to be changed is MsxEncryptChannelOptions. There isn’t a whole lot of information about this subkey (such as what other services it impacts) but I’m hoping that since its prefixed with “Msx” that and sits in the SQLAgent key, the change will be isolated to Multiserver Administration. So here are the 3 options:

0 Disables encryption between this target server and the master server. Choose this option only when the channel between the target server and master server is secured by another means.
1 Enables encryption only between this target server and the master server, but no certificate validation is required.
2 Enables full SSL encryption and certificate validation between this target server and the master server. This setting is the default.


Like the table says, 2 (Encryption+SSL required) is the default. Most blogs I’ve seen change their option to 0 (No encryption), but I tested it with 1 (Encryption enabled+ SSL not required) and default out of the box SQL encryption settings and it worked. Microsoft says this about the default encryption:

Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.

I always prefer encryption if it’s not disruptive, so this is the setting I will recommend, and the setting that is default in the script below. This script asks for the SQL Server version (SQL2k5 is not supported because I no longer use it and instance paths are more challenging.)

Write-Host "***** Set SQL Agent Encryption Options on Target Servers *****`n "

# Menu for SQL Server Version. SQL Server 2005 could work in theory, but
# it's registry values are unpredictable and I didn't want to mess.

[int]$menuChoice = 0
while ( $menuChoice -lt 1 -or $menuChoice -gt 3 ){
Write-host "1. SQL Server 2008"
Write-host "2. SQL Server 2008 R2"
Write-host "3. SQL Server 2012"
[Int]$menuChoice = read-host "Select your SQL Server version" }

Switch( $menuChoice ){
1{$SQLVersion = "10"}
2{$SQLVersion = "10_50"}
3{$SQLVersion = "11"}
default{$SQLVersion = "10_50"}

# Enter the name of your SQL Server
Write-Host "Enter the hostname of SQL Server (do not include instance name)"
$ServerName = Read-Host "If you are using a cluster, enter the individual node name"
$ServerName = $ServerName.ToUpper()

# And the instance
$Instance = Read-Host "Enter Instance Name (leave blank for default)"
if (!$Instance) {$Instance = "MSSQLSERVER" }
$Instance = $Instance.ToUpper()

Write-Host "`nOptions for Encryption`n"
[int]$menuChoice = -1
while ( $menuChoice -lt 0 -or $menuChoice -gt 2 ){
Write-host "0. Disables encryption between this target server and the master server."
Write-host "1. Enables encryption only between this target server and the master server, but no certificate validation is required."
Write-host "2. Enables full SSL encryption and certificate validation between this target server and the master server. "
[Int]$menuChoice = read-host "Select Encryption Option for SQL Agent Master/Target Communication" }
Switch( $menuChoice ){
0{$EncryptionOption = "0"}
1{$EncryptionOption = "1"}
2{$EncryptionOption = "2"}
default{$EncryptionOption = "1"}

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $ServerName)
$regKey= $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$SQLVersion.$Instance\SQLServerAgent",$true)

if ($regkey -ne $null) {
$oldValue = $regKey.GetValue("MsxEncryptChannelOptions")
Write-Host "Done"
$newValue = $regKey.GetValue("MsxEncryptChannelOptions")
Write-Host "Server: $Servername`nOld value: $oldValue`nNew value: $newValue"
} else
{ "No match. Make sure you typed in the proper hostname and instance name." }

(Thanks to quickclix for the easy PowerShell menu code.)

Once you’ve run this script and modified the settings on your Target servers, you can easily setup Multiserver Administration. Note that the default setting may create a SQL Server login for the target server automatically. I uncheck that option because I’m trying to get away from local SQL Server logins and all of my SQL Agents run under the same domain account anyway.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server
5 comments on “Safely Enable SQL Server Agent MultiServer Administration using PowerShell
  1. Suzanne says:

    I am in the process of trying to setup a Multiserver administration, but the thing that keeps giving me questions is this registry key. I am hesitant to change it, because there is so little documentation about it. I've made a support request to Microsoft and they say that within normal environments, in a secured network, it should not be a problem. I'll probably dig a little further to find if it impacts anything outside MSX communication and if I don't find anything, I will probably change it. Thanks for posting.

  2. Suzanne says:

    According to Microsoft, this doesn't impact anything outside MSX, so that's good news. I am currently investigating wether the certificates that are used are safe enought. So, still to be continued. Will let you know though!

    • Thanks for the info :) I ended up not using it, unfortunately, because you can't schedule the target jobs independently. I was attempting to consolidate my backup maintenance so all of my backups would have to run at once. Boo.

  3. KC says:

    Chrissy we use a multi-server job to do our backups. The job runs hourly and the first thing it does is check a table on our CMS server to see if its that servers time to backup. Allows us to use one job to perform global backups staggered across time as best fits the users needs.

Leave a Reply