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

Do Logins with sysadmin role require User Mapping to be done for each database in the Instance?

The answer is no. A Login with sysadmin role does not need user mapping at the database level to be able to perform any tasks on those databases.
If a user would gain access to SQL Server on that Login and creates a database, the Login would be mapped to the built-in dbo database user as seen in the example below; Login BCEDU\agonzal2 (Windows Authentication) created three databases including the Management database.





The following Q&A is in the context of using the BUILTIN\Administrators local built-in account for the Windows host as a SQL Server Login.

Note: In some cases a Login with sysadmin role must be granted explicit rights (database roles). An example is when working multiserver environments and the TargetServerRole must be granted to the Login on the Target server.
Question
system administrators role and dbo rights - Jaso
26-Mar-08 10:31:12

I noticed that the built in admin account is given sysadmin role, but it is
also given dbo on every database. Isn’t this redundant? I thought sysadmin
role automatically gives a user dbo rights on any db it uses.

When I add the new group of dbas to replace the built-in admin group, I only
need to give it the sysadmin role, correct?
Answer
26-Mar-08 11:31:39

Hi Jason,
In addition to Tibor's suggestion, I would like to add more comments for
your first question here.

I think that it is not redundant but necessary. You know that database
level access is based on users but not logins, so there must be a user in
each database mapping to a specific login, even a sysadmin login account
without exception. Now let us first look at the definition about dbo in SQL
Server Books Online:
--------------------------------------------------------------
The dbo is a user that has implied permissions to perform all activities in
the database. Any member of the sysadmin fixed server role who uses a
database is mapped to the special user inside each database called dbo.
Also, any object created by any member of the sysadmin fixed server role
belongs to dbo automatically.

For example, if user Andrew is a member of the sysadmin fixed server role
and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not
as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed
server role but is a member only of the db_owner fixed database role and
creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The
table belongs to Andrew because he did not qualify the table as dbo.T1.

The dbo user cannot be deleted and is always present in every database.

Only objects created by members of the sysadmin fixed server role (or by
the dbo user) belong to dbo. Objects created by any other user who is not
also a member of the sysadmin fixed server role (including members of the
db_owner fixed database role):
- Belong to the user creating the object, not dbo.
- Are qualified with the name of the user who created the object.
---------------------------------------------------------------

You said that your built-in admin account was given dbo on every database,
so I believe that all of the databases were created by your built-in admin
account. If one database was created by another login account with sysadmin
fixed server role, the dbo should have been mapped to that login and in
this case, your build-in admin account would map to another user, by
default same as the login name, who is assigned with the db_owner database
role.

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support

(Source: http://www.eggheadcafe.com )

Connecting to Named Instances through firewalls

This document lists learned lessons from trying to connect to named instances. The scenario is like this:
• Client connects to a remote SQL Server 2005 Named Instance
• The host where the Named Instance runs is behind a network firewall
• The host is running Windows Server 2003 or 2008
• The host (Windows) firewall allows only inbound connections that match a rule.

Note that host and Named Instance are generic references. Replace them with your specific names.
On the server side
1. Identify an available port (not in use) on the host. From a DOS session, netstat –no would list all TCP ports in use; choose one that is not listed, and that is not 1433 even if not in use. It is a good practice to reserve 1433 for the Default Instance.
2. First enable the SQL Browser:
a. Start SQL Browser service
3. Set port number for Named Instance. On the SQL Server Configuration Manager:
a. Under Protocols for NamedInstance
i. Enable TCP/IP
ii. Right-click TCP/IP and edit properties as follows:
1. On the Protocol tab, note whether Listen All is set to Yes or No
2. On the IP Addresses tab, if Listen All was set to Yes, then go to the bottom and change the IPAll ? TCP Dynamic Ports to blank. Then change IPAll ? TCP Port to the available port.
3. If Listen All was set to No, then you need to perform the changes described above on each IPn section of the IP Addresses tab.
4. Save changes (click OK)
b. Restart the SQL Server (Named Instance) service
4. Open firewalls for the SQLBrowser and the NamedInstance:
a. Create an inbound rule in the Windows Firewall for Port 1434 UDP
b. Create an inbound rule in the Windows Firewall for port availableport TCP
c. Create an inbound rule in the network firewall to host on port 1434 UDP
d. Create an inbound rule in the network firewall to host on port availableport TCP
On the client side
1. Connection string from clients should be servername\instancename