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 and Server B

  • 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:

      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:

      Raise Domain Functional Level

      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:

      Trust Computer for Delegation

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

      SETSPN.EXE - Setting Service Principal Names

      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
      • Server B
        • Primary IP address and FQDN of: SERVERB.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:

          1. setspn -A MSSQLSvc/SERVERB:1433 JOELBLOGS\\SQLAgent
          2. setspn -A MSSQLSvc/SERVERB.JOELBLOGS.LOCAL:1433 JOELBLOGS\\SQLAgent
          3. setspn -A HTTP/REPORTS JOELBLOGS\\SSRSSvc
          4. setspn -A HTTP/REPORTS.JOELBLOGS.LOCAL JOELBLOGS\\SSRSSvc
          5. setspn -A HTTP/SERVERA JOELBLOGS\\SPConfigSVC
          6. setspn -A HTTP/SERVERA.JOELBLOGS.LOCAL JOELBLOGS\\SPConfigSVC
          7. setspn -A HTTP/INTRANET JOELBLOGS\\ContentWebAppSVC
          8. setspn -A HTTP/INTRANET.JOELBLOGS.LOCAL JOELBLOGS\\ContentWebAppSVC

          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:

            • 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”
          • Editing the Registry to tell SSRS to use Kerberos (Really? I mean, *really*?)
          • 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.

            Comments

            1. Michelle says

              We have a very similar situation that you are describing above and I finally got it working but only with accounts from Domain1. Our second domain (Domain2) will prompt us for credentials. Here is the full scoop.

              We have a SharePoint 2007 environment (2 front ends, 1 SSRS, 1 Excel services, 1 Central admin) that uses two Active Directories with different domains for authentication. One is the primary domain (Domain1) that the SharePoint Servers reside in. The secondary active directory domain (Domain2) enables a different domain of users access to the site. There is not a problem when users use either account to access our site. The two front end servers sit in a different VLAN than the rest of the servers.

              We also have a SQL Server Reporting Services environment. When the users try to run a SQL server reporting services report with the secondary domain account (Domain2), it does NOT work. It only works with the Default domain. When I try to run a report directly by clicking on the RDL, I get this message: An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. When I run the report with the SQL server reporting services “Report viewer”, it does not allow me on the page.

              The SSRS has been integated and has no problem connecting. We are using integrated authentication and Kerberos.

              We have a on-way trust to the secondary domain accounts. I have read that we need a two-way trust to make this work. That is not possible in our environment. What else can I do? Can I use SSO or do I need to do something that is custom? Thanks in advance.

            2. James says

              Hello;
              You article is missing a few things. I was able to get a 5 server farm (MOSS 2007) up and running in sharepoint integrated mode. My reports (rdl files) work in both my FBA (Forms Based Auth) AND my Windows Auth site. We are running Kerberos as well.

              Truthfully you have to jump through a lot of hoops (not all documented in forums). I found that our setup works best with SQL 2008 R2, RS Add in SP2, and MOSS 2007. We are using 2 wfe 1 apps, 1 sql cluster, and 1 ssrs server.

            Leave a Reply

            Your email address will not be published. Required fields are marked *

            You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>