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

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

Posted in PowerShell, SQL Server
4 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.

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">