主页 / dba / 问题 / 271020
Accepted
Ali
Asked: 2020-07-16 11:13:19 +0800 CST

SQL Server - 排序规则更改后无法将目标登记到 SQL Server 代理 (MSX)

  • 772

在将 SQL Server 的排序规则从 QL_Latin1_General_CP1_CI_AS 更改为 Latin1_General_CI_AI 后，我试图将服务器添加回 MSX 作为 CMS 中的目标，但登记失败。在 SQL 排序规则更改之前我忘记做的一件事是背叛服务器。现在我不确定是不是因为没有破坏服务器或更改排序规则导致了这种情况？无论如何，现在我需要在 MSX 中登记服务器并推送 SQL 代理监视作业。在更改之前，这台服务器已入伍。

我可以使用 SSMS 从 CMS 连接到目标服务器，反之亦然

目标 SQL Server 版本：2017 CMS SQL Server 版本：2017

尝试从 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)

尝试从目标服务器将此添加为目标时出错：

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

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

我似乎无法在网上找到很多关于这个问题的信息。任何帮助，将不胜感激。

sql-server sql-server-agent

1 个回答

  1. Best Answer

    我的解决方案是：

    1. 为 SQL Server 代理打开适当的防火墙端口。它解决了从目标入伍到大师的问题。

    2. 打开端口后，我收到以下错误：

      登记操作失败（原因：SQLServerAgent 错误：对象“sp_enlist_tsx”、数据库“msdb”、架构“dbo”的执行权限被拒绝。[SQLSTATE 42000]（错误 229）。）（.Net SqlClient 数据提供程序）

    通过在 CMS 中删除 MSDB 中的孤立 SQLAgent 用户，然后将用户重新映射回 MSDB 并为其分配 TargetServerRole 角色，此问题已得到解决。

    • 0

相关问题