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:
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:
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:
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
- Review Microsoft’s How to Implement Kerberos Constrained Delegation with SQL Server 2008.
- Read through Microsoft’s Troubleshooting Kerberos Errors guide.
