Kerberos and Delegation Tips

Have you ever seen the following error "NT AUTHORITY/ANONYMOUS LOGON" or "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection"? Or maybe you've seen the "The request failed with HTTP status 401: Unauthorized." error accessing Microsoft CRM Reports?
Because these issues pop up from time to time with our Microsoft CRM customers, I spent some time recently researching this on the Internet and talking to various support engineers at Microsoft. The problems are usually caused by a Kerberos and/or delegation issue and they are very challenging to troubleshoot.
I am not an expert in the Kerberos/delegation topics domain, nor do I want to this post to delve into all possibilities/scenarios/etc. However, I wanted to share some of my learning to provide you a starting point when configuring or troubleshooting your own environments and save you some time when tackling this situation. Hopefully this post can save you some time and headaches!
Kerberos
In a nutshell, Kerberos is a secure ticket-based protocol for authenticating a service request. While Kerberos is not a Microsoft specific protocol, it is integral to the Active Directory security structure of Windows 2000/2003. Please see the Kerberos Explained article for a more detailed information.
Delegation & Service Principal Names (SPN)
Delegation is simply allowing another server/service to allow a Kerberos ticket to be created for another service on the originating user's behalf. This can be done at the computer level by using full delegation or with constrained delegation (Windows 2003 environment only). Constrained delegation means that the Kerberos delegation can only be executed against a limited set of services.
The Service Principal Name (SPN) is a name that uniquely identifies an instance of a service to a client within Active Directory. SPN's can not be duplicated in a given domain and must be specified for each name/alias that can be used to access the host.
Sample Scenario
Let's look at a common scenario for a web application accessing a database to retrieve information and return it to the client.

A client requests a web page from a web server that retrieves some information from a SQL Server database.
The web server and database server are on two separate machines in the same Windows 2000/2003 domain.
The web page uses Windows authentication to connect to the database server. Note that a typical connection string might look like: server=DATABASE;database=MyCustomDatabase;Integrated Security=SSPI
The client will access the web using any of the following: http://crmserver, http://crmserver.domain.local, http://crmalias

Configuration Requirements for the Sample Scenario
The scenario described above is also known as a "double hop" situation. The client's credentials will need to be passed to the web server (1st hop) and the forwarded onto the database server (2nd hop) for authentication and access. In order to properly display data from the database, we will need to ensure the following:
Client

Must be member of a trusted domain
Ensure the URL being used is part of the Internet Explorer trusted sites
Internet Explorer must be set to use Integrated Authentication (In IE, click Tools > Internet Options > Advanced > scroll to Security)
The domain user for the application must have the "Account is sensitive and cannot be delegated" option unselected in Active Directory.

Web/Database Servers

Both servers needs to support Kerberos (Windows 2000 and 2003 both do)
Both servers are a member of a trusted domain and have a valid Computer account
Note: When a computer is attached to the domain, a computer account should be created in Active Directory and a HOST service principal name should also be added automatically.
The web server (1st hop server) must be trusted for delegation.
SQL Server allows the user's account access. (If not, you will usually get a different error about not have valid access to the database).
SPN's are properly setup for each name that the client will access.

Validating & Creating SPN's
Only a domain administrator will be able to view and create SPN's. There are multiple ways to manage SPN's, and I will be showing examples using the setspn.exe (Setspn.exe Download). This simple command line tool can be installed on any machine in the domain, but you will need to execute as a domain administrator.
For example, you can list all SPN's for a computer account by using the following:
setspn -l computer
In the sample scenario above, you would use the following to list all SPN's for the web server:
setspn -l crmserver
To create a new SPN for the HTTP service, you would use the following syntax:
setspn -a http/<name> <server netbios name> (if the identity is running under the computer account)
setspn -a http/<name> <domain\name> (if the identity is running under a domain account)
When creating a new SPN for our CRM web server, you will need to first determine the account running IIS by reviewing the identity of the Application Pool (IIS 6.0). If that account is local system or network service, then it is running under the computer account. You would also need to determine the name your clients will access this service.
In our sample scenario, you would need to do the following:
setspn -a http/crmserver crmserver
setspn -a http/crmserver.domain.local crmserver
setspn -a http/crmalias crmserver
Note, if your service is running under a domain account, then you will need to specify that account in your setspn -a statement:
setspn -a http/crmserver.domain.local domain\user
You will also need an SPN for the SQL service (usually this is created automatically when you install SQL Server). The usual reason this is configured incorrectly is when the account that is running SQL Server changes after installation. If you do need to create a new SPN for the SQL service, use something similar to:
setspn -a mssqlsvc/database.domain.local domain\user
See Setspn Syntax for more information on the syntax and usage of the setspn tool.
Troubleshooting Tips

