SharePoint Fun with Kerberos and SQL Server Reporting Services (SSRS)

Fun.

I recently had lots of fun with this at a client site. Especially if you redefine the dictionary definition of the word “fun” to some sort of diametrically opposed antonym.

The Platform

• Server A: Windows 2008, IIS7, MOSS 2007 SP1.
• Server B: Windows 2008, SQL 2005 SP2 with SQL Server Analysis Services.

The Task

Configure SQL Server Reporting Services (SSRS) for SQL 2005 SP2 on Server A in SharePoint Integrated Mode.

The Problem

There are SSRS Reports that access a SSAS Cube which uses ID of the current user as part of a filter. In other words, we must log into SSAS as the Windows Account of the current SharePoint user.

Why this Hurts

Default Windows Authentication (Negotiate/NTLM) cannot pass your credentials from one server to another. When you log into a web server, it knows who you are. When the server then has to log in to a database as you, it cannot send your credentials down the line to the next server. This is referred to as the “double hop” problem. It’s not a SharePoint problem, it’s a fundamental design constraint of N-tier systems since Active Directory has existed. It works OK on a single server environment, but when you deploy this to a production system with two or more tiers (i.e. a separate database) we hit the brick wall.

How to Get Out of It

There are generally two mainstream solutions (yes, there are others, I know!). My preferred solution, let’s call it “Solution A” is this:

• Don’t do it.

Seriously. If you’re designing your own applications, it’s frequently best practice to have a small set of (usually *one*) service accounts that you use for all server-to-server access. But that won’t work here. SSAS is not my application; the reports and analytics are not up for being edited. So we must take another choice.

Which brings me to “Solution B”, thus:

• Use Kerberos – the Greek Triple-Headed Hate-Dog from Hell.

How Kerberos Gets Round The Problem

Kerberos is an MIT-invented protocol for authentication developed in the 1980’s. It’s gone through many iterations, and since Windows 2000 it has been the default authentication method in Active Directory Windows installations. We want to use a Kerberos technique called “Constrained Delegation”.

With NTLM authentication, Server A would authenticate you, and your client would send a one-way hash of your credentials, called a “token”. Server A would then be unable to pass similar token to Server B, as it has no way of reverse-engineering your password from the token, to generate a token of its own.

Kerberos, however, works on a “ticketing” system. If we configure Kerberos correctly, then your client will send Server A a different kind of one-way hash of your password: instead this is a “ticket” with a time-to-live. That means if Server A is appropriately privileged in Active Directory, it can send your ticket on to the next server down the chain – i.e. Server B – if the ticket has not expired.

Kerberos Caveats

Kerberos ticketing is time-based, so it needs to have all the system clocks of all the participating machines in sync. There are a few other requirements:

1. The Key Distribution Center service should be running on your Domain Controller(s)
2. The Functional Level of your Active Directory should be set to Windows Server 2003 or above
3. Computer accounts for Server A and Server B need to be “trusted for delegation” in Active Directory Users and Computers
4. Service Principal Names need to be created for every service for which you want to allow delegation

Points one and two above are free if you’re running Windows 2008 Active Directories, which we were. So that’s all good. You should note that the default for a Windows 2003 Active Directory domain is to run in Windows 2000 Compatibility Mode. If you need to, you can raise this in Active Directory Users and Computers like this:

You can accomplish step 3 – trusting the computer accounts of Server A and Server B – through Active Directory Users and Computers –> Computers –> Server A, like this:

Step 4 requires some command-line action with a tool called SETSPN.EXE.

And that’s where things get tricky. Luckily for us, SETSPN.EXE comes with Windows 2008 Server. However, if you’re using 2003 you’re not going to be so lucky. You’ll need to download it from the Support Tools for 2003 Server.

Server Set Up

There’s a lot of moving parts going on here, so let’s have a look at how I intend to set some of this up.

DNS

• Server A
  • Primary IP address and FQDN of: SERVERA.JOELBLOGS.LOCAL
  • Secondary IP address and FQDN of: REPORTS.JOELBLOGS.LOCAL

Active Directory

• Windows 2008 Active Directory Functional Level
• Our Active Directory Domain is called JOELBLOGS (or JOELBLOGS.LOCAL)
• Server A and Server B trusted for delegation
• Domain Accounts created for the main services:
  • SQL Server: JOELBLOGS\SQLAgent
  • Reporting Services: JOELBLOGS\SSRSSvc
  • SharePoint Database Access Account: JOELBLOGS\SPConfigSVC
  • SharePoint Content Web Application Pool Account: JOELBLOGS\ContentWebAppSVC

Service Principal Names

Putting it all together, here’s the commands I issued at a command prompt on my Domain Controller to create the relevant Service Principal Names for delegation to work:

What I Didn’t Do

Actually, I tried many, many things; some of which would never work. Luckily, remembered to create a virtual machine snapshot before applying the changes… and so managed to “undo” them and roll the machine back and start again! This is what I tried and rejected:

• Following all the advice in Microsoft Knowledge Base Article KB938245 including:

• Removing Kerberos from the IIS Metabase for the Reports Server (No. Just don’t. This is silly. It sets NTLM explicitly as the authentication provider here. So, why did we just spend all that time configuring Kerberos then? Quite. Don’t do this.)

• cscript adsutil.vbs set w3svc/##/root/NTAuthenticationProviders “NTLM”

After 12 hours of faffing around with SQL Reporting Services 2005, I decided to quit, and take the easy path and install SQL Server Reporting Services 2008 on Server A, using the SQL Server 2005 on Server B.

SQL Server Reporting Server 2008 Unpleasantness

Before you install SSRS 2008 on top of SharePoint, you might want to install the SharePoint Option Pack for Reporting Services 2008, rsSharePoint.msi. One problem with this is that the Reporting Services Add-In setup stalls when you run it on Windows Server 2008. You can try running it as Administrator, but it will likely fail. Instead you need to launch it from an Administrative Command Prompt, with the following:

rsSharePoint.msi SKIPCA=1

This will unpack the option pack into your temp directory. For me, it put it under “%temp%\\1”. Yes. That was indeed odd, that extra numerically-named folder underneath… Heigh-ho! Then you need to change into that directory and run:

rsCustomAction.exe /i

Now the option pack should start installing without too much going wrong, and you can install SSRS 2008 on your SharePoint Web Front End server.

Things should start working at this point. Unfortunately, there are a few problems with persuading SSRS 2008 to use Kerberos authentication. Some things get a bit missed out in the RSReportServer.config file that drives how Reporting Services authenticates you. Specifically, line 3 below is missing from the file:

   1: <Authentication>

   2: <AuthenticationTypes>

   3: <RSWindowsNegotiate/>

   4: <RSWindowsNTLM/>

   5: </AuthenticationTypes>

   6: </Authentication>

After this, everything starts authenticating properly.

Tidying Up

You may find yourself sitting in a shotgun shack. You may find yourself with a working SQL Server Reporting Services 2008. And you may ask yourself, “Well, how did I get here?”

You may also find yourself with Kerberos authentication broken because of duplicate Service Principal Names (SPNs). To find out if this is what’s preventing your servers from authenticating, you can execute:

SETSPN.EXE –X

You can then run

SETSPN.EXE –D <ServicePrincipalName>

for each duplicate you want to remove.

Best of luck! And if you find yourself stuck, banging your head against a brick wall trying to get SSRS 2008 working with Kerberos on Windows Server 2008 in SharePoint Integrated Mode, then you now know someone who’s got it working: me.