SQL: Are your linked servers secure?

I recently wrote about the most common misconfiguration that I see for linked servers. It greatly affects performance.

The second most common linked server misconfiguration is related to security. I can’t tell you how many times I’ve seen this when reviewing SQL Server systems for clients.

The issue you need to consider is that when your local SQL Server connects to the remote SQL Server, which identity is used for the connection?

Login mappings

At the top of the screen shown above, there is a list of logins. You don’t need to have any entries in this list, but they can be helpful for some of the options we’ll discuss next.

For each entry, you are configuring the following:

• Local Login - This is the local SQL Server login. Note that it’s not a user, it’s a login, as linked servers are server-level objects, and users are database-level objects.
• Impersonate/RemoteUser - When you connect to the remote server, you don’t need to use the same login that you do locally. This asks you if you want to impersonate another login, and if so, which login on the remote server needs to be impersonated i.e., it’s just what’s the remote login?
• Remote Password - If you are impersonating a remote login, this is the required password.

Note that many servers have no entries in this list. Managing the list manually is a bit painful.

Default configuration

The default configuration of Be made without using a security context is quite secure. It says that if the login who wants to use a remote object isn’t in the list at the top, then an anonymous connection is made. Almost always, that means that no connection will be made, unless the remote server allows guest access. By default, none do.

More secure configuration

The most secure option is the top option that says Not be made. This says that if the login isn’t in the list at the top, then no connection will be made. You’re either in the list, or it’s tough luck for you.

Just be yourself

The option Be made using the login’s current security context tells SQL Server that if you’re not in the list at the top, just use delegation to act as the current login on the local server i.e., if the local login also has access to the remote server, then use that connection.

It’s difficult to make hard and fast rules but this is the option that most servers should probably use.

The deadly option

The final option Be made using this security context is the one that I see misused all the time. It says that no matter who you are, if you aren’t in the list at the top, use the following remote login and password for the connection.

Clearly this is an easy option to use. You set up a remote user/password, and you enter it in this screen. Then even when the top list is empty, everyone on the local server can connect to the remote server.

But which login would you use for this?

The most common misconfiguration that I see is that a sysadmin login is entered on this screen.

That is beyond scary security-wise. You’re saying that every local login is able to act as an administrator on the remote server.

Please never do this.

Learning SQL Server Administration

It’s worth your while becoming proficient in SQL Server administration. We’ve recently released an online, on-demand and low-cost course that’s been very popular. You can find it here: SQL Server Administration for Developers and DBAs

2025-05-22