The Problem
You’re a security conscious DBA so you follow best practices; you run your SQL services as a domain account and have all your users and applications using Windows authentication. Then one of your developers comes to you and says he’s getting this error running a query that uses a linked server:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
What they’re likely seeing is the result of something known as the “double-hop”: A connection from Machine A to Machine B asks Machine B to access something on Machine C using the credentials originally provided by Machine A. In this scenario, Machine B using the credentials from Machine A is known as impersonation and Machine B’s ability to use impersonation to connect to Machine C is called delegation. The errors occur when delegation has not been properly configured. There are couple of ways this may manifest itself:
- Bulk inserting a file from a UNC share on another computer (e.g. \\servername\share\file.bcp)
- An Ad-hoc distributed query using a trusted connection
- Using a linked server that’s been configured with a trusted connection
The Solution
There are two ways to solve this problem – either use SQL authentication or configure delegation. Since this post is about fixing the double hop problem in a Windows auth environment I’ll save the debate on authentication methods for another time and focus on what it takes to get delegation working. Microsoft has a nice write-up on how to configure delegation on MSDN. I’ve added to it by providing screenshots and my own comments.
To use delegation, all computers must use Active Directory and have Kerberos support enabled (Kerberos is the default authentication method in Windows 2000 and up, so this usually isn’t an issue). The following options in Active Directory must also be specified (which you get to through the Active Directory Users and Computers MMC snapin):
- The Account is sensitive and cannot be delegated check box must not be selected for the user requesting delegation.
- The Account is trusted for delegation check box must be selected for the service account of SQL Server. This is a bit outdated; for Windows Server 2003 there’s a Delegation tab where you can choose the different levels of trust for delegation (note that the tab doesn’t show up until SQL Server is started using that account). For those ultra-tight on security you can specify services on a computer-by-computer basis…for the slightly more relaxed there’s the “any service” option.
- The Computer is trusted for delegation check box must be selected for the server running an instance of SQL Server. Same deal as the previous setting – there’s a tab for this with different options depending on how tight you need to control security.
Next, your SQL Server service must have a Service Principal Name (SPN). This is essentially a unique name in Active Directory for an instance of a service on a computer. When SQL is run under a domain account a SPN needs to be manually created using the setspn utility found in the Windows 2003 Support Tools. The command for this utility amounts to something like this:
setspn -A MSSQLSvc/[FULLY QUALIFIED DOMAIN NAME]:[PORT] [DOMAIN]\[LOGIN]
For example:
setspn -A MSSQLSvc/hq-dbadmin01.mydomain.com:1433 MYDOMAIN\sql.service
Finally, there are a few things to do to make it all work:
- You have to restart your SQL Service after creating the SPN.
- For distributed queries or queries using a linked server where both servers are running as a domain account you must create a SPN for both servers
- You must use Kerberos for all of this to work. To check if you’re using Kerberos, after you’ve gone through all the steps above make a new connection and run the query below. It should return "KERBEROS” if everything’s set up right:
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
VISIT: Administering SQL Server (SQL Server 2000): Security Account Delegation
VISIT: MSDN Library: Service Principal Names
VISIT: Wikipedia: Kerberos
DOWNLOAD: Windows Server 2003 Service Pack 1 32-bit Support Tools
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
2 comments
I have a question here...
If the 3 servers A,B,C are in same domain, Same OU then is it needed to setup delegation?
Why I'm asking this is
Case 1:
I connect to Server A which is CA(USA) and I'm able to query a linked server table from this server to server B which is in NY(USA).
Case 2:
I connect to Server A which is CA and I'm NOT able to query a linked server table from this server to server B which is in Korea.
If SQL on servers A, B, and C is running under a domain account, then yes you would need to set up delegation.
If you haven't seen it, read my follow up post on Service Principal Names. I link to a utility which will let you query AD to find out which servers have a SPN set up. It sounds like one of your servers doesn't have a SPN.
Post a Comment