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.
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.
USE [master]
GO
SELECT
ss.name AS 'Linked Server Name',
CASE ss.Server_id
WHEN 0 THEN 'Current Server'
ELSE 'Remote Server'
END AS 'Server Type',
sl.remote_name AS 'Remote Login',
CASE ss.is_linked
WHEN 1 THEN 'True'
ELSE 'False'
END AS 'Is Linked',
CASE is_remote_login_enabled
WHEN 1 THEN 'True'
ELSE 'False'
END AS 'Login Enabled',
CASE ss.is_rpc_out_enabled
WHEN 1 THEN 'True'
ELSE 'False'
END AS 'RPC Out Enabled',
CASE ss.is_data_access_enabled
WHEN 1 THEN 'True'
ELSE 'False'
END AS 'Data Access Enabled',
ss.modify_date AS 'Date Modified'
FROM sys.Servers ss
LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id
LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
GO
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.
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.
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!