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:

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.)

 1Write-Host "***** Set SQL Agent Encryption Options on Target Servers *****`n "
 2
 3# Menu for SQL Server Version. SQL Server 2005 could work in theory, but
 4# it's registry values are unpredictable and I didn't want to mess.
 5
 6[int]$menuChoice = 0
 7while ( $menuChoice -lt 1 -or $menuChoice -gt 3 ){
 8Write-host "1. SQL Server 2008"
 9Write-host "2. SQL Server 2008 R2"
10Write-host "3. SQL Server 2012"
11[Int]$menuChoice = read-host "Select your SQL Server version" }
12
13Switch( $menuChoice ){
141{$SQLVersion = "10"}
152{$SQLVersion = "10_50"}
163{$SQLVersion = "11"}
17default{$SQLVersion = "10_50"}
18}
19
20# Enter the name of your SQL Server
21Write-Host "Enter the hostname of SQL Server (do not include instance name)"
22$ServerName = Read-Host "If you are using a cluster, enter the individual node name"
23$ServerName = $ServerName.ToUpper()
24
25# And the instance
26$Instance = Read-Host "Enter Instance Name (leave blank for default)"
27if (!$Instance) {$Instance = "MSSQLSERVER" }
28$Instance = $Instance.ToUpper()
29
30Write-Host "`nOptions for Encryption`n"
31[int]$menuChoice = -1
32while ( $menuChoice -lt 0 -or $menuChoice -gt 2 ){
33Write-host "0. Disables encryption between this target server and the master server."
34Write-host "1. Enables encryption only between this target server and the master server, but no certificate validation is required."
35Write-host "2. Enables full SSL encryption and certificate validation between this target server and the master server. "
36[Int]$menuChoice = read-host "Select Encryption Option for SQL Agent Master/Target Communication" }
37Switch( $menuChoice ){
380{$EncryptionOption = "0"}
391{$EncryptionOption = "1"}
402{$EncryptionOption = "2"}
41default{$EncryptionOption = "1"}
42}
43
44$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey("LocalMachine", $ServerName)
45$regKey= $reg.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL$SQLVersion.$Instance\SQLServerAgent",$true)
46
47if ($regkey -ne $null) {
48$oldValue = $regKey.GetValue("MsxEncryptChannelOptions")
49$regKey.SetValue("MsxEncryptChannelOptions","0000000$EncryptionOption",[Microsoft.Win32.RegistryValueKind]::DWORD)
50Write-Host "Done"
51$newValue = $regKey.GetValue("MsxEncryptChannelOptions")
52Write-Host "Server: $Servername`nOld value: $oldValue`nNew value: $newValue"
53} else
54{ "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.