Reporting Services Scale-Out Setup with Kerberos Delegation

A common configuration for SQL Reporting Services is to use a scale-out setup. The reason for this is the performance of the rendering (or pagination) of the reports is relatively processor intensive (at the time of this article SQL Server 2005)

Along with this setup comes an intrinsic problem. By default, SQL Reporting Services uses integrated authentication with impersonation. In other words, SQL Reporting services uses an authentication scheme that integrates with Active Directory in order to provide access and administrative priveliges. When Reporting Services is installed on the same machine as the database engine, this does not pose any issues. However with a scale-out setup, it does. This obstacle is known as the “double-hop” issue. Why? Because user credentials cannot be passed from one machine to another without a setting up Kerberos Delegation. In this article we will go over how to accomplish this setup and the typical obstacles you may need to overcome.

There are a few prerequisites to setting up Kerberos delegation.

1. All computers accessing the application must be in the same domain.
2. The time of all computers must be synchronized using the time service.
3. Kerberos ports must be open if going through a firewall.
4. Client browsers must be setup to allow integrated authentication.
5. Clients must be domain users.
6. All clients must be running Windows 2000 or greater.
7. All client’s browsers must be IE 5+
8. Functional level of the domain is set to Windows 2003 (highly preferable but not required)

Physical Layout

Depending on your infrastructure design, you may choose to separate the web layer from the rendering. For this article, we will separate the web from rendering. Keep in mind, this setup uses a custom application that accesses the reporting services ‘ReportServer’ web service. Our setup will involve 3 boxes. A web box, a rendering box, and a SQL box. (Web01, Rendering01, & SQL01)

Client01 -> Web01 -> Rendering01 -> SQL01

Now for the individual computer settings:

Client01

1. Open IE, Tools -> Internet Options -> Advanced -> (Scroll down and check the box ‘Enable Windows Integrated Authentication’)

Web01

1. Open IIS and open the virtual directory tab and take note of the name of the application pool being used.
2. While still in IIS, open Directory Security -> Edit -> (Uncheck ‘Anonymous Access’, and check ‘Integrated Windows Authentication’).
3. Now expand the application pools in IIS and right click on the one being used ->; properties -> Identity tab -> (we want the application pool to run under the default ‘Network Service’)
4. Open widows explorer and navigate to the directory containing the web application. Make sure the user group that you are allowing access to this entire system is added to the folder’s ACL. For our example we will use: ‘domaindomain users’. Adding our target users to the ACL is necessary for Impersonation.
5. Open the web.config. Make sure the following setting below is set to true. This allows the impersonation of the client that has logged into IIS. Meaning the processes invoked in his session will run under the context of his account.

Rendering01

1. Run the Reporting Services Service under the localsystem account if not already doing so.
2. Open IIS and follow steps 1-5 above for the virtual directory ‘ReportServer’.
3. Follow steps 1-5 above for the virtual directory ‘Reports’. However, you may or may not want to allow all users access to this site because it is typically used for administration. If you do not want to allow access then do not add all users to the directory’s ACL.
4. Open ReportManager (http://rendering01/reports) and make sure all datasources that are referenced by your reports are set to ‘windows integrated security’.

SQL01

1. Run the SQL Server Service account under the localsystem account.
2. Open SQL Server Management Studio and expand the Security (at the server level). Create a new login for the domain group ‘domaindomain users’. Give appropriate access to the stored procedures used to run reports.
3. Add the same user group to the Reporting Services instance in management studio and set permissions accordingly.

Active Directory Setup

Ok, now that we have the individual computers setup, we move onto what can be the more difficult part; the Active Directory setup. What we need to know here, is that each server that access the the tier below it, needs to pass on the user’s credentials. This is not setup by default. In order to set this up we need to tell Active Directory exactly what services that computer is allowed to communicate with. But how does Active Directory know what services are running on a computer? With SPN’s (pronounces “Spins”) or Service Principal Names. SPN’s are a combination of:

ServiceType + ComputerName + Port (sometimes optional) + (Logon As) Context

examples:

• MsSQLSvc/SQL01.yourdomain.local:1433
• MsSQLSvc/SQL01:1433
• HTTP/Web01.yourdomain.local
• HTTP/Web01

These SPN’s are not case sensitive. It is also best practice to create the two versions of the same SPN. One to use the FQDN (Fully Qualified Domain Name) and the other to use the NetBIOS name. While this may be best practice, I’ve seen it work one way or the other.

Probably the most likely culprit for not getting this Kerberos authentication to work is improper or duplicate SPN’s. Duplicate SPN’s can easily be created if the person that install say, SQL, is a domain admin and decides to run the setup under their domain admin account. What happens in this scenario, is the SPN for SQL server is automatically registered under that Domain Admin’s user account. Even if they go back and change the service to run under localsystem, Active Directory will still know of a SPN to SQL01’s SQL Server Service. This creates a duplicate SPN and will break your entire setup. It is almost impossible to find this SPN without a tool to do so. So we are including a tool here.

Search for Duplicate SPN’s

Open a command prompt and using the tool discussed above, run the following from the command line:

[code]
c:> cscript DHCheck.vbs myuser web01 rendering01 SQL01
[/code]

The output that is generated will show you the Active Directory properties for each object you list. What you want to look for here is listed under “Checking for duplicate SPN’s”. You want it to say “No duplicate SPN’s found”. If it does not, you will need to delete these SPN’s before you proceed.

Deleting Duplicate SPN’s

To delete duplicate SPN’s, you need two things. A tool call Setspn.exe (from Microsoft support tools), and a domain administrator account.

Delete the duplicate SPN:

[code]
c:>setspn -d MsSQLSvc/SQL01:1433 domainnamedomainadmin
[/code]

This will delete the duplicate SPN and we can now proceed.

Creating Necessary SPN’s

The SPN setup for this article is very straight forward because we are running our services under the localsystem accounts. All we need to do now is to create the neccessary SPN’s. Since we are running everything under localsystem, the SPN’s will be defined under the computer they are running under. If we were running the services under domain level accounts, we would have to define the SPN’s under the domain accounts. Here are the SPN’s we need to create:

Web01

[code]
setspn -a http/Web01.domain.local Web01

setspn -a http/Web01 Web01
[/code]

Rendering01

[code]
setspn -a http/Rendering01.domain.local Rendering01

setspn -a http/Rendering01 Rendering01
[/code]

SQL01

[code]
setspn -a MSSQLSvc/SQL01.domain.local SQL01

setspn -a MSSQLSvc/SQL01 SQL01
[/code]

Allowing Constrained Delegation

The last step is to allow constrained delegation to the SPN’s we created above.

Allow constrained delegation for the following computers to the SPN’s below them:

Web01

http/rendering01

http/rendering01.domain.local

Rendering01

MSSQLSvc/SQL01

MSSQLSvc/SQL01.domain.local

SQL01

No Delegation Necessary

Viola! Now that wasn’t difficult at all was it?