Kerberos, Delegation and SQL Reporting Services

Here is a very quick overview of the Service Principal Names (SPNs) you need set-up to get SQL Reporting Services working.

Assumptions:

• Domain Functional Level is "Windows 2003".
• SQL Reporting Services and SQL Server (the database engine) are installed to different machines.
• Your domain is "acme.com".
• The account running SQL Reporting Services is "acme\SQLReportingServices" (it does not matter which account SQL Server is running under).
• SQL Reporting Services should NOT be run under a system account ("Local System" or "Network Service").
• Your SQL Server machine name is "SQL01".
• Your SQL Reporting Services machine name is "RPTS01".
• The SQL Server instance holding the database is running on the default port (1433).

You should create four SPNs with "setspn.exe" as follows:

• setspn -a MSSQLSvc/SQL01:1433 acme\SqlReportingServices
• setspn -a MSSQLSvc/SQL01.acme.com:1433 acme\SqlReportingServices
• setspn -a http/RPTS01 acme\SqlReportingServices
• setspn -a http/RPTS01.acme.com acme\SqlReportingServices

You can substitute values to match your environment given the assumptions above. Note the capitalisation of the Service Class prefixes of the SPNs ("MSSQLSvc" and "http"). These are important.

After you have set-up the SPNs, the "acme\SQLReportingServices" account should be trusted for delegation against those SPNs. This is done in the Properties of the User Account object in Active Directory.

Please note it is best practice to set SPNs against dedicated Domain accounts rather than machines (as per above example). Having SPNs registered to Domain accounts allows you to enable Kerberos delegation in a load balanced environment. If your SPN is registered against a machine you can't delegate from other machines so you can't load balance. Services you wish to enable for delegation should not run under "Local System" or "Network Service". If the service is running under such an account, you have to set the SPN against the machine which limits your options for load balancing.

"setspn.exe" is available in the Windows Server 2003 Support Tools, more information here:

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

Delegation with Kerberos can be tricky at times. It makes complete sense when you get your head around it but the initial learning curve can be steep. The first thing to do is understand your network "hops". A "hop" is a call between services on which the clients credentials will be passed on the clients behalf.

A great document for understand and troubleshooting Kerberos can be found here:

www.microsoft.com/DownLoads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en