Ensure the client's IE configuration is correct (see client configuration above), verify from where are accessing the web site(LAN, VPN, Internet) and what URL they are using.
In most instances I have seen, a misconfigured SPN is the issue. Determine your IIS and SQL Server service's accounts. Double check that you have a valid SPN for each service account account for the access method used (netbios, FQDN, any alias/CNAME used, etc).
NOTE: I recommend creating an SPN for the fully qualified domain name (FQDN) if one doesn't exist. I have seen a situation where the netbios name was used to access CRM, but under the covers it gets submitted as the FQDN...in this case the user had a DNS suffix added to their network properties.
Try to determine if your client is accessing the web server with Kerberos instead of NTLM. I find that this is easiest to check the event viewer on the web server and look at the error thrown. In the details, it will tell you which protocol was used to authenticate. If it is Kerberos, then your problem will typically be with an SPN or delegation, and your client configuration/access is fine.
Check the delegation from the web server to the SQL service SPN. If you are using constrained delegation, ensure you have the correct service setup.
Check the time on the client versus the servers. They need to be in sync (typically within a few minutes) for a Kerberos ticket to be granted. Most machines on the domain usually get their time from the domain controller, so I haven't seen this to be the issue in most cases.
For the more obscure issues and more advanced users, capture the network trace from the 1st hop machine (typically the web server). This can be done with a tool like Wireshark. Filter the trace file by Kerberos and look for these common errors:
- time_skew - There is likely a w32time error or difference between the machines
- s_principal_unknown - Indicates an SPN error
- access denied - Try rebooting the client and reviewing the event viewer on the domain controller for any errors. Use KerbTray on the client to see if you have any valid Kerberos tickets.
If all else fails, then contact Microsoft Support and use their trained engineers to help troubleshoot the root cause. Prepare to allocate some time and have someone with domain admin rights available to assist the Microsoft engineer.

Additional Notes

Kerberos/delegation is very important to Microsoft CRM 3.0 because of its integration with Reporting Services AND because filtered views will not return records from a connection string using SQL authentication.
Kerberos requires the client computer to be on a trusted domain. It will not work if you are accessing over the Internet as the domain will not be able to trust you. If you were to access the web page or report over the Internet and receive the standard challenge/response dialog, you will typically be authenticating over NTLM, and as such unable to pass a Kerberos ticket to SQL server in a double-hop scenario.
NOTE: NTLM can be used to authenticate to SQL server if the web is on the same server (ie it doesn't need to delegate to another server).
You can get Kerberos to work if you connect with most VPN software, provided the computer you are using has been previously registered on a trusted domain (typically applies to a laptop situation).
For instance, when I try the reports page through CRM at home, I get a "The request failed with HTTP status 401: Unauthorized." error. My home desktop can't access our reports through CRM even when I have established a VPN connection to the office. Our CRM architecture has reports/SQL/CRM on separate machines AND my computer is registered on my home network (which isn't trusted by my office), so I won't have a Kerberos ticket to forward to SQL Server. However, if I VPN into my office using my laptop (a member of my office domain), I can access the reports fine. I am told that not all VPN software will allow you to get a Kerberos ticket, although most I have tried seemed to work.
Small Business Server should not see any of these problems (even when accessing over the Internet) because all components are on the same server. Therefore, delegation is not required, and an NTLM authentication is sufficient. Likewise, if you installed CRM/SQL Server/SRS on the same server, you would also avoid this problem (although you would have other ones to address).
It is possible for the web server to convert an NTLM request to a Kerberos ticket by using a technique known as Protocol Transition. I am told it is quite challenging to make this work, and have included a link to an article in the Additional Resources section if you are curious/ambitious.
If your service account is a domain user (instead of network service or local system), then this user also needs to have its trust setup for delegation in Active Directory. If you don't see the delegation tab, then it means there is no SPN setup for that account. Setup an SPN for the account, then try user properties dialog again and you should see the tab.
A Kerberos hotfix does exist for cases where a client Kerberos ticket is expiring on Windows XP SP2. Unfortunately, I haven't personally seen this hotfix work in the cases where I have this situation. It is limited distribution, so contact Microsoft Support if you need it. I am told that if you have an XP SP2 machine that has an expired Kerberos ticket (use KerbTray to verify), and it is corrected by simply logging off and back in, then that hotfix should work for you.