Understanding When SQL Server Kerberos Delegation is Needed

By:   |   Updated: 2011-02-22   |   Comments (2)   |   Related: > Security


Problem

Recently, we were trying to setup a web application which uses Windows authentication. We want to pass the user's credentials through to the SQL Server because the database security is dependent on who the user is. However, we weren't able to make the connection. In the SQL Server error log it said the connection was being made by NT AUTHORITY\ANONYMOUS LOGON. What is this and why isn't it connecting?

Solution

This usually happens when you have a client, such as one using Internet Explorer (IE) from a workstation, connecting to a web server which in turn connects to a SQL Server and the web server and the SQL Server are on different machines. This creates a classic "double hop" situation, which by default, won't allow the user's credentials to be passed all the way through. Let's visualize this:

setting up a web application which uses windows authentication and passing the users credentials through the sql server

Every time you cross a machine "boundary" and you want to use the same user credentials, a new hop is established. So going from the client workstation to the IIS server is one hop. Going from the IIS server to the SQL Server is another hop. There are two hops in all. This differs from the case where IIS and SQL Server are on the same machine:

this differs from the case where iis and sql server are on the same machine

Because the machine boundary is only crossed one time, going from the client workstation to the server hosting IIS, there is only one hop. When IIS talks to SQL Server in this situation, it is not considered a hop (except in certain cases with clustered SQL Servers, but likely if you have a clustered setup, you aren't running another application that talks to SQL Server on the same "physical" node).

In the first case with two hops, since Windows authentication is used to connect to the SQL Server, SQL Server is looking for a Windows user account. However, the IIS web server is not permitted to pass on the credentials of the user. As a result, it attempts an anonymous logon. When this occurs, SQL Server registers the account coming in as NT AUTHORITY\ANONYMOUS LOGON. And that's why you see that user account failing to connect when you check your SQL Server error logs.

So why is IIS blocked from passing on the user credentials?

Prior to Windows 2000 Active Directory, the security protocol used by Windows to authenticate users was NTLM, or one of the flavors of it. By design, this protocol never allowed more than one hop. So if you ran into a situation where you needed to do more than one hop, you couldn't. There were three ways we attacked this problem:

  • Use Basic Authentication on IIS, which would prompt the user for a username/password. Since this first hop wasn't done with Windows authentication, even though the username/password was the user's Windows account, the first hop was considered to start from the IIS server, meaning there was only a single hop between IIS and SQL Server. The disadvantage here is that the user is prompted when trying to use the web site, effectively logging in again.
  • Use a "service account" to connect from IIS to SQL Server. This was a domain user account and since the user's credentials weren't being passed through, IIS was the start of a brand new hop. This also meant that if there were different levels of permissions going back to the database server, it had to be handled completely by the web application since it was a shared account connecting to SQL Server.
  • Use a SQL Server-based login to connect from IIS to SQL Server. Like the previous option, this breaks the hopping because we're not using Windows authentication at all to go to SQL Server. It also suffers from the drawback that if there are different levels of permissions going back to the database server, it has to be handled by the web application.

Another scenario where we see this is when you have a client connecting to a SQL Server and that SQL Server has a linked server connection to a second SQL Server. The authentication specified for the linked server connection is to use the existing security context of the user. If the user queries the linked server through that first SQL Server, you have a double hop situation, too. This can be visualized like so:

client connecting to a sql server and that sql server has a linked server connection

So what if we need multiple hops?

There is a solution. With the introduction of Active Directory came a new security protocol, Kerberos. This was developed to handle some of the limitations of older security protocols (not just NTLM, as it was developed at MIT and was company agnostic), one of the things it does permit is cases where you have to pass the credentials over two or more hops. However, this situation isn't to be entered into lightly, as there were very valid reasons why NTLM and other protocols limited things to just one hop. So by default, Kerberos only permits one hop. Therefore, even though Kerberos permits more than a single hop, to do so requires extra configuration.

This extra configuration lies mostly within Active Directory and when the credentials are passed in this manner, it is called Kerberos Delegation. That's because the right to act on behalf of the user account is being delegated to another process, or service.

Now in most scenarios Kerberos delegation isn't needed. For instance:

  • The user is directly connecting to SQL Server, say via SSMS or Microsoft Office.
  • The user connects to a web site where IIS is running on the same server as the SQL Server (one of the ways people install SQL Server Reporting Services, for instance).
  • The user connects to a web site anonymously or via basic authentication and the web site uses a Windows domain account or a SQL Server login to connect to the SQL Server.
  • The user connects to a web site or application on a different system and it uses a Windows domain account (other than the user's) or a SQL Server login to connect to the SQL Server.

The only situation where Kerberos delegation is necessary (and the setup that comes with it) is when you want to pass the user's credentials through and you're dealing with more than one hop. If that's the case and you find yourself getting the NT AUTHORITY\ANONYMOUS LOGON login failed messages, then either the Kerberos delegation is set up incorrectly or it isn't set up at all. In either case, it typically requires domain admin rights to fix this, which most DBAs and developers don't have. If you don't have domain admin rights, get with those who do so this can be addressed.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-02-22

Comments For This Article




Tuesday, February 22, 2011 - 1:26:27 PM - K. Brian Kelley Back To Top (13006)

The first one, quite possibly. You can see that if it's coming from an untrusted computer (such as a personal workstation that's not on your domain) or an untrusted account (someone is logged on to their system with a local account for their system, which the machine where SQL Server sits knows nothing about). But you will also see it when you're in a double hop situation.

The second one, no. In that case the account is being authenticated. It is the System account that's local to the machine. Likely the issue is that you have full text installed (SQL 2000) or the VSS writer (2005 and up) which runs as System and needs sysadmin rights but NT Authority\System has been removed from SQL Server.

 

Tuesday, February 22, 2011 - 9:57:45 AM - Scott Shaw Back To Top (13005)

Thanks for the article!  Kerberos\NTLM authentication has been an issue for us. It mostly was caused by changing the SQL Service accounts. We would receive a large number of SSPI errors. We found out it was because we changed the service account to an account that wasn't used to initially install SQL Server. This caused the SPN to not register correctly. We have sinced learn that you should only change the SQL Service account through the SQL Network utility.  We also received these same errros when the AD admins would patch the domain servers.

Quick question. Do you know whether or not login errors about "null" login and NT AUTHORITY\SYSTEM could also be caused by Kerberos or NTLM double hop problems?  We see a fair number of these errors:

18452  Login failed for user (null). Reason: Not associated with a trusted SQL Server connection.

18456  Login failed for user NT AUTHORITY\SYSTEM.

Thanks,

Scott

 














get free sql tips
agree to terms