In this post I’ll address improper linked server configurations and their potential impact, as well as recommendations for securing SQL Server linked servers.
Linked servers are one of SQL Server’s features that can be very useful and versatile, but can also be pretty easy to incorrectly set up and improperly secure.
What’s a SQL Server linked server?
A linked server is a server-level object that is used to create a connection between two SQL Server instances or between a SQL Server instance and another RDBMS, that allows querying and command execution against a remote database.
A few examples of other RDBMSs to which linked server connections can be created:
- Oracle
- SAP Hana
- MySQL
- PostgreSQL
In this post I’ll only focus on the scenario where a linked server connection is established between two SQL Server instances.
How does authentication work in linked servers?
To properly understand the security implications of linked servers I’ll first go over how instance authentication in a linked server connection can be configured.
Mapping a login from instance A to a login on instance B.
This tells SQL Server that, when login A from instance A tries to access a resource from instance B through the linked server connection, it should use login B that exists on the remote instance (instance B).
Here’s an example from my test environment, where on the WINSRV2K22\SQL2022 instance I’ve created a linked server connection to the WINSRV2K22\SQL2019 instance.
In this case, if either SQL2022Login (a SQL login) or WinSrv2k22\WinSQL2022 (a login based on a Windows account), both of which exist only on the WINSRV2K22\SQL2022 instance, try to access a resource from WINSRV2K22\SQL2019 via the linked server connection, they will both interact with the remote instance as the SQL2019Login (a SQL login that only exists on WINSRV2K22\SQL2019) with the password saved in the Remote Password field.
Setting a login from instance A to interact with resources on instance B as itself.
This requires the existence of the same login with the same password (if it’s a SQL login) on both instances, otherwise it won’t work.
Using the same example from my test environment, when either of WinSrv2k22\WinSQLBoth (a login based on a Windows account) or SQLBothLogin (a SQL login) try to access a resource from WINSRV2K22\SQL2019 via the linked server connection they’ll do that directly as themselves since both logins exist on both instances.
What about logins not defined in the mapping?
This is where the seconds half of the Security page in the Linked Server Properties menu comes in.
This section specifies if and how the connection on the remote instance should be made for if any login that isn’t specified in the mapping section tries to access the remote instance through the linked server connection.
| Option | Explanation | Recommended option |
|---|---|---|
| Not be made | This is the most restrictive option. If any login on the local instance tries to use the linked server connection, but isn’t defined in local login to remote login mapping section, it will simply encounter the following error message: Msg 7416, Level 16, State 1, Line 7 Access to the remote server is denied because no login-mapping exists. | Yes, especially from a security standpoint. |
| Be made without using a security context | This will result in an anonymous connection to be made in an attempt to login against the remote server and, in most cases, will result in the following error message: Msg 7399, Level 16, State 1, Line 7 The OLE DB provider “MSOLEDBSQL” for linked server “WINSRV2K22\SQL2019” reported an error. Authentication failed. | Yes, since it’s really limited by default |
| Be made using the login’s current security context | This acts just like the Impersonate option in the login mapping section, but doesn’t require you to specify the local login name. It will just let any local login that also exists on the remote instance access the remote instance’s resources via the linked server connection. Any logins that only exist on the local instance, but not on the remote instance, will still get the 7416 error message when trying to use the linked server connection. | It depends on whether you want all the users with logins on both instance A and B to be able to use the linked server connection. |
| Be made using this security context | Checking this option will enable the “Remote login” and “With password” fields found right under it. That will basically act as a catch-all mapping for all the other logins not specified in the in local login to remote login mapping section. This the most lax option and will result in every login on the local instance being able access the remote instance through the linked server connection, with the only limitation being the specified remote login’s level of permissions. | NOPE. At best – all the logins on instance A get access to instance B and access data that some of them might not be allowed to. At worst – they’ll all end up connecting as SA to instance B through the linked server. Either way, I strongly recommend avoiding this option. |
Setting up the demo environment
For this demo I’m using a Windows Server 2022 VM hosting 2 SQL Server instance, one 2022 and one 2019, although in this case SQL Server versions are not that important since linked server connections have the same general behavior since SQL Server 2008.
First step is to create 3 Windows accounts using PowerShell opened as Administrator.
Note, these are local Windows users since I’m not in an AD environment, in an AD environment you’d normally have domain accounts.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | #Use distinct passwords just to avoid confusion $Password1 = ConvertTo-SecureString "P@ssW0rD1!" -AsPlainText -Force $Password2 = ConvertTo-SecureString "P@ssW0rD2!" -AsPlainText -Force $Password3 = ConvertTo-SecureString "P@ssW0rD3!" -AsPlainText -Force #Create the Windows accounts #One for the SQL2019 instance New-LocalUser -Name 'WinSQL2019' ` -Description 'Windows account for SQL2019' -Password $Password1 ` -AccountNeverExpires -PasswordNeverExpires #One for the SQL2022 instance New-LocalUser -Name 'WinSQL2022' ` -Description 'Windows account for SQL2022' -Password $Password2 ` -AccountNeverExpires -PasswordNeverExpires #And one for both instances New-LocalUser -Name 'WinSQLBoth' ` -Description 'Windows account for both instances' -Password $Password3 ` -AccountNeverExpires -PasswordNeverExpires |
Now, in SQL Server, I create a few logins as follows:
- Only on the WinSrv2k22\SQL2019 instance
| 1 2 3 4 5 6 7 8 9 10 11 12 | /*On SQL2019*/ /*Windows Login*/ CREATE LOGIN [WinSrv2k22\WinSQL2019] FROM WINDOWS; GO /*SQL Login*/ USE [master] GO CREATE LOGIN [SQL2019Login] WITH PASSWORD=N'SQL2019!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO |
- Only on the WinSrv2k22\SQL2022 instance
| 1 2 3 4 5 6 7 8 9 10 11 12 | /*On SQL2022*/ /*Windows Login*/ CREATE LOGIN [WinSrv2k22\WinSQL2022] FROM WINDOWS; GO /*SQL Login*/ USE [master] GO CREATE LOGIN [SQL2022Login] WITH PASSWORD=N'SQL2022!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO |
- On both instances
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /*On both instances*/ /*Windows Login*/ USE [master] GO CREATE LOGIN [WinSrv2k22\WinSQLBoth] FROM WINDOWS; GO /*SQL Login*/ USE [master] GO CREATE LOGIN [SQLBothLogin] WITH PASSWORD=N'SQLB0th!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO |
Now to create the linked server connection on the WinSrv2k22\SQL2022 instance.
I’ll go through both ways of creating the linked server connection, the GUI way and the T-SQL way.
Creating SQL Server linked servers using the GUI
In the General page I select the “SQL Server” radio button and fill in the name of the remote instance, which in this case is WinSrv2k22\SQL2019
Notice the highlighted message at the bottom of the window stating that:
“Server Type is either SQL Server or an OLE DB provider installed on the server. If SQL Server is
selected then the Linked Server name is also the network name of the server.”.
This means that the linked server object will have the same name as the remote instance WinSrv2k22\SQL2019, this can me changed later on if needed (and I’ll change it in order to avoid any potential confusion).
Up next is the Security page where I specify which local logins can connect as themselves to the remote instance (hint: it’s the ones with the Impersonate checkbox checked), and which local logins should map to the SQL2019Login login that exists on the remote instance.
I also check the “Not be made” radio button so that local logins not defined in the mapping section won’t be able to use the linked server connection.
On to the “Server Options” page where I set RPC (remote procedure call) and RPC Out to true because I want to be able to execute T-SQL, and have it return output, through the linked server connection, not just perform basic DML against some table(s).
Last thing left to do is to hit Ok in order to create the linked server connection.
Detour for potentially confusing error messages
But, when I press OK, out of nowhere:
Now, this is what I suspect can get people to press “No” and go back to the security page to add their login to the login mapping list.
Which isn’t really a bad thing, especially if they can use the impersonate option.
But they also might just go straight to checking either “Be made using the login’s current security context” or, the really bad option, “Be made using this security context”, and combine it with the sa credentials for the remote instance.
Which would lead to everyone who has any type of access on WinSrv2k22\SQL2022 ending up interacting as sa with the WinSrv2k22\SQL2019 instance via the linked server connection.
There’s even a StackOverflow question where the approved answer is resorting to the “Be made using this security context” option, but, luckily, not directly with the remote instance’s sa account.
There’s also some consulting company recommending the same thing on their blog, but I’m not linking the post here because of reasons.
This is one of those errors where reading the error message carefully (even a couple of times if needed) gives us a better understanding of what’s going on.
The first line starts with the linked server was created, which is good, but continues with “but failed a connection test”, which means that, even if it’s showing up in OE, it’s not working right, right?
Well, not really. Reading the rest of the error message reveals the reason of the connection test failure “access was denied because no login mapping exists”.
So what actually happened?
SQL Server did, successfully I might add, create the linked server connection exactly how I configured it, but, because I’m logged in as sa, and I haven’t defined any mapping or impersonation of the sa login from the local instance the sa or any other login on the WinSrv2k22\SQL2019 instance, when SQL Server tries to test the linked server connection it will do so as my current login which causes the above error message.
TL;DR: it’s ok, the linked server is created and works as intended
This can be easily tested by logging in as one of the logins that are defined in the linked server connection and doing a test.
| 1 2 3 4 5 6 7 | SELECT SUSER_NAME() AS [LocalLogin], @@SERVERNAME AS [LocalInstance]; DECLARE @RmtSQL NVARCHAR(500); SET @RmtSQL = N'SELECT SUSER_NAME() AS [RemoteLogin],' +N' @@SERVERNAME AS [RemoteInstance];'; EXEC (@RmtSQL) AT [WINSRV2K22\SQL2019]; |
I’ve mentioned earlier that I want to rename the linked server connection to avoid confusion, and I can do this via the sp_serveroption system stored procedure:
| 1 2 3 4 | USE [master] GO EXEC sp_serveroption @server=N'WINSRV2K22\SQL2019', @optname=N'name', @optvalue=N'LinkToSQL2019'; |
Creating the SQL Server linked server via T-SQL
Note, you won’t get the 7416 error message when using T-SQL to create a linked server, because the linked server test is only done when creating it from the GUI.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | USE [master] GO /*Create linked server*/ EXEC sp_addlinkedserver @server = N'WINSRV2K22\SQL2019', @srvproduct=N'SQL Server'; GO /*Set a more memorable name for the linked server connection to avoid confusion*/ EXEC sp_serveroption @server=N'WINSRV2K22\SQL2019', @optname=N'name', @optvalue=N'LinkToSQL2019'; GO /*Do the rest of the configuration*/ EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'collation compatible', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'data access', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'dist', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'pub', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'rpc', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'rpc out', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'sub', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'connect timeout', @optvalue=N'0'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'collation name', @optvalue=null; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'lazy schema validation', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'query timeout', @optvalue=N'0'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'use remote collation', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2019', @optname=N'remote proc transaction promotion', @optvalue=N'true'; GO /*Set logins that can use impersonation*/ EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LinkToSQL2019', @locallogin = N'SQLBothLogin', @useself = N'True'; GO EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LinkToSQL2019', @locallogin = N'WinSrv2k22\WinSQLBoth', @useself = N'True'; GO /*Map local logins to remote logins*/ EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LinkToSQL2019', @locallogin = N'SQL2022Login', @useself = N'False', @rmtuser = N'SQL2019Login', @rmtpassword = N'SQL2019!' GO EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LinkToSQL2019', @locallogin = N'WinSrv2k22\WinSQL2022', @useself = N'False', @rmtuser = N'SQL2019Login', @rmtpassword = N'SQL2019!' GO /*Set authentication to not be made for other logins that aren't mapped or set to impersonate*/ EXEC sp_droplinkedsrvlogin @rmtsrvname = N'LinkToSQL2019', @locallogin = NULL; GO |
Demoing a linked server connection
To test the linked server connection, I’ll run the following query logged in on WinSrv2k22\SQL2022 as different users.
| 1 2 3 4 5 6 7 8 9 | /*Get info from local instance*/ SELECT SUSER_NAME() AS [LocalLogin], @@SERVERNAME AS [LocalInstance]; /*Get info from remote instance*/ DECLARE @RmtSQL NVARCHAR(500); SET @RmtSQL = N'SELECT SUSER_NAME() AS [RemoteLogin],' +N' @@SERVERNAME AS [RemoteInstance];'; EXEC (@RmtSQL) AT [LinkToSQL2019]; |
First, a “baseline” using the sa account which just throws the same error that I got when creating the linked server connection.
Msg 7416, Level 16, State 1, Line 9
Access to the remote server is denied because no login-mapping exists.
SQL login with Impersonate checked
SQL login mapped to a remote SQL login
Login from Windows account mapped to a remote SQL login
Login from Windows account with Impersonate checked
How can a SQL Server linked server become a security risk?
There are two main ways in which I’ve seen linked servers between two SQL Server instances configured in such a way that ended up with a low privilege login from instance A running as SA or another high privilege account on instance B:
One or more low privilege local logins mapped to the remote instance’s sa login
To reproduce this scenario, I modify the two entries where local logins were mapped to low privilege remote logins so that they are now mapped to the remote instance’s SA login.
Running a simple test confirms that now every interaction made by either SQL2022Login or WinSrv2k22\WinSQL2022 to the remote instance via the linked server connection will be as SA.
This also means that with the help of the linked server connection and a bit of crafty T-SQL, either of those two user can create their own logins on SQL2019 and add said logins to the SysAdmin fixed server-level role.
| 1 2 3 4 5 6 | /*Create a high privilege login on the remote instance via linked server*/ DECLARE @RmtSQL NVARCHAR(500); SET @RmtSQL = N'CREATE LOGIN [SQL2022Login] WITH PASSWORD=N''SQL2022!'',' +N' CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;' +N' ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQL2022Login];'; EXEC (@RmtSQL) AT [LinkToSQL2019]; |
Using the remote instance’s SA login as a catch-all
This is a worst case scenario because this results in anyone who has access to the WinSrv2k22\WinSQL2022 instance being able to interact with WinSrv2k22\WinSQL2019 as SA.
To recreate this I remove all the entries from the mappings section, select the “Be made using this security context” radio button and fill in the SA credentials for the WinSrv2k22\WinSQL2019 instance.
<sarcasm>The good news is that now I no longer get a 7416 error when saving the changes I’ve done to the linked server connection.</sarcasm>
The bad news is…
Everyone from WinSrv2k22\WinSQL2022 gets to be sa on WinSrv2k22\WinSQL2019.
Bonus round – the most unfortunate setup I have ever ran into
This is probably the biggest failure in securing SQL Server linked servers I’ve ever seen.
I hope it’s a fairly rare set-up since I’ve only seen it a couple of times in real life and once in a CTF.
The first part of the story is pretty familiar: A linked server connection from instance A to instance B using the remote instance’s sa login as a catch-all.
But the twist her is that instance B, in turn, has a linked server connection back to instance A, also using instance A’s sa login as a catch-all.
For this setup I leave WinSrv2k22\WinSQL2022 unchanged from the previous demo, but I create a linked server on WinSrv2k22\WinSQL2019 that points to WinSrv2k22\WinSQL2022 and uses sa as a catch-all.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | USE [master] GO /*Create linked server*/ EXEC sp_addlinkedserver @server = N'WINSRV2K22\SQL2022', @srvproduct=N'SQL Server'; GO /*Set a more memorable name for the linked server connection to avoid confusion*/ EXEC sp_serveroption @server=N'WINSRV2K22\SQL2022', @optname=N'name', @optvalue=N'LinkToSQL2022'; GO /*Do the rest of the configuration*/ EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'collation compatible', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'data access', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'dist', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'pub', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'rpc', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'rpc out', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'sub', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'connect timeout', @optvalue=N'0'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'collation name', @optvalue=null; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'lazy schema validation', @optvalue=N'false'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'query timeout', @optvalue=N'0'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'use remote collation', @optvalue=N'true'; GO EXEC sp_serveroption @server=N'LinkToSQL2022', @optname=N'remote proc transaction promotion', @optvalue=N'true'; GO /*Set authentication to be made through SA*/ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkToSQL2022',@useself=N'False', @locallogin=NULL,@rmtuser=N'sa',@rmtpassword='SA2022' GO |
To test this setup’s behavior I need to modify the previous test query so that it daisy chains another execution from WinSrv2k22\WinSQL2019 to WinSrv2k22\WinSQL2022 via the LinkToSQL2022 linked server connection.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | /*Get info from local instance*/ SELECT SUSER_NAME() AS [LocalLogin], @@SERVERNAME AS [LocalInstance]; /* Get info from remote instance and also get info from the even more remote instance */ DECLARE @RmtSQL NVARCHAR(500); SET @RmtSQL = N'SELECT SUSER_NAME() AS [RemoteLogin],' + N' @@SERVERNAME AS [RemoteInstance];' + ' DECLARE @RmtSQL NVARCHAR(500); SET @RmtSQL = ' +'''SELECT SUSER_NAME() AS [EvenMoreRemoteLogin],' + '@@SERVERNAME AS [EvenMoreRemoteInstance];''' + ' EXEC (@RmtSQL) AT [LinkToSQL2022];' EXEC (@RmtSQL) AT [LinkToSQL2019]; |
Here, the first result set is from the local instance (the one I’m directly connected to as SQLBothLogin via SSMS).
The second result set is the one obtained through LinkToSQL2019 from WinSrv2k22\WinSQL2019.
And the third one is obtained through LinkToSQL2022 from WinSrv2k22\WinSQL2022 by executing the relevant portion of T-SQL on WinSrv2k22\WinSQL2019 through LinkToSQL2019.
The impact in all 3 of these scenarios isn’t limited to just SQL Server’s level, with access to the sa account a user can leverage xp_cmdshell to interact with the instance’s host OS.
At which point, the instance’s service account, if not properly secured, may allow an attacker privileged access to the underlying OS.
Conclusion
SQL Server’s linked servers is a pretty useful feature when you need to move or sync data between instances, implement some in-house database sharding, or centralizing diagnostics/monitoring data from across your SQL Server environment into a single instance where you can have reports ran against said data.
The security risk comes when they’re incorrectly configured to give low privilege logins access to high privilege remote logins, but this can be avoided by correctly defining mappings and impersonations and staying away from “catch-all” types of configurations.
This is one of those features where you really want to be mindful of the principle of least privilege, and take the them to ensure you’re properly securing SQL Server linked servers.
3 comments
This is perfect timing because I have just inherited an environment filled with interconnected SQL Servers.
Great article. This is one of the clearest explanations of linked server security settings I’ve ever seen.
Hi Rick,
Thank you for the feedback. I’m glad you found it useful.