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