Security Configuration for Linked Servers

A few years ago I published an article about how to use linked servers to avoid performance issues. I wanted to follow up on that with a discussion of linked server security configuration because this is another big issue I see all too often with linked servers.

The first thing to understand is that all linked server objects are accessible by everyone in the public database role (normally everybody). So what you are doing when you configure the security for a linked server is determining what the security context of the login will be on the remote server. The permissions of that login on the remote server will ultimately determine what the user can and can’t do.

A 750-point Health Check from SQL Solutions Group includes a security audit.

Find out how healthy your SQL Server estate really is.

Learn More

What we will be looking at is the Security tab of the linked server dialog. We’ll also look at the system stored procedures being executed behind the scenes. You can see these yourself by clicking the Script button after configuring the security in the GUI.

Default Behavior for Unmapped Logins

The top part of the dialog box allows us to map local logins to remote logins. This is fairly straightforward and not where the problem usually lies so we’re going to focus on what happens if there is not a mapped login.

We’ll look at the options one at a time.

Not be made

In this case, all that is done is to create the linked server with no login behavior specified. Without any mapped logins, the linked server is useless.

Source code
   
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1', @srvproduct=N'SQL Server'

If we try to execute a linked server query, we get the following results:

Source code
   
select name from SERVER1.master.sys.databases

Msg 18456, Level 14, State 1, Line 7
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

It tried to make an anonymous connection to the remote server which thankfully did not work.

Be made without using a security context

In this case, it explicitly creates a mapped login of NULL with @useself = ‘False’. In other words we are explicitly telling it not to pass a security context to the remote server whereas in the first option it tried an anonymous connection.

Source code
   
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1', @srvproduct=N'SQL Server', @provider=N'SQLNCLI11', @datasrc=N'SERVER1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

Let’s see what happens when we execute the query here.

Source code
   
select name from SERVER1.master.sys.databases

OLE DB provider "SQLNCLI11" for linked server "SERVER1" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 7
The OLE DB provider "SQLNCLI11" for linked server "SERVER1" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 7
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "SERVER1".

This option is more secure than the first one simply because it would not work even if the anonymous login worked on the remote server.

Be made using the login’s current security context

In this case, it is going to pass the security context of the local login to the remote login. If you have to have an option to connect without a mapped login, this is the best option. Note that the call to sp_addlinkedsrvlogin is almost the same as the last one. The only difference is that the @useself parameter is True.

Source code
   
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1', @srvproduct=N'SERVER1', @provider=N'SQLNCLI11', @datasrc=N'SERVER1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

Let’s see what happens when we execute the query here.

Source code
   
select name from SERVER1.master.sys.databases

It worked this time. The login used on the local server also existed on the remote server so the user connected as if they were directly connecting to the remote server. One thing to note is that with standard logins, this will only work if the login name AND password are the same on both instances. If the login name is the same and the password is different you will get the following error:

Msg 18456, Level 14, State 1, Line 11
Login failed for user 'UserA'.

For this reason, this is a lot easier to deal with when using Active Directory accounts. As long as the AD-based login has permission on both instances it should work just fine, with one caveat. Depending on the configuration of the AD environment you’re in, you may run in what is known as the Kerberos Double Hop issue. Typically you’d be going from a user’s workstation to the local server to the remote server. So two hops. A full discussion of this is beyond the scope of this article but suffice it to say that Kerberos delegation must be properly configured in the environment for this to work.

Be made using this security context

This is unfortunately both the most commonly used and the least secure way to address the security configuration of your linked server. What we are saying here is that every user who uses the linked server will be authenticated on the remote server using the credentials provided here. This is RARELY going to be the right thing to do in a secure environment. A user with public on the local server and nothing else will connect to the remote server using this login’s permissions which are usually higher than that. I’ve seen more than one environment using an account like linkedserver that exists and is a sysadmin on every server!

Source code
   
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1', @srvproduct=N'RKLAPTOPSS', @provider=N'SQLOLEDB', @datasrc=N'SERVER1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVER1', @locallogin = NULL , @useself = N'False', @rmtuser = N'UserA', @rmtpassword = N'password'

I won’t bother executing the query for this one because obviously it will work.

Remote Login Mapping

We didn’t spend a lot of time on mapped logins because that is fairly straightforward. We can map a local login to a remote login, providing the login name and password on the remote server. This is useful if we can’t control the login name and password on the remote server. It is also useful if we want to control exactly who on the local server can use the remote server. If you have logins mapped here and are using one of the first two options for the default behavior, only these users will have access to the remote server via the linked server.

The Impersonate option is just what it sounds like. This is effectively the same as the Be made using the login’s current security context but applies to a specific login, not all of them.

In this example, the only users who can access the remote server are UserA and UserB. For UserA, we have specified the login name and password on the remote server. UserB has the same login name and password on the remote server so impersonation can be used.