TransWikia.com

SQL Server - Unable to Enlist Target to SQL Server Agent (MSX) after Collation Change

Database Administrators Asked on November 8, 2021

After changing the Collation of SQL Server to Latin1_General_CI_AI from QL_Latin1_General_CP1_CI_AS I’m trying to add the server back to MSX as target in CMS but Enlisting is failing. One thing I forgot to do before SQL Collation change was defecting the server. Now I’m not sure if it is because of not defecting the server or changing collation that has caused this? Regardless, now I need to enlist the server in MSX and push SQL Agent monitoring jobs. This same server was enlisted before the change.

I’m able to connect to the target server from CMS and vice versa using SSMS

Target SQL Server Version: 2017
CMS SQL Server Version: 2017

Error when trying to enlist from CMS:

===================================

MSX enlist failed for JobServer 'myserver.domain.sa'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17285.0+((SSMS_Rel_17_4).180821-0238)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.MsxEnlist(String masterServer, String location)
   at Microsoft.SqlServer.Management.SqlManagerUI.EnlistTsxActions.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.MsxEnlist(String masterServer, String location)

===================================

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) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3281&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

------------------------------
Server Name: myserver.domain.sa
Error Number: 22026
Severity: 16
State: 1


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Error when trying to Add this as target from target server:

===================================

MSX enlist failed for JobServer 'myserver.domain.sa'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.MsxEnlist(String masterServer, String location)
   at Microsoft.SqlServer.Management.SqlManagerUI.EnlistTsxActions.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.MsxEnlist(String masterServer, String location)

===================================

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) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3281&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

I can’t seem to find much information online on this issue. Any help would be appreciated.

One Answer

The resolution in my case was:

  1. Open appropriate Firewall Port for the SQL Server Agent. It resolved the Enlisting of Target to Master issue.

  2. After opening the port I was getting following error:

    The enlist operation failed (reason: SQLServerAgent Error: The EXECUTE permission was denied on the object 'sp_enlist_tsx', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229).) (.Net SqlClient Data Provider)

This issue was resolved by dropping the Orphaned SQLAgent user in MSDB in CMS and then remapping the user back to MSDB and assigning it TargetServerRole Role.

Answered by Ali on November 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP