The Double-Hop Authentication Problem

Project Server. Remember installing it? Well, your Project guys will remind you once in a while that it's something to keep abreast of.

• By Joshua Jones
• 08/23/2010

For many seasoned DBAs and system administrators, the double-hop authentication problem is a known issue. However, that doesn't make it any less of a pain to resolve. For those who don't know what I mean, consider this scenario:

Your environment has three servers:

• ServerA hosts a SQL Server 2008 Database Engine instance.
• ServerB hosts SQL Server Reporting Services (2008).
• ServerC hosts a custom Web application that utilizes the SSRS reports.

In this scenario, a developer writes a brand new report using the Visual Studio 2008 development studio. The report relies on a database on ServerA. They design the data source for the report to rely on Windows Authentication to the SQL Server instance on ServerA. They then deploy the report (which works on their design machine quite well) to ServerB. They create the custom data source on ServerB, and correctly configure the report to use the data source. However, when they attempt to run the report, they get an error similar to the following:

An error occurred during client rendering.
An error has occurred during report processing.
Cannot create a connection to data source 'MyDatabase.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Troubleshooting the issue has resulted in confirming that the server is setup correctly, valid credentials are being used in the data source, and the error always occurs on the server but not on the development machine. Often times the key piece of information is the last bullet; clearly ANONYMOUS LOGON should fail. The question is, why is that credential being passed in the first place?
While this scenario has about a hundred different permutations (with SSRS, third-party applications, and even linked servers), the problem is always the same; Kerberos Authentication. The anonymous logon is the pointer here; it means that the supplied credentials can't be used, so the default is to fallback to anonymous logon attempts.

The solution is easy really. You have to set up Service Principle Names for the SQL Service, enable delegation for the machine AND accounts in Active Directory, and restart the services. And for SSRS, you have to modify the RSReportServer.config file. Simple, no?
Let's break it down a little more.

Service Principle Names
Service Principle Names, or SPNs, are best described as portable authentication tokens. They are created by combining a service name (and TCP port if needed) with a machine name, and registering that with Active Directory so that all of the other machines in the domain understand the security context of requests coming from that machine. Registering an SPN is a command line function. You use the "setspn.exe" tool that is a part of the Windows Support Toolkit (which can be downloaded from Microsoft). The syntax is pretty simple:

setspn -a <servicetype>/<computername>.<domain>:<port> <accountname>

and

setspn -a <servicetype>/<computername>:<port> <accountname>

SQL Server will need an MSSQLSvc SPN. Reporting Services, in addition, will need an HTTP SPN. It's important to note that both SQL Server and IIS actually try to register SPNs when they start. If they are running as LOCAL MACHINE, the can successfully register; however, this will NOT work for the double-hop scenario. A domain account must be used to get delegation to work.

So, to create SPNs for SQL Server, the command would look something like this:

setspn -A MSSQLSvc/ServerA.mydomain.com:1433 MYDOMAIN\SQLServiceAccount
setspn -A MSSQLSvc/ServerA:1433 MYDOMAIN\SQLServiceAccount

Notice you're actually setting up TWO different SPNs: One for the Fully Qualified Domain Name (FQDN), and one for the NETBIOS style name. This will help resolve any lookup issues in the domain. For a named instance of SQL Server, you'll need to modify the command slightly:

setspn -A MSSQLSvc/ServerA.mydomain.com:Instance1 MYDOMAIN\SQLServiceAccount
setspn -A MSSQLSvc/ServerA:Instance1 MYDOMAIN\SQLServiceAccount

This simply specifies the named instance Instance1 where the port designator was. This particular syntax works for SQL Server 2008 and on; for older versions, you'll need to specify the TCP port that the named instance is using (and be sure to "statically" define that port in the configuration of the instance using the SQL Server Configuration Manager).

In order to set up the SPN for RS, the commands are nearly identical, with only the SPN type and service account changed:

setspn -A HTTP/ServerB.mydomain.com:1433 MYDOMAIN\RSServiceAccount
setspn -A HTTP/ServerB:1433 MYDOMAIN\RSServiceAccount

There; provided there are no errors, we're all set. Just to double check, run the setspn command with –L to list all of the SPNs registered in Active Directory to make sure they look right; if they don't, drop the bad SPNs (-D) and retry.
 
Account Delegation
Now, you have to go into Active Directory Users and Computers and enable delegation for the accounts and machines being used. Find the service account in question, right-click on it and select Properties. Then, simply go to the Account table, and under Account options, make sure the "Account is sensitive and cannot be delegated" checkbox is cleared (see Fig. 1).

Figure 1. Be sure to check the right box under Account options in the Account tab of the SQL Service Properties dialog. (Click image to view larger version.)

Click OK. Then, navigate to the computer account, right-click and select Properties. Then select the Delegation tab (which will only be present if a registered SPN exists; see Fig. 2), and enable delegation. Note that you can restrict delegation to specific services and computers, if needed.

Figure 2. Now, go to the Delegation tab and enable delegation. Simple, right?. (Click image to view larger version.)

Click OK, and you're done.

RsReportServer.Config
The final piece (for SSRS) is letting Reporting Services know to use the correct authentication method. You should be able to find the file in the installation directory (default C:\Program Files\Microsoft SQL Server\MSRS10. MSSQLSERVER\Reporting Services\ReportServer). Simply open the file in your favorite editor, and replace

<RSWindowsNTLM/>

with

<RSWindowsNegotiate/>

Then simply restart services that you've changed. In this case, both the SQL Server and the Reporting Services services will need to be restarted.

Hopefully this will help you get that tricky RS installation up and running. Until then, have fun!