While configuring SQL Server Multi Server Administration: Master and Target Servers in SQL Agent, we encountered an error as below:
MSX enlist failed for JobServer ‘TargetServer’.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17289.0+((SSMS_Rel_17_4).181117-0805)&
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 ‘MasterServer’.
Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)
Root cause Analysis & Resolution
As per the investigation, it is identified as an issue associated with a registry value on the target server MsxEncryptChannelOptions. When we configure the set up, it tries to establish the connection between master and targets in a secure channel with full SSL encryption. And if SSL encryption is not enabled between servers/instances, then this setting has to be changed in target servers using the registry. You can change the registry value here: \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\SQLServerAgent. Please note the default value is 2. We need to change it to 0, however, this change needs to be verified and evaluated with you security norms and standards.