I've tried to set Mater and Target Servers on various cobinations of SQL Servers 2012 R2 on Window Server 2008 R2 and Windows 7 machines but always getting one or 2 of the following errors during setting either target or master server:
- Enlist TSX Progress
1) Create MSXOperator (always Success)
- Checking for an existing MSXOperator.
- Operator does not exist. Creating.
- Successfully created MSXOperator.
2) Make sure the Agent service for '*Master-Server-Name' is running (sometimes an Error, sometimes not)*
An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.SqlWmiManagement)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.InnerWmiException&LinkId=20476
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Invalid class (System.Management)
3) Ensure the agent startup account for 'Master-Server-Name' has rights to login as a target server (if Success here, then 4) is error or both 3) and 4) are errors)
- Checking to see if the startup account for 'Target-Server-Name' already exists. Login exists on server.
- Checking to see if login has rights to msdb.
- Login does not have rights to msdb. Adding.
- Created new user in msdb.
- Checking to see if user is a member of the TargetServersRole.
- Adding user to the TargetServersRole.
- Added user to TargetServersRole.
4) Enlist 'Target-Server-Name' into 'Master-Server-Name' (if Success here, then error in 3) or both in 3) and 4))
- Enlisting target server 'Master-Server-Name' with master server 'NOVOSIB-1C'.
Using new enlistment method.
MSX enlist failed for JobServer 'Target-Server-Name'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'Target-Server-Name'.) (Microsoft SQL Server, Error: 22026)
In all cases I was using a domainUser login with sysadmin role for setting SQL Server Agent service and owner of an SQL Server Agent job (or, in the latter, tried also
sa), Windows Authentication to
localhost in used by them maintenance plans. The used domainUser is part of (local) Administrators group in each of the Windows (machines).
The SQL Server Management Studio is run
How can I troubleshoot these errors?
What should I check and look for?
Specifically, I do not quite understand:
- Which server and which login to check in from the phrase:
"Ensure the agent startup account for 'Master-Server-Name' has rights to login as a target server (Success)"
- How to check that this login has rights for msdb from the phrase:
"Checking to see if login has rights to msdb"