SQL Server Blog

SQL Server Security Best Practices for Linked Servers

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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.

add new SQL Server linked server

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:

    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
    sys.servers s
    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.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This