SQL Server Security Best Practices for Linked Servers

SQL Server Linked Servers provide a method to directly execute distributed queries on remote databases. However, they are not an ideal tool due to performance issues. If you decide to use them, it’s crucial to ensure they are secure. This post will outline some best practices for securing SQL Server Linked Servers.

Connection Types: Mapped and Default Unmapped

There are two types of connections in SQL Server Linked Servers: mapped and unmapped. We have seen some use the option for connections to ‘Be made using this security context’ and then use an overprivileged account such as a login with sysadmin or even sa (as seen below). This is an extremely dangerous configuration option because anyone who has access to server has the rights of this specific context.

We recommend using the unmapped option to ‘Be made using the login’s current security context’ as it requires specific logins or groups to be on both servers, and the permissions to have been defined for the linked server connection to work. Alternatively, if your linked servers are using a general mapping as in the fourth option, you should have a permissions review to reduce those login’s permissions to the least privilege necessary to complete the tasks it will be used for.

Principle of Least Privilege

The principle of least privilege should be used when configuring SQL Server Linked Servers. This principle states that users and systems should have the bare minimum privileges needed to carry out their jobs. The urge to overprovision access to make a process work is high here, resist the urge and work through only granting permissions for the work that will be done and nothing more.

Identifying Linked Server Configurations

To identify what linked servers are configured, what access methods are being used, and what remote account is being used, you can use the following script:

SELECT 
    s.name AS LinkedServerName,
    s.data_source AS DataSource,
	SUSER_NAME(l.local_principal_id) as local_mapped_user_name,
    l.uses_self_credential AS uses_current_login_credential,
    l.remote_name AS remote_login_name
FROM 
    sys.servers s
LEFT JOIN 
    sys.linked_logins l ON l.server_id = s.server_id
WHERE is_linked = 1

This script will return a list of all linked servers, their data sources, any local to remote mapped users, whether current context use is allowed, and the one result you should really pay attention to is if there is no local name but there is a remote name – that means the ‘Be made using this security context’  option was selected and every user can run queries against the linked server with the permissions of the remote login. The picture below shows sa as the remote context everyone gets to use, and yes, this is based on configurations we have seen before.

In conclusion, while SQL Server Linked Servers can be a useful tool in some cases, they must be used with security in mind. By following these best practices, you can help ensure the security of your SQL Server environment.