More master job server issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

More master job server issues

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-30 : 14:42:11

Has anyone seen this issue before?

Msg 22026, Level 16, State 1, Line 0
The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server 'MSXSERVER\INSTANCE'. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.)

I did check the registry key (on all servers) and all of them have MsxEncryptChannelOptions set to the default of 2 (used this link as a guide - http://msdn.microsoft.com/en-us/library/ms365379.aspx).

I have setup the server MYMSXSERVER\INSTANCE as the master and currently have 5 targets. This error occurs when I attempt to enlist the 6th server as a target.

Server information:
MSXSERVER\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Developer Edition; Engine Edition=3
TSXSERVER1\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Developer Edition; Engine Edition=3
TSXSERVER2\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Developer Edition; Engine Edition=3
TSXSERVER3\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Developer Edition; Engine Edition=3
TSXSERVER4\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Enterprise Edition; Engine Edition=3
TSXSERVER5\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Enterprise Edition; Engine Edition=3
TSXSERVER6\INSTANCE - Product Version=9.00.3042.00; Product Level=SP2; Edition=Enterprise Edition (64-bit); Engine Edition=3

TSXSERVERS1-3 are 'nonproduction' level machines used for development and testing of our software. TSXSERVER4-5 are production servers (TSXSERVER5 is actually on an active/passive cluster and is the virtual name). TSXSERVER6 is a server that houses a day old copy of our production data for reporting purposes.

Now, TSXSERVER6 is the odd one here. It's a new server where we are pushing replicated data to for reporting purposes. Aside from the version of SQL Server being for 32-bit and 64-bit the only other difference is the OS:
MSXSERVER - Microsoft(R) Windows(R) Server 2003, Enterprise Edition
TSXSERVER1-3 - Microsoft(R) Windows(R) Server 2003, Standard Edition
TSXSERVER4-5 - Microsoft(R) Windows(R) Server 2003, Enterprise Edition
TSXSERVER6 - Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition

Has anyone ran into a situation like this? The obvious thing that comes to mind is that I might not be able to have a 64-bit version of SQL Server be a target to a 32-bit version of SQL Server. But I have been unable to locate any documentation stating this either way. Any suggestions on what to look at next... or better yet if you know the answer already let me know! :)

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-04-30 : 14:49:29

Another piece of information... I dug into the SQL Agent log and found this error on TSXSERVER6:

[298] SQLServer Error: 805, SSL Provider: The certificate chain was issued by an authority that is not trusted. [SQLSTATE 08001]

I'm not quite sure what that means. Any suggestions anyone?

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2008-05-01 : 13:36:21

No suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-01 : 13:42:18

Most people do not use the master server job thing, so that's probably why you haven't gotten help on your posts. I have very little experience with it and thus can't provide any useful help to you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

ElSalsero
Starting Member

1 Post

Posted - 2009-01-08 : 17:22:19

I updated my sql server instance to the latest service pack, then updated the registry value for MsxEncryptChannelOptions as per http://msdn.microsoft.com/en-us/library/ms365379.aspx to '1'. Resolved problem.

Acabo de llegar

ngoj
Starting Member

1 Post

Posted - 2009-07-16 : 14:13:08

Well, it doesn't work without proper(!) security :)
So, you'll have either install SSL Security Cerificate
or lower security needed for master-target connection:

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.

I took this from:
http://blogs.ameriteach.com/chris-randall/2007/8/23/sql-server-2005-troubleshooting-multi-server-administration-.html

abbi1680
Starting Member

3 Posts

Posted - 2010-04-29 : 18:52:37

It has worked for me with out any certs...

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance_name>\SQLServerAgent\MsxEncryptChannelOptions(REG_DWORD)
Enter 0 instead 2 for all participated servers.

It worked perfectly OK for me.

Thanks
Abbi

abbi1680
Starting Member

3 Posts

Posted - 2010-04-29 : 18:53:16

Sorry- forgot to mention that I used 2008 ..

fayalif
Starting Member

8 Posts

Posted - 2010-08-04 : 08:43:50

Thanks for the tip ABBI.. It worked for me... using 2008 indeed.

Regards,
fayalif

redi311
Starting Member

3 Posts

Posted - 2012-01-18 : 15:47:01

if you configure a 'server authentication' certificate on the properties of the TCP/IP protocol this starts working as well. You can still connect un-encrypted, but this allows the agent to make an SSL connection. The certificate should be to the FQDN of the host machine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources