1

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.

  • The error messages suggest connectivity problems, so I'd start there first. – StrayCatDBA Jul 15 at 22:36
  • @StrayCatDBA, Opened a ticket with the network team and there was a firewall issue. However, I'm getting below error now. First I'm not sure which account it should be but I suspect it should be SQL Agent account which I have created in target and provided sysadmin to no luck. 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) – Ali Jul 18 at 1:51
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.