SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SCOME - Setting Up Your Remote Servers - Part 2

By Drew Salem,

A Short Recap

In Part 1: An Introduction, we discussed why it may be necessary to centralize the monitoring of all our SQL Servers. There are too many and frankly, they're doing your head in. It's a free-for-all where you are and the monkeys you work with are swinging and screaming across the server room, pressing buttons and tugging at cables.You may be pleased to know that you're not the only one. For these articles aim to carry us to our ultimate goal of not panicking in such an environment, by setting up a suite of reports that displays information about the state of all our servers. Thus giving us back control and allowing us to be super super cool (figure1) in such situations.

Figure 1 Our Ultimate Goal

Before we can use the SCOME technique (discussed in article one), we must first set up all SQL Servers to be monitored as "Linked Servers" from our monitoring server, "SQL_Admin" . This article demonstrates how to do so.



For today's article you will need...

1 x local SQL Server called SQL_Admin from which you will be doing your monitoring
1 x database call DBA_Admin, setup on SQL_Admin
1...n x remote SQL Servers to monitor (Payroll_Server, Finance_Server, Bunnytown_Server, CRM_Server etc)
1 x user with Read access to the Master and msdb databases on the remote servers
1 x user, an owner of DBA_Admin, on SQL_Admin
1 x table in DBA_Admin to hold linked servers' names (see below for definition).
Access to change Component Services settings (see below) on each remote server if the remote servers operating system is Windows 2003 SP1 or greater
Access to create SQL Server login accounts on each remote server
50g of sugar to justify "Sweeeeeet" when it all comes together at the end

Before you can setup each linked server

If any of your remote SQL Servers (the ones you will be monitoring) reside on a Windows Server 2003 SP1 or later, you'll need to make a few configuration changes to the windows server (that the remote SQL Server resides on), in order for any communication to take place. Service Pack 1 (SP2 on Windows XP) saw some changes made to the default security settings in the Windows environment. Of these changes were those made to the MSDTC service and it's this service we need to configure.

The MSDTC settings dialog box can be accessed via the Control Panel -> Administrative Tools -> Component Services (or dcomcnfg from the Run box). Then within the MMC window, expand the Component Services node, expand the Computers node, expand the MyComputer icon (wait for the actual icon of a computer to appear, otherwise it will give false results) and right click the MyComputer icon. Select Properties from the context menu that has just appeared, select the MSDTC tab and click the security configuration button. You should see a window similar to the one below:


Image of the MSDTC settings dialog box

Figure 2: MSDTC Security Settings

You now need to change the settings to that in the above screenshot for us to be able to create a linked server connection from SQL_Admin, but before you do, here's an explanation of each option and any implications, according to Microsoft:

Network DTC Access
The Network DTC Access check box lets you determine whether the Distributed Transaction Coordinator service can access the network. The Network DTC Access check box must be selected together with one of the other check boxes under the Network DTC Access check box to enable network Distributed Transaction Coordinator transactions. As we need to select the other options, we will need this to be checked.

Allow Inbound
The Allow Inbound check box lets you determine whether to allow a distributed transaction that originates from a remote computer to run on the local computer. By default, this setting is turned off, but clearly we need it on so that our monitoring queries can be run on the remote SQL Server.

Allow Outbound
When enabled, Allow Outbound will allow the local computer to flow transactions to a remote computer; this is typically needed on the “client” box, where the transaction is initiated. Currently we do not need this, but a later script for a new report may require that we enable it.

Mutual Authentication Required
Mutual Authentication Required sets the greatest security mode that is currently available for network communication. This mode requires the remotely accessing component to provide an authenticated connection with the local computer. This authentication is verified by impersonation on the local computer. Additionally, if the remote access communication is performed between two Distributed Transaction Coordinator services, this authentication information must specify a computer account that matches the remote transaction mode computer's host name. Microsoft recommends this transaction mode for server computers that are running Windows Server 2003 SP1 i.e. if anyone of your remote SQL Servers are prior to Server 2003 SP1, then don't select this option.

Incoming Caller Authentication
The Incoming Caller Authentication Required transaction mode only requires the remote connection to be authenticated. Additionally, if the remotely accessing component is a Distributed Transaction Coordinator service, the authentication information must be for a computer account.

No Authentication Required
The No Authentication Required transaction mode does not validate an authenticated connection or verify whether an authenticated connection is being established. This was how servers were set up by default before SP1 but is now accepted as having a greater security risk then the other options. I choose this mode myself, firstly because many of my remote servers reside on windows boxes prior to 2003 SP1 and secondly because I am confident that there is minimal risk of untrusted access in my working environment, but you may choose one of the other modes. If you do though, bear in mind that for our purposes, you will need to set up trusted users (Windows Authentication) for each linked server (see below) with the adequate rights to run your monitoring stored procedures/queries.

