Unable to Add SQL 2005 Target Servers

Feb 16, 2012 at 4:20 PM
Edited Feb 16, 2012 at 4:24 PM

I have SQL 2008 and SQL 2005 servers that I'm trying to monitor with CMS.  Due to Corporate Policies, my SQL 2005 installations are owned by "svcSQL_2005" and my SQL 2008 installations are owned by "svcSQL_2008".  When I went in to SQL Server Agent > Multi Server Administration and tried to add my development SQL 2005 server, it failed with:

MSX enlist failed for Job Server <server_name>
An exception occurred while executing a Transact-SQL statement or batch 
(Microsoft.SqlServer.ConnectionInfo) The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server <server_name>.  Make sure that the MsxEncryptedChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server Error 22026)

Some searching found http://sqlship.wordpress.com/category/multi-server-administration/ which recommends updating \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent\MsxEncryptChannelOptions(REG_DWORD) on both the CMS server and target machine to 0 (zero).  I tried this but got

MSX enlist failed for Job Server <server_name>
An exception occurred while executing a Transact-SQL statement or batch 
(Microsoft.SqlServer.ConnectionInfo) The enlist operation failed (reason: SQLServerAgent Error: Unable to conenct to MSX
<server_name>) (Microsoft SQL Server Error 22026)

Further reasearch led to http://connect.microsoft.com/SQLServer/feedback/details/207438/msx-enlist-failed-for-jobserver-servername#details which shows that the registry entry has the following values:

MsxencryptChannelOptions = 0 : No encryption, certificate required
MsxencryptChannelOptions = 1: Encryption required but no certificate validation involved.
MsxencryptChannelOptions = 2: Encryption required along with certificate validation.

As far as I know, neither server has a certificate so I tried setting the registry entry to 1 (one) but now the attempt to add the 2005 server to my 2008 CMS fails with the following error:

MSX enlist failed for Job Server <server_name>
An exception occurred while executing a Transact-SQL statement or batch 
(Microsoft.SqlServer.ConnectionInfo) The enlist operation failed (reason: The time-out was exceeded while the server waited for a response from SQL Server Agent. Make sure that the SQL Server Agent service is running) (Microsoft SQL Server Error 22026)

My SQL Server Agent is running on my SQL 2005 server and various jobs run successfully every day so I know it's working.  It appears to me that there's some sort of Microsoft voodoo that I'm missing.  Does anyone have any recommendations?

Feb 16, 2012 at 4:40 PM

I found this Microsoft link:

http://msdn.microsoft.com/en-us/library/ms366280.aspx

That says the owner of the SQL Server Agent service should be a member of the local administrators group.  This contradicts what I read earlier about restricting the privileges of the SQL Server account.  Can anyone tell me if their SQL Server account has local administrator privilege?

Feb 16, 2012 at 5:45 PM

I opened up a ticket with MS Premier Support asking if changing my configuration so that all SQL Servers run under the same account will fix this issue.  As this will require a significant outage to implement, I wanted some assurance this change will fix the problem before I go through the trouble (and expense) of making this change.