Microsoft BI and Kerberos

I was briefing a bunch of partners the other day, and during coffee I went off piste and did a short overview of my presentation “It’s not about the Technology” to the BI MSc course at Dundee University.  However sometimes technology does get in the way especially when it comes to implementation and security, so  not planning for these will upset deadlines and cause projects to over run. 

The most common problem for a typical large BI project is the “double hop” security problem that occurs when reporting services is used, so a client on PC (A) access reporting services on server (B) this one hop is fine as windows authentication will work, however in many scenarios the data in the report is on another server (C), and windows authentication will treat this second hop as anonymous, and authentication will fail.

The standard approach to this is to use kerberos authentication, which overcomes this problem without the users having to login again, or use aliases to connect to the source data. The headache for BI professional like us is that this is another skill to learn and even if we know what to do we are unlikely to be give the permission to make the changes to the servers in a production environment.  However the infrastructure guys who can do this don’t know too much about reporting services and may have other priorities.

I can’t help with the priorities but I do know of the key resources you and the infrastructure guys need to make the right changes to get reporting services working and to troubleshoot any potential problems, and that is this whitepaper.  The whitepaper also works just as well for reporting services in SQL Server 2008 R2, and all parts of the BI stack where a double hop is needed, not just reporting services.

Hopefully this will help to clarify Kerberos, but if not this post will at least signpost the resources next time I get asked about the “double hop” problem!