So we've made those changes on each Windows server that hosts a SQL Server you will be monitoring remotely. We now need to create each one as a linked server object on our monitoring SQL Server, SQL_Admin.

Image of the linked server tree

Figure 3: The Linked Servers Tree in SQL Server Management Studio

By creating a Linked Server Object on an instance of SQL Server, we give this instance the facility to access data from other sources, just as if these sources were actually on the local server. The linked server can be a SQL Server, allowing us to access tables on this remote SQL Server and allowing us to issue inserts, updates and run stored procedures. It doesn't have to be a SQL Server though, it can be an Oracle, Access or Excel datasource, heck it can even be a text file. In fact in a later article we be creating a web report that displays data from other sources (such as the size of Oracle data files). But for the time being we will only be dealing with linking to SQL Servers.

Connect to SQL_Admin via SSMS (SQL Server Management Studio) and expand the node Server Objects. Right click on Linked Servers and click on New Linked Server. A window like this should appear:

Image of linked server setup

Figure 4: The General tab

The General page
In the General page, enter the details of your remote server, let's call it PAYROLL_SERVER. The Linked Server box takes any name you want to give the remote linked server, might as well call it whatever the actual server is called. Product Name, you can enter Microsoft OLE DB or SQL Server, data source is what it's called on the network. Chances are this too is the name of your server.  

The Security page
The Security page is where we set up a user on SQL_Admin that will pretend to be an existing user on the remote server. So now we will create both these users on each server using SQL accounts. It's worth noting that if you intend on using an MSDTC Transaction Communication mode other than "No Authentication Required", your users will need to be Windows authenticated users.

Now, I'm not going to be patronising and insist in big block red letters that YOU SHOULD NOT USE THE SA USER for this access.You're the DBA, no one knows your databases and the environment they are in more than you. But I'll point out that for the user on the remote server, most of the stored procedures used to retrieve the reports' data need only Read access to the Master and msdb databases on these remote servers. When there is a specific object that needs fancy Execute permissions, I will point out the user permissions to just that object. As for SQL_Admins' user, it needs to be the owner of the DBA_Admin database as this user will need to execute DBA_Admin procs and read/write to tables in this database. These are the tables that our web reports will be getting their data from.

Image of the security tab

 Figure 5: The security tab

So now we have a user called, I don't know, "MonitoringReportUser" on SQL_Admin who owns the database DBA_Admin and we have a user called MonitoringReportUser on PAYROLL_SERVER who has Read access to the Master and msdb databases. We enter these details into the Security page of the Linked Server setup dialog, with the password that belongs to MonitoringReportUser on PAYROLL_SERVER. This is displayed above in figure 5 (but with Your_Login in place of MonitoringReportUser).

The Server Options Page
Finally, set the Server Options as below, where we enable Remote Procedure Calls to and from the remote server. This is necessary for what we need to do.


Image of the Server Options tab

To make sure that the new linked server was created successfully, refresh the Linked Servers node in SSMS and you should see a node called PAYROLL_SERVER. Right click the node and "Test Connection" or better still, expand the tree so that you can browse the tables of the Master database on PAYROLL_SERVER. If you get an error, then you need to review the setup.

If at any stage, you get the following error message when trying to view the linked server object via your newly created user:

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider'

You can GRANT access to this permission with the following:

GRANT EXECUTE ON xp_prop_oledb_provider TO MonitoringReportUser;


Now you could do all the above OR you could just copy and paste the T-SQL below and run it to set up a linked server. Saves you clicking lots. Remember, if you have 50 remote SQL Servers to monitor, then you'll have to set up 50 linked servers. It's a lot easier running T-SQL and changing the parameters with CTRL-H where applicable then using the SSMS. Just replace the keyword 'Payroll_Server', the username and the password.


 Part 2: Setting up your remote servers as Linked Servers
 Object Creation: Linked Server Connections
 Purpose: To create a linked server object
 Date: 25/01/2009
 Author: Drew Salem - www.thebuddingdba.com
 For: SQLServerCentral.com
 @server = 'PAYROLL_SERVER',
 @srvproduct = 'Microsoft OLD DB',
 @provider = 'sqloledb',
 @datasrc = 'PAYROLL_SERVER'
 master.dbo.sp_serveroption @server=N'PAYROLL_SERVER', 
 @optname=N'rpc', @optvalue=N'true'

 EXEC master.dbo.sp_serveroption @server=N'PAYROLL_SERVER', 
 @optname=N'rpc out', @optvalue=N'true'
