The wonders of Kerberos, SharePoint and SQL Reporting Services

OK, so we’re trying to get a little more modern here, decreasing our reliance on Oracle Reports (yetch!) and Access based reporting (shudder) and move on to the spiffy SQL Server Reporting Services, only 2005, but hey, we have to start somewhere.

Given that our Intranet is SharePoint based (full MOSS2007), I of course wanted to run SSRS in integrated mode, so along came Kerberos.

I’ve had Kerberos working for SharePoint for some time now, so thought it would be a doddle to get SSRS integrated, boy was I wrong.

First up, turned out that none of our SQL servers had registered server principal names with AD, so:

Setspn –a MSSQLSvc/NETBIOSNAME_IN_UPPERCASE:PORT DOMAIN\DOMAINACCOUNT_THAT_SQL_IS_RUNNING_UNDER
Setspn –a MSSQLSvc/FQDN_NAME_IN_UPPERCASE:PORT DOMAIN\DOMAINACCOUNT_THAT_SQL_IS_RUNNING_UNDER

Note: the inclusion of the PORT, not all of the documentation mentions that for SQL…
Note: uppercase – yes, dispite the MS documentation stating that “as long as you’re only using Kerberos against Windows machines, it’s case-insensitive”, I found that it didn’t work until I pushed the case up. No I haven’t tried combinations of case to find exactly where the case is important. Though according to the Kerberos tickets on my PC, my domain ticket has an all uppercase domain, but one of my SQL tickets has only the server name (and not the domain) in uppercase… go figure.

Having discovered the case sensitivity, I had to go back and re-do all my SharePoint SPNs and just to make sure, I doubled up on the ports too:

Setspn –a HTTP/NETBIOSNAME_IN_UPPERCASE DOMAIN\DOMAINACCOUNT_THAT_THE_SP_APPPOOL_IS_RUNNING_UNDER
Setspn –a HTTP/FQDNNAME_IN_UPPERCASE DOMAIN\DOMAINACCOUNT_THAT_THE_SP_APPPOOL_IS_RUNNING_UNDER
Setspn –a HTTP/NETBIOSNAME_IN_UPPERCASE:80 DOMAIN\DOMAINACCOUNT_THAT_THE_SP_APPPOOL_IS_RUNNING_UNDER
Setspn –a HTTP/FQDNNAME_IN_UPPERCASE:80 DOMAIN\DOMAINACCOUNT_THAT_THE_SP_APPPOOL_IS_RUNNING_UNDER

And now it all works, yay, with the current user authenticating all the way back to the DB.

Only took a few days to track that down. Kudos to the MS team producing the Troubleshooting Kerberos Delegation in Windows document which took me through a very rigorous set of steps to ensure I was configured correctly (though more help on what Kerberos should look like in NetMon would be good!) though doesn’t mention case-sensitivity, had to dig that up HERE.

Now we can get back to concentrating to actually delivering reports…