Validating The Credentials And Configuration Of All Linked Servers On A SQL Server

A Linked Server, simply put, is a connection between two or more SQL Servers that allows for cross-server querying.  In nearly all deployments of SQL Server you’re going to have multiple servers running.  Without setting up the appropriate linked server on each implementation, you would have no way to execute queries that can pull data from both servers.

If you’re not already familiar with Linked Servers in SQL Server and would like to learn a bit more about configuring them, refer to my previous post on Creating And Configuring A Linked Server In SQL Server.

Depending on the size of your organization you may have quite a few SQL servers running and you may have just as many linked servers.  I recently wrapped up a project in which the goal was to consolidate and review all linked servers across every implementation of SQL Server.  The first goal was to ensure that all linked servers were using the same service account as the login.  I also wanted to verify that certain Security and Server Options were enabled.

Though it is possible to log into the SQL Server and navigate to the Linked Server drop-down (shown below), this would require me to review each linked server individually, which can often be very time consuming.

Linked_Servers_location

Fortunately, this information can be more easily retrieved by querying the sys.servers, sys.linked_logins and sys.server_principals System Views from the SQL Server master system database.

The following script should provide you the most relevant information regarding the configuration of all linked servers on a particular instance of SQL Server.

Though I am running this within my lab environment, where I have fewer linked servers configured, the output will appear similar to the screenshot below.

Notice the first record in the output has a NULL value for Remote Login.  This indicates that this record corresponds to the server on which the script is being executed.  This server does not have a remote login.  This is also indicated by the status of Current Server in the Server Type column.

Linked_Servers_configuration_output

The script provided above can be further modified to pull additional data from the various system views but I find the included colunns tend to be the most relevant.

Any modifications to the actual linked server settings can easily be made by right-clicking the linked server and opening the Linked Server Properties window in SQL Management Studio and clicking on either the Security or Server Options pages.

Linked_Server_Security_ServerOptions_window

Locking down linked servers is a vital piece of managing the security in your SQL Server environment.  Preventing unnecessary connections from one server to another can inevitably prevent undesired actions from occurring.  Though you will need to have a solid understanding of your database environment to know which servers should and should not be linked, one safe assumption that you can always make is that you should never have a production environment linked to a lab or development server.  Nothing good can come of that!