-- Next we 
 add the login that takes the following parameters:
-- The name 
 of the remote server to monitor, whether to impersonate the login or 
 not, the local login to
-- map 
 from SQL_Admin,
 -- the user name on remote server to map to and the password to use 
 to log on to the remote server.
 sp_addlinkedsrvlogin 'PAYROLL_SERVER', 
  'mY_P455Word' --Password
--Now login onto SQL_Admin as MonitoringReportUser and run
 * from spt_server_info')

If the following message is received:

OLE DB provider "SQLNCLI" for linked server "Payroll_Server" returned message "Deferred prepare could not be completed.".

then chances are the MSSQLServer service account is logging on as Local System. It needs to be an account that can communicate with other SQL servers such as a domain user account.

Stored Procedures' accessibility to the names of Linked Servers

When we run our stored procedure against all our remote servers, we'll need to tell SQL_Admin which server we want it to run the proc on next. We can do this if we enter the name of each linked server into a table, which the proc can then read from. We'll call the table [Server_SQL_Details]. Here's the definition for the table...  

          2: Setting up your remote servers as Linked Servers
          Object Creation: Table [Server_SQL_Details]
          Purpose: Used to store data about Linked Servers
          Date: 25/01/2009
          Author: Drew Salem - www.thebuddingdba.com
          For: SQLServerCentral.com
          TABLE [Server_SQL_Details](
          [ServerID] [int] IDENTITY(1,1) NOT NULL,
          [Servername] [sysname] NOT NULL,
          [ServerVersion] [tinyint] NOT NULL,
          [Active] [bit] NULL,
          CONSTRAINT [PK__Server__LinkedServers] PRIMARY KEY CLUSTERED([ServerID] 

We have an incrementing ID, the name of the server, it's version (as the proc will need to determine if the remote server is SQL Server 2000 or 2005 +, so that it knows which system tables/DMVs to interrogate), and a true/false flag so that we can disable the engine from running the proc on a particular server if we need to do so. Again, more on that later).

Now enter PAYROLL_SERVER into the first row, with the version of SQL Server (8 for 2000, 9 for 2005 etc) and "True" for it to be actively included in the collection of data. 

You'll now have to do the same for aaaall your SQL Servers. Don't be put off. Trust me, once this is setup, you'll never look back... That said, there is a lazy DBAs option. You can run the below script which uses sp_linkedservers to populate the Server_SQL_Details table with all your already setup linked server objects. You'll need to temporarily relax the NOT NULL constraint on the ServerVersion column though.

          Part 2: Setting up your remote servers as Linked Servers
          Purpose: To insert Linked Servers data into Server_SQL_Details 
          Date: 25/01/2009
          Author: Drew Salem - www.thebuddingdba.com
          For: SQLServerCentral.com
        DECLARE@SQL varchar 
          SELECT @SQL = 'Exec 
          ' + @@servername + '.master.dbo.sp_linkedservers'
          CREATE table #temptable (
          srv_name varchar (100),
          srv_providername varchar (100),
          srv_product varchar(100),
          srv_datasource varchar (100),
          srv_providerstring varchar (100),
          srv_location varchar (100),
          srv_cat varchar (100))
        INSERT INTO #temptable EXEC 
        INSERT INTO Server_SQL_Details (Servername) 
          Select srv_datasource from #temptable
          SET Active = 1
          DROP TABLE #temptable
        SELECT* FROM 
        --You can now update the version of 
          the server.

We now have our linked servers setup and we have our table Server_SQL_Details which holds a few details about the SQL Servers that SQL_Admin is linked to. This table will be used by the procs to know which servers they should be run on.

That's it for this article. In the next one we'll start on our first monitoring report, one of the more straightforward ones, the Failed_Jobs report, where information regarding any failed jobs will be collected from all SQL Servers being monitored and placed into one table. Then we'll take our first look at Visual Studio to build a web application that will present the failed jobs.

Till then,




Create_Linked_Server.sql | Create_Table_ Server_SQL_Details.sql | Insert_LinkedServer_Into_Table.sql
Total article views: 6276 | Views in the last 30 days: 1
Related Articles

Another great reason to enable remote admin connections

I recently came cross another great reason to enable the remote admin connections server configurati...


Preventing Unauthorized Admin Access

Keeping Server Admins Out of SSMS


Connecting a Remote Sql Server & Accessing

Connecting a Remote Sql Server & Accessing


Accessing remotely through views

Remote server database access through views on local database


Enabling Remote Access to the Dedicated Admin Connection

Recently I was running a health check on one of my clients SQL Servers and I noticed that access to....