Errors When Setting Up Multiserver Environments

Ensure the agent startup account for has rights to login as a target server (Error) – Access is denied.

Context
• Setting up multi-server management with SQL Server 2008 master (MSX) and SQL Server 2005 target (TSX) on different machines.
• Error is accompanied by “Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)”
• SQL Server Agent on MSX and SQL Server Agent on TSX run different Domain accounts (The importance thing here is that they are Domain accounts, not local accounts).

Resolution
1. Verify that both MSX and TSX have the IP protocol enabled and ports open for connection through firewall if this is enabled.
2. Follow instructions to setup Master Server here http://msdn.microsoft.com/en-us/library/ms175104.aspx. Important: When adding a connection to a target you have two choices: Windows Authentication or SQL Server Login. That is expected, but what is important is to use an account that on the TSX instance has:
a. sysadmin role (server level), and
b. access to msdb database, and
c. TargetServersRole database role on msdb database.
That means that if a Domain account is used (Windows Authentication) that account must have a login on the TSX instance with the rights listed above; otherwise, it will get the error.

Ensure the agent startup account for has rights to login as a target server (Error) – RPC is unavailable.

Context
• Setting up multi-server management with SQL Server 2008 master (MSX) and SQL Server 2005 target (TSX) on different machines.
• Error is accompanied by “RPC is unavailable” error.
• TSX runs on Windows Server 2008 SP1.
• SQL Server Agent on MSX and SQL Server Agent on TSX run different Domain accounts (The importance thing here is that they are Domain accounts, not local accounts).

Resolution
1. Verify that both MSX and TSX have the IP protocol enabled and ports open for connection through firewall if this is enabled.
2. Follow instructions to setup Master Server here http://msdn.microsoft.com/en-us/library/ms175104.aspx. Important: When adding a connection to a target you have two choices: Windows Authentication or SQL Server Login. That is expected, but what is important is to use an account that on the TSX instance has:
a. sysadmin role (server level), and
b. access to msdb database, and
c. TargetServersRole database role on msdb database.
That means that if a Domain account is used (Windows Authentication) that account must have a login on the TSX instance with the rights listed above; otherwise, it will get the error.
3. On the computer that hosts TSX do this:
a. On the Windows desktop, click Start, and then click Run.
b. In the Run dialog box, type gpedit.msc and then click OK.
c. In Local Group Policy Editor, under Computer Configuration ? Administrative Templates ? Network Connections ? Windows Firewall ? Domain Profile.
d. In the Domain Profile pane, right-click Windows Firewall: Allow inbound remote administration exception, and then click Properties.
e. Click Enabled, and then click OK.


MSX enlist failed for JobServer
Context
1. Setting up multi-server management with SQL Server 2008 master (MSX) and SQL Server 2005 target (TSX) on different machines.
2. SQL Server Agent on MSX and SQL Server Agent on TSX run different Domain accounts (The importance thing here is that they are Domain accounts, not local accounts).
3. There is not a signed certificate present on the MSX. Multiserver administration on SQL Server 2005 and 2008 require by default “a properly signed certificate”.
4. A sample error listing can be seen below.


Enlist TSX Progress

- Create MSXOperator (Success)
• Checking for an existing MSXOperator.
• Updating existing MSXOperator.
• Successfully updated MSXOperator.

- Make sure the Agent service for '31-330-2-24-AG\SQL2008' is running (Success)
• The service 'SQLAgent$SQL2008' is running.

- Ensure the agent startup account for 'ITPROJ' has rights to login as a target server (Success)
• Checking to see if the startup account for 'ITPROJ' already exists.
• Login exists on server.
• Checking to see if login has rights to msdb.
• Login has rights to msdb.
• Checking to see if user is a member of the TargetServersRole.
• User is a member of the TargetServersRole.

- Enlist 'ITPROJ' into '31-330-2-24-AG\SQL2008' (Error)
• Enlisting target server 'ITPROJ' with master server '31-330-2-24-AG\SQL2008'.
• Using new enlistment method.

Messages
• MSX enlist failed for JobServer 'ITPROJ'. (Microsoft.SqlServer.Smo)

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

------------------------------
ADDITIONAL INFORMATION:

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 '31-330-2-24-AG\SQL2008'. Make sure that the MsxEncryptChannelOptions registry subkey is set correctly on the target server.) (Microsoft SQL Server, Error: 22026)

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




Resolution
1. This resolution allows use of SSL without certificate validation, so a signed certificate on the MSX is not needed. It also allows setting the connection without SSL (no encryption), but that should be used only when connections are kept within a secured network (behind an enterprise firewall for example).
2. Follow steps in http://msdn.microsoft.com/en-us/library/ms365379.aspx