SQL Server support multi-server administration that allows us to distribute jobs across our enterprise, basically across our SQL Servers. Configuration of multi-server administration is done through SQL Server Agent and one server needs to be configured as master and other servers have to configured as targets. When setting it up, you might get following error at the end of the wizard;
The enlist operation failed (reason: SQLServerAgent Error: The target server cannot establish an encrypted connection to the master server. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)
What is the reason for this?
Reason for this is, it tries to establish the connection between master and targets in a secure channel with full SSL encryption. It tries this because your systems requests it by default.
If SSL encryption is not enabled between servers, then this setting has to be changed in target servers using the registry. The key MsxEncryptChannelOptions(REG_DWORD) is available under \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
\SQLServerAgent for changing the default setting which is 2.
It accepts three values:
- 0 - disables encryption between the master and target.
- 1 - enables encryption between the master and target but no certification validation is required.
- 2 - enables full SSL encryption between the master and target.
If you do not need an encryption channel between the master and target, then set the value as 0 and configure multi-server administration.