SQL Server 2005: Troubleshooting Multi Server Administration (MSX)

In this week's course 2780 - Maintaining a Microsoft SQL Server 2005 Database - I had some difficulty demonstrating the setup of multiserver administration (MSX) between the 2 instances I had running inside my virtual machine. I checked:

• Service account privileges - I had the same account used for both instances
• Account startup properties - both Agents set to autostart
• Compatible network protocols enabled (to be safe, I enabled TCP/IP and named pipes even though it was all running on the same VM)

But I kept getting an SMO error from Management Studio that it couldn't connect to the instance marked to become the master when attempting to enlist the target.

Some research led me to Books Online: Setting Encryption Options on Target Servers, which I found allowed me to disable encryption support for SQL Agent in the registry:

To configure the appropriate level of security required for a specific master server/target server communication channel, set the SQL Server Agent registry subkey \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQLServerAgent\MsxEncryptChannelOptions(REG_DWORD) on the target server to one of the following values. The value of <instance_name> is MSSQL.n. For example, MSSQL.1 or MSSQL.3.

Value	Description
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. Unless you have specific reason to choose a different value, we recommend not changing it.

The default value on these instances was 2, which supported SSL. After editing this subkey for each instance to 0m (in my case MSSQL.1 and MSSQL.4), and restarting the Agents, I was able to successfully create a master server and enlist a target.

The key here (no pun intended) for me was this snippet:

If 1 or 2 is specified, you must have SSL enabled on both the master and target servers. If 2 is specified, you must also have a properly signed certificate present on the master server. For more information about enabling SSL for SQL Server, see Encrypting Connections to SQL Server.

My classroom demo machine does not have SSL enabled and configured. This hasn't been an issue in test/production as those machines have been better secured.

Some further digging on the MSDN forums  reveals that MS are aware that this encryption option should be better exposed in the SSMS UI and in SMO, and "We have a tracking bug for the next release to expose it at SMO and UI level."

Comments

[Bobhen]

Looks like this hasn't been fixed at all in SQL 2008 :(

[nwebsolution]

i am really impressed with the required in the total amount of services which is really helpful for query language,thanks.