Using Kerberos Delegation with SSRS 2008

Many SQL Server Reporting Services (SSRS) implementations use a distributed architecture where the data source for a report does not reside on the same server as the SSRS installation. If you want to use Windows Authentication in your report connections your network must be configured to pass a user’s credentials from the client to the SSRS server then on to the database server. Many people call this the SSRS double hop problem.

To enable this functionality Kerberos Delegation needs to be configured to allow the second hop, this turns out to be quite simple to configure.

Configure the correct Service Principal Names (SPN) to allow the web server service and SQL Server service to authenticate using Kerberos. You must review the existing SPN configuration prior to manually adding SPNs, if you add a duplicate SPN the delegation will not work. The setspn.exe tool in Windows Server can be used to review and edit your SPN configuration.

  • SSRS Server– you’ll need two SPNs (DNS and NetBIOS format) for the HTTP service host header name of the SSRS website, e.g. if your SSRS server is called DevReports you need the following two SPNs:
    • http/devreports
    • http/devreports.yourdomain.com

If these two SPNs do not exist you can added them with the following two commands:

setspn –A http/devreports DomainName/SSRSServiceAccountName
setspn –A http/devreports.yourdomain.com DomainName/SSRSServiceAccountName

  • SQL Server– you’ll need a single SPN on the database server and you must use the correct port number the SQL Server instance is listening on, e.g. if your server is called DevDBServer using the default instance name listening on the default port, 1433:
    • MSSQLSvc/devdbserver.yourdomain.com:1433

If this SPN does not exist you can add it with this command:

setspn –A MSSQLSvc/devdbserver.yourdomain.com:1433 DomainName/DBEngineServiceAccountName

Configure Kerberos Delegation for the SSRS Service Account. If the SSRS website is using the NetworkService account this configuration will be performed on the machine account in Active Directory (AD); if the SSRS site is using a domain account this configuration will be performed on the that domain account. The example will use a domain account.

In the SSRS account’s Delegation tab select the “Trust this user for delegation to specified services only” and add the MSSQLSvc for your database server. You can also use ADSI.exe to make this configuration.

Configure the SSRS rsreportserver.config file on the SSRS server to use Kerberos authentication. Make sure the AuthenticationTypes property is configured to use RSWindowsNegotiate this then restart the SSRS service:

<AuthenticationTypes>
  <RSWindowsNegotiate/>
  <RSWindowsNTLM/>
</AuthenticationTypes>

One last note:

There are many things that could go wrong with Kerberos authentication. One I experienced was some users had IE configured to disable Integrated Windows Authentication. This must be enabled at the client for the delegation to work.

Here’s a thorough checklist for troubleshooting Kerberos delegation issues.

4 Responses to Using Kerberos Delegation with SSRS 2008

  1. payday loans says:

    I want to thank the blogger very much not only for this post but also for his all previous efforts. I found sql-ution.com to be very interesting. I will be coming back to sql-ution.com for more information.

  2. DBA Dave says:

    Thanks! It’s good to know that some of these posts are useful to people…

  3. Dick Heerschap says:

    Great stuff, just one other item we hit, I used the wrong account name and came across the following link which explains why you need to do parts of the above.

    http://support.microsoft.com/kb/811889

    If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

  4. You can find additional info when targetting SSAS 2008 here

Leave a Reply

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