SQL Reporting Services Double Hop Authentication Problem

Started using SQL Reporting Services recently and found it very useful. I used it to create a Reporting module on our current project with graphs and charts. It came out pretty good. With all the flexibility and the ease with which you can create Reports using Reporting Services, you are sure to like it once you use it.

I came across a strange problem using SQL Reporting Services which I thought should discuss here. I deployed the Reports in my Dev Environment  it worked fine, and also deployed in another Dev Environment it worked fine, then deployed it in Pre-Prod and started getting the following errors:

 

Errors: 

Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors


                                                      OR
 

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'xxx'. (rsErrorOpeningConnection)

Login failed for user ‘domain\\username’. The user is not associated with a trusted SQL Server connection.

 

It was pretty confusing to know what was going wrong. With some R&D using Google came to the conclusion: It’s a Double Hop Issue.

 

What’s a Double Hop Issue?

To explain it in simple terms, when your SQL Reports make a call to the IIS Server  (SQL Reports run on IIS)  using the Authentication set for the Reports and then IIS attempts to make a call to SQL Server running on a different machine, using the same Authentication, it get Authentication error with SQL Server. This is a case of a Double Hop, as it tries to hop around the credentials across different machines.

 

So in my case, I had set Windows Integrated Security Authentication for my Reports and it worked fine as long as my Report Server and the SQL Server which had my Reports data were on the same machine. But if the SQL Database Server was on a different machine than the SQL Reports Server, the Reports had authentication failure due to Double Hop Issue and threw above errors.

 

Solution:

After exploring the web, got some different solutions for this problem.

 

1.       Kerberos
Kerberos is a bit complicated, but a very authentic way of handling the Double hop issue.  To read more on Kerberos and how it can solve the Double Hop issue read this article Kerberos: Solution to Double Hop Authentication Problem.

The problem with Kerberos I had was, as a Developer it’s not within my domain to go and implement or enable Kerberos over the Network.  So I could not use this option to solve my problem. Hence I had to find out another way to get around this Double Hop Problem and make my Reports work. That’s explained in option 3 below.

2.       In case of Windows Server 2003 you can Delegate Authentication
You can read about it here Delegate Authentication and here on How to Allow a User to be trusted for delegation

3.       The easiest and quickest way to get around the Double Hop authentication issue with my Reporting Project was to create a new User account and give it the required rights and privileges to the SQL Database Server and store that User Credentials on your Report Server. This way the Report uses this User Credentials and has no Double Hop Authentication problem. It’s just a bit slower than the Windows Integrated Security option, but at least got my Reports working without waiting for the implementation of Kerberos. I have described below how to Implement this option of storing the User Credentials on the Report Server.

 

How to store the User Credentials on Report Server to solve the Double Hop Issue?

 

Step 1:

                Create a New User or use an existing User who has required rights on the Reports Database (the database from where you Report will pull the data)

 

Step 2:

                Access your Report Manager Web UI on the Report Server.  You can do that by going to the following URL on your Report Server

 

                http://servername/Reports


Step 3:

                Navigate to the Data Source that is used by your Reports.

 

Step 4: 

Click on the Data Source and then click on the General Tab in the left panel if it is not already selected.

 

Step 5:

                Select the Credentials stored securely in the report server option and enter the credentials

 

Step 6:

                Make sure the Use as Windows credentials when connecting to the data source option is checked as shown in the following image.

 

 

Step 7:

                Click on Apply

 

This should take care of the Double Hop Authentication issue with SQL Reports and you should be able to see the Reports even if your Report Server and your SQL Database Server are on different machines.

 

Hope that helps!

Have a Nice Day

Robert

 

 

For Further Reading

Credentials and Double Hop 

http://mkdot.net/blogs/dejan/archive/2008/08/20/credentials-and-double-hop.aspx 

http://wendyverse.blogspot.com/2009/06/double-hop-issue-net-to-iis-to-sql.html

 

Kerberos - solution for double hop authentication 

http://mkdot.net/blogs/dejan/archive/2008/03/07/kerberos-solution-for-double-hop-authentication.aspx 

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

 

Allow a user to be trusted for delegation ( in Windows Server 2003) 

http://technet.microsoft.com/en-us/library/cc739474(WS.10).aspx