JoelBlogs - Joel Jeffery's Microsoft 365 Blog

Microsoft 365, SharePoint, Teams and Office 365 Architecture, Development, Administration and Training

  • Home
    • Sitemap
  • Articles
    • #SPThingADay
    • SharePoint Online
      • SharePoint Online – Drag and Drop and Large File Uploads
    • SharePoint 2016
    • SharePoint 2013
      • Content Database Changes to the AllUserData Table
    • SharePoint 2010
      • Administration
        • Disable CRL Checking
        • Excel 2010 & PowerPivot
        • Limits & Thresholds
        • PeoplePicker AD Errors
        • Recycle Bin Behaviour
        • Renaming a Server
        • Service Pack 1
        • Unattended Installs
        • Uninstall All SharePoint 2010 Solutions via PowerShell
        • User Alert Management
        • Virtualised SharePoint
        • Visio Stencils for Administrators
      • Development
        • Audience Membership Workflow Activity
        • Base Types, Lists & Content Types
        • BCS & Offline Sync
        • Debugger Skipping Lines
        • Development Laptop Spec
        • Enabling JavaScript IntelliSense
        • Event Receivers & Deployment Jobs
        • FavIcons & SPUrl
        • Google Maps Sandbox Web Part
        • Group By Content Type for List Views
        • Locale Stapler / Master or Default Locale
        • Removing Default Editor Parts
        • Sandbox Embedding Resources
        • Solution Sandbox Introduction
        • SPPersistedObject
        • Restoring Deleted SPSites in SP1
        • SPWebConfigModification 1
        • SPWebConfigModification 2
        • STSADM copyappbincontent vs. Install-SPApplicationContent
        • Workflows for Beginners
        • Workflow InitiationData Seralizer
    • SharePoint 2007
      • Alternate Access Mappings
      • Excel Services
      • Excel Services UDFs & Excel Client 2007
      • Experiences from the Field
      • InfoPath & Forms Server
      • Kerberos & SSRS
      • Records Management
      • Web Application Service
      • WSS vs MOSS
  • Training
    • SharePoint Admin Links
  • Downloads
    • Summary Slides for PowerPoint
    • CodePlex Projects
      • Audience Membership Workflow Activity
      • Google Maps Sandbox Web Part
      • Group By Content Type in List Views
      • Locale Stapler / Master or Default Locale
      • SharePoint Outlook Connector
  • Hire Me!
    • MCP Transcript
    • Résumé/CV

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

December 2, 2009 by Joel Jeffery

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:

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

      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:

            • 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”
          • 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.

            Filed Under: SharePoint Tagged With: Active Directory, Kerberos, Reporting Services, SetSPN, SharePoint, SharePoint Architecture, SSRS

            Joel is a full-stack cloud architect who codes. He is a Microsoft Certified SharePoint Online, SharePoint Server and Azure specialist and Microsoft Certified Trainer.
            He has over 20 years' experience with SharePoint and the Microsoft .NET Framework.
            He's also co-founder of Microsoft Gold Partner JFDI Consulting Ltd. Read More…

            Recent Posts

            • Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections
            • SharePoint PowerShell Tip #1 – Select-Object and FieldValues
            • Popular Misconceptions – Microsoft Teams relationship with SharePoint
            • Course: Microsoft 365 Certified Teamwork Administrator
            • Audience Targeted Searches in Modern SharePoint Online
            MCT 2020-2021
            Microsoft Teamwork Administrator Associate
            Joel's Acclaim Profile
            Joel's Microsoft Profile

            Tags

            Administration Architecture Certification Cloud Development Information Architecture intranets MCP Microsoft Microsoft Architecture Microsoft Azure migration Mobile Development MOSS MOSS 2007 office365 Office 365 Office 365 PowerShell SaaS SharePoint SharePoint 2010 SharePoint 2010 Training SharePoint 2013 SharePoint Administration SharePoint Administrator SharePoint Architecture SharePoint Designer 2010 SharePoint Developer SharePoint Development SharePoint Online sharepointonline SharePoint Search SharePoint Training SharePoint Videos Silverlight SOA Solution Sandbox SPThingADay TechEd 2007 Training Videos Visual Studio 2010 Windows Phone 7 WSS

            Copyright © 2022 Joel Jeffery, SharePoint Architect