Problem

When creating Linked Servers, security often gets overlooked.  This can lead to accounts on one instance having access, sometimes with elevated permissions, to a second instance.

Security Options

Linked Servers offer the following security options....

• Local server to remote server mappings

• Not be made

• Be made without using a security context

• Be made using the logins current security context

• Be made using this security context

Lets take a look at each one...

Local server to remote server mappings

This setting takes precedence over the other Linked Server security settings and gives the ability to map specific logins on the local instance to logins on the remote instance.

The login on the local instance can be either SQL or Windows authenticated but unfortunately, Windows Groups cant be used. The local login can be mapped to logins on the remote instance, where the permissions of the remote login would then be used.

Accounts can impersonate the same account on the remote server. For SQL logins, the username and password need to be exact matches on both instances. For Windows logins, a Service Principal Name (SPN) needs to be created on both instances. As this is done in Active Directory and very often not the responsibility of the DBA, this can be missing which can cause errors. I will go into more detail about SPNs later.

All other security settings are used for any accounts that are NOT specified in the local to remote server mappings.

Not be made

This setting denies access to the remote server for all logins except the ones specified in the local server to remote server mappings.

Be made without using a security context

Books online has the very unhelpful description for this setting...

Be made without using a security context
Specify that a connection will be made without using a security context for logins not defined in the list.

My impression was that accounts would connect and use the privileges assigned to the public server role. Unfortunately, I have never been able to test this out as I always get the following error when trying to create the Linked Server with the setting...

If anyone knows how to make this setting work, please get in touch 🙂

Be made using the login's current security context

This setting uses the same login connected to the local instance to connect to the remote instance. When using a SQL authenticated user, the username and password need to be the same on both instances. For Windows authenticated users, they can have direct access or be part of an AD group on the remote instance.

This setting gives the user access on the local instance to everything they have access to on the remote instance. If a user should only be accessing, from the local instance, a subset of the database\objects they have access to on the remote instance, then this may not be the correct setting to use.

Be made using this security context

This setting uses a SQL authenticated login on the remote instance for all connections using the Linked Server. A remote login and password need to specified within the Linked Server security options. Its not possible to use this setting with a Windows login or AD group.

I've seen people use the SA account for this setting, probably to overcome SPN issues with one of the other settings or the complexity involved with granting multiple accounts access to specific databases\objects on the remote instance. Making the remote login SA (or any login with sysadmin priveleges) will effectively allow the local login SA privileges on the remote instance.

an example...

Set the “Be made using this security context” to an account with sysadmin permissions. Then create an account on the remote instance with no permissions. I have used a login called LinkerServerTest.

Execute the following stored procedure on the local instance...

Now check the permissions that LinkerServerTest has...

(For this to work, the rpc out in the server options page needs to be set to true)

Service Principal Name (SPN)

SPNs are vital when using Linked Servers with Windows Authentication. The following error can be received when using a Linked Server, which normally means an SPN hasnt been created...

MSDN describes SPNs as...

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

There are many articles on the internet that can explain SPNs and Kerberos authenication and the “double hop” problem much better than I can (this one is pretty good https://shuggill.wordpress.com/2015/03/06/configuring-sql-server-kerberos-for-double-hop-authentication/). The way I like to think about this problem is...

If you are using Window authentication on a laptop\desktop\application computer to connect to a SQL instance on another computer (one hop) which then uses a Linked Server to connect to an instance on another computer (second hop), you will need to create an SPN for the connection to work.

The above scenario is probably how most people are connecting and without SPNs, Windows accounts will not be able to gain access to the remote instance through the Linked Server.

I use the following two methods to find out if an SPN has been created.

1 – Check the SQL error log.

You'll either get good news...

or bad news...

2 - Microsoft have produced the Kerberos Configuration Manager tool to help with the detection and creation of SPNs. The tool can be downloaded from...

https://www.microsoft.com/en-us/download/details.aspx?id=39046

Creating SPNs goes above what I'd like to talk about here, so instead use this link to show you how...

https://msdn.microsoft.com/en-us/library/ms191153.aspx

Server Options

There are a couple of settings within the security options that can alter the way Linked Servers work. Both settings can be set to either true or false.

• Data Access – When set to false, this disables the Linked Servers ability to be used to retrieve data in a 4 part naming convention or using OPENQUERY SELECT statement.

• RPC out - When set to false, this disables the Linked Servers ability to be used to execute stored procedures.

DMVs

The following two DMVs give you information regarding the Linked Servers and login mappings on the instance.

• sys.servers - This gives the information on the Linked Servers that have been created on the instance. The column is_linked will show a 1 for any linked servers that have been created.

• sys.linked_logins – This gives the information on the local logins and the mappings they have on the remote instance

How should a Linked Servers security be set?

Like pretty much everything else in SQL Server, there isn't a one size fits all and you have to tailor the options to your needs. For me, I will always try to set up a Linked Server per database and explicitly set the each user to map to a specifically created SQL authenticated user that only has access to a single database with the correct permissions. Any login not specified in the local server to remote server mappings, wont be able to use the Linked Server. This way, only the logins which need access will be able to access the remote server and also SPN problems can be bypassed. Obviously, the downside to this method is that there can be many Linked Servers created with many users which can make the management burdensome.

Multiple Linked Servers for 6 databases on 2 instances.

Specific accounts mapped to SQL authenticated users.

Summary

After creating a Linked Server, time should be spent to work out what logins need to access it and what permissions will be required on the remote instance. Taking short cuts with Linked Server security can make an instance accessible to many more people than originally intended.