Kerberos Delegation with SQL Server Reporting Services and SQL Server

2006/04/05

I've been dealing with Kerberos delegation setup with respect to Microsoft's CRM 3.0 product and while the process isn't difficult, if you miss one step, things don't work. That can be extremely frustrating, but there are some tools and web pages which help with configuration and troubleshooting. First the web pages.

Troubleshooting Kerberos Delegation whitepaper
How to troubleshoot the "Cannot generate SSPI context" error message (811889)
HOW TO: Troubleshoot Kerberos-Related Issues in IIS (326985)
How to use Kerberos authentication in SQL Server (319723)
SQL Protocols blog: "Cannot Generate SSPI Context" error message, more comments for SQL Server

For those looking at working with CRM 3.0:

Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server

You'll still need to do the tasks in 319723 and 326985, however.

Now for the tools:

SetSPN

This tool is invaluable because it quickly allows lookups of Service Principal Names (SPNs) as well as the ability to add and delete them. While other directory service tools allow you to touch SPNs, this command line tool is fast and easy to use. It's needed to verify the proper SPN is set for SQL Server (and for the web server if it's using a different name than it's fully qualified domain name... for instance, reporting.mycompany.com is what you want people to type in when your web server is really myweb01.mycompany.com or something along those lines). You can find this tool in either the Windows 2000 or Windows Server 2003 Resource Kit, whichever is applicable to you.

Windows 2000 Resource Kit setspn.exe setup download
Windows Server 2003 Resource Kit tools download

KerbTray

One of the things that can get confusing is determining what you have Kerberos tickets for. KerbTray can display this information as well as flush out any tickets. When you run it, it sits in your tray and when you double-click on it you can see what tickets you have. Awesome for tracking down exactly WHERE the Kerberos delegation is failing when you're hopping all over the place (like with CRM => SSRS => SQL Server).

Windows 2000 Resource Kit kerbtray.exe setup download
See link above for Windows Server 2003 Resource Kit tools download