SQL Server: Curse Of Linked Server Security And The Fix: Pass-through Authentication

A great and related reference to what we will discuss in this blog post is this one: https://thesqldude.com/2011/12/30/how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied/

Linked Servers in a sentence or two:

Linked servers in SQL Server are connection definitions from one data source to another (or same instance) so that queries can be written joining tables spanning the multiple data sources. Although Linked Servers can be made to other data sources besides SQL Server (as we saw on how to setup a Linked Server to MS Access), in this post we only look at links from SQL Server to another SQL Server.

What is the concern?

In the more and more connected world, Linked Servers are somewhat unavoidable. However, from an administrators perspective, Linked Servers are generally frowned upon due to the negative performance, trouble-shooting and security considerations.

As a quick side-note, when it comes to Linked Server query performance, using OPENQUERY is better (the target instance query optimizer can optimize the query) than using the three-part table-name reference (clueless source server blindly pulls data).

How to keep Linked Servers secure?

If you read the “security” link above where SQL Server security expert Brian Kelley offers his opinion on when Linked Servers are bad with respect to security, you will immediately realize that the right option to choose when creating Linked Servers is to use the below option:

In this post, let us quickly see how we can setup linked servers so that security is no longer a concern as in – using Pass-through authentication – i.e., connections “Be made using the login’s current security context”. In other words, no elevation of privileges happens under the covers. We will also see how to resolve issues that come-up when setting this up.

When it comes to security, always remember the “Principle of least privilege“.

Tools to help!

• This utility reports Kerberos issues and generates missing SPN’s (something you will need in a later step)
  • If you are looking for an “Easy button”, please read this great post (that was brought to my attention by my co-DBA), which talks about this utility: Microsoft® Kerberos Configuration Manager for SQL Server® which a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. Check-out this post that was brought to my attention by my co-DBA: MICROSOFT MADE AN EASY BUTTON FOR SPN AND DOUBLE HOP ISSUES
• Enabling Kerberos Logging on the SQL Server host – https://support.microsoft.com/en-us/help/262177/how-to-enable-kerberos-event-logging

Thanks to my co-DBA for letting me know about this option!

The basics of Pass-through – Walk-through:

Let us say you have SQLServer1 and you want to setup a linked server to SQLServer2 using “pass-through authentication”, a double-hop happens as explained in the article below. Basically, the first hop is when the user authenticates to SQLServer1 and the second hop when that gets passed on from SQLServer1 to SQLServer2.

The below article is a must-read before you proceed:

• Configuring Linked Servers for Delegation

The three nodes involved in the double-hop as illustrated in the example are

1. Client – The client PC from which the user is initiating connection to SQLServer1
2. Middle server – SQLServer1
3. Second server – SQLServer2

Let us look at the requirements for each

The Client:

1. The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2.
2. The user Active Directory property, Account is sensitive and cannot be delegated, must not be selected.
3. The client computer must be using TCP/IP or named pipes network connectivity.

#1 is straight-forward enough. Just make sure that the Windows login of user who is connecting has access to both SQLServer1 and SQLServer2 using “Windows Integrated Security”.

#2 is easy to check. If you have not already done so, install Remote Server Administration Tools (RSAT) which gives you a bunch of tools for Active Directory management including a very powerful and useful Active Directory PowerShell module.

Once you have RSAT installed run “Active directory users and computers”. Windows -> Start -> Run

%SystemRoot%\system32\dsa.msc

Locate the user who be connecting to SQLServer1 & SQLServer2 and check his/her properties. Right-click to choose properties. It should look like what is shown in the picture (note that “Account is sensitive and cannot be delegated” is not checked!

If it is checked, you need to uncheck it (ask your AD admin if you do not have privileges).

Middle server – SQLServer1

1. The server must have an SPN registered by the domain administrator.
2. The account under which SQL Server is running must be trusted for delegation.
3. The server must be using TCP/IP or named pipes network connectivity.
4. The second server, SQLSERVER2, must be added as a linked server. This can be done by running the sp_addlinkedserver stored procedure. For example:

   EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'
   

5. The linked server logins must be configured for self mapping. This can be done by running the sp_addlinkedsrvlogin stored procedure. For example:

   EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

First find the ID of the SQL Server Service account for SQLServer1 (middle server)

The below is right out of MS documentation. Do this for the service account of SQLServer1 in AD (sorry, don’t have the source documentation link handy).

Grant delegation permission to the SQL Server service account domain user account. You must have a domain user account for clustered SQL Server installations (this step is not required for computers that are running SQL Server that are using a local system account):

1. In the Usersfolder, right-click the user account, and then click Properties.
2. In the user account properties dialog box, click the Account
3. Under Account Options, click to select the Account is Trusted for Delegationcheck box. Make sure that the Account is sensitive and cannot be delegated check box is cleared for this account.
   
   Note The ‘Account is trusted for delegation’ right is required for the SQL Server service account only when you are delegating credentials from the target SQL server to a remote SQL server such as in a double hop scenario like distributed queries (linked server queries) that use Windows authentication.

Note: If your administrator is concerned about allowing this, he/she could still configure it for certain service types and user/computers and port so that where this is done is limited to the specific SQL Servers (last option and the lower half of the screen in the above picture).

Configure the SQL Server service to create SPNs dynamically

To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

• Read servicePrincipalName
• Write servicePrincipalName

Warnings

• If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or the LDAP 3 clients and you incorrectly modify the attributes of Active Directory objects, serious problems occur. To resolve these problems, you may have to reinstall Microsoft Exchange 2000 Server or Microsoft Exchange Server 2003. In some cases, you may have to reinstall Microsoft Windows 2000 Server or Microsoft Windows Server 2003 and then reinstall Exchange 2000 Server or Exchange Server 2003. We cannot guarantee that these problems can be resolved. Modify these attributes at your own risk.
• You must be logged on as a domain administrator. Alternatively, you must ask your domain administrator to grant the appropriate permissions and the appropriate user rights to the SQL Server startup account.

To configure the SQL Server service to create SPNs dynamically when the SQL Server service starts, follow these steps:

1. Click Start, click Run, type Adsiedit.msc, and then click OK.NoteThe ADSIEdit tool is included in the Windows Support Tools. To obtain the Windows Support Tools, visit the following Microsoft Web site:

http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en

2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties.Notes
   • DomainName is a placeholder for the name of the domain.
   • RootDomainName is a placeholder for the name of the root domain.
   • AccountName is a placeholder for the account that you specify to start the SQL Server service.
   • If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
   • If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
3. In the CN= AccountName Propertiesdialog box, click the Security
4. On the Securitytab, click Advanced.
5. In the Advanced Security Settingsdialog box, make sure that SELF is listed under Permission entries.If SELF is not listed, click Add, and then add SELF.
6. Under Permission entries, click SELF, and then click Edit.
7. In the Permission Entrydialog box, click the Properties
8. On the Propertiestab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions underPermissions:
   • Read servicePrincipalName
   • Write servicePrincipalName

9. Click OKtwo times.
   
   Note For help with this process, contact Active Directory product support, and mention this Microsoft Knowledge Base article.Note To use the dsacls tool to determine if the self account has the Write ServicePrincipalName permission, use the dsacls The following is the syntax:

dsacls

If the self account has the Write ServicePrincipalName permission, you see the following output:

Allow NT Authority\SELF SPECIAL ACCESS for Validated Write to Service principal nameWRITE PROPERTY

The dsacls tool is part of the Support Tools.

10. In the CN= AccountName Propertiesdialog box, click Attribute Editor.

11. Under Attributes, click servicePrincipalNamein the Attribute column, and then click Edit.

12. In the Multi-valued String Editordialog box, remove the service principle names (SPNs) for the instances of SQL Server that use this SQL Server service account. Warning You should only delete the SPNs for the instances of SQL Server that you are currently working on. The other instances of SQL Server that use this service account will be able to remove the SPNs that are related to these instances the next time that you start these instances.

13. Exit the ADSI Edit snap-in.

Check if Kerberos is working:

Now that the middle server SQLServer1’s service account has the permissions to read/write SPN’s, when we make new connections, the auth_scheme should be KERBEROS if you run the below query:

select auth_scheme,@@SERVERNAME from sys.dm_exec_connections where session_id=@@spid

If auth_scheme is still showing as NTLM, you have a problem. It should be KERBEROS for the double-hop to work. Please go back and carefully check all the settings described so far.

NOTE: If you are trying it on an open connection try closing it and reopening. Also, close all open query windows and before disconnecting the open session. A restart of the SQL instance should not be necessary for KERBEROS to take effect for new connections. Basically, you are connecting to the middle-server SQLServer1 and issuing the above query to verify.

At this point you are still not ready to create the linked server but if you were impatient and created a linked server, you may get an error like the one shown below:

The server must have an SPN registered by the domain administrator:

This is where the “Kerberos Configuration Manager” tool referenced before will come in handy. You do not have to do this manually as the tool will help generate what is necessary and report issues. You can also do this manually as explained below but I recommend using the tool.

Let us say the service account name for SQLServer1 is svcSQLService, you can list the SPN’s using the “-l” switch of Setspn DOS command to list

Setspn -l svcSQLService

If you do not get anything back, there are no SPN’s registered and you need to register SPN.

If you were using it in two instances, say Prod and Test with hostnames MYTESTHOSTNAME and MYPRODHOSTNAME with instance names “TestInstName” and “ProdInstName” respectively, your output would look like below:

C:\Windows\system32>setspn -l svcSQLService
Registered ServicePrincipalNames for CN=svcSQLService,OU=Services,DC=mylocation,DC=mycompany,DC=local:
MSSQLSvc/MYTESTHOSTNAME.mylocation.mycompany.local/TestInstName
MSSQLSvc/MYTESTHOSTNAME.mylocation.mycompany.local:TestPortNumber
MSSQLSvc/MYTESTHOSTNAME.mylocation.mycompany.local:TestInstName
MSSQLSvc/MYPRODHOSTNAME.mylocation.mycompany.local/ProdInstName
MSSQLSvc/MYPRODHOSTNAME.mylocation.mycompany.local:ProdPortNumber
MSSQLSvc/MYPRODHOSTNAME.mylocation.mycompany.local:ProdInstName

Go ahead and register your SPN’s using the below syntax. The below example assumes that SQLServer1 is the host name (substitute with your company FQDN suffix) and the SQL Server instance name is ProdInstName using service account name svcSQLService. Note that we are using the “-s” switch to set the SPN

C:\Windows\system32>setspn -s MSSQLSvc/SQLServer1.mylocation.mycompany.local/ProdInstName mylocation\svcSQLService
Checking domain DC=mylocation,DC=mycompany,DC=local

Registering ServicePrincipalNames for CN=svcSQLService,OU=Services,OU=CLT,OU=US,DC=mylocation,DC=mycompany,DC=local
        MSSQLSvc/SQLServer1.mylocation.mycompany.local/ProdInstName

Updated object

If you get an error message about not having adequate privileges, contact your AD admin.

DNS Alias vs Actual Hostname

You would still run into errors if the registered SPN’s are using DNS aliases but your connection was made with actual hostname. Start troubleshooting by using the actual hostname, then the FQDN of the hostname. If in doubt, register both the actual hostname and the DNS alias if you think that you might define your linked server using the DNS alias.

If you are still having issues, try registering the SPN for the port number that SQL Server is listening on too. If you have got Kerberos to work and if delegation permission is in place, at this point it is just a matter of getting the SPN’s set.

Requirements for the Second Server (SQLSERVER2)

• If using TCP/IP network connectivity, the server must have an SPN registered by the domain administrator.
• The server must be using TCP/IP or named pipes network connectivity.

I am not going to expand on the these requirements as they are similar to the middle server and are simple enough to setup and verify.

Create the linked server:

At this point, just go ahead and create the linked server. I have also included the SQL that I used to create it but you can create it using the UI.

Configure security to use “pass through” (the whole point of this exercise!)

Below is the SQL used to create the same linked server as above. Notice the highlighted line with clause @useself=N’True’

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'SQLSERVER2\PRODINSTNAME', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'SQLSERVER2\PRODINSTNAME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLSERVER2\PRODINSTNAME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'connect timeout', @optvalu<span id="mce_SELREST_start" style="line-height:0;overflow:hidden;"></span>e=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SQLSERVER2\PRODINSTNAME', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Go ahead and test the linked server. Hopefully, you see a message like below and not an error!

Conclusion

Linked Servers using pass-through authentication is hard to setup but the rewards are tremendous in terms of security as the users of the Linked Servers have exactly the same privilege as their ID has on the target server – nothing more or nothing less!

The first time around, it was very difficult to navigate uncharted territory (Active Directory) and create a pass-through security based linked servers. However, once it is documented (as I did above 🙂 ), the process should be less cumbersome. If you are methodical with each step and verify to make sure, it should not be hard.

This is the right way to configure Linked Server security (if you have to create Linked Servers for your organization anyway). Thinking of the alternative, you do not want a proliferation of link user accounts with various levels of security that gets out of hand too quickly. You can never answer audit questions too if you went that route.

When it comes to security, always remember the “Principle of least privilege“!

Hopefully, this haphazard dump of a post helps you at least a little bit in the journey towards a more secure administration role.