SQL Server Reporting Services (SSRS): A Tale of Two Servers

Goal 

Need to write SSRS Reports that identify who a user is by their network identity and display appropriate information they should see. This can be a project manager only seeing the projects they managed or a grant manager seeing only the grants they manage. Target information to an individual based on who they are.    SSRS should be a natural fit for this because it is Windows Authentication Based.

Environment

SSRS 2005 (or 2008) is installed on two Servers: One Server is the IIS Web Server, the other is the back end database server.  NTLM Network Network.  IIS 6.0 or 7.0.

Issue

                You use the SQL statement behind the report to identify the user as in the following:

Select project from projectmanagers where manager = system_user

or

Select Grant from grantmanagers where manager = suser_sname()

You are using SQL Server to tell you who the user is. Reporting Server must pass windows credentials to another Server to evaluate the statement properly. You have a table that has the windows id of the manager and what entities they should see.  You use SQL Server to evaluate the user to the text Domain Name/User Name and then filter you data based on a table of users. I have used this model over and over again in different systems to identify who see’s what.   

Now if you have SSRS  installed on the same server as the database server, the scenario described works. No Problems. It is when you use the two server model that the script can’t seem to identify who is running the report.  As you know it is not recommended to install IIS on same machine as the Database Server. It is a security Risk, so most places will have two servers: A web server and a Database Server. This makes it necessary for the process to pass your credentials from the workstation to the SSRS Server and then to the Database Server. This is a two server jump.  

The problem you run into is the single server limit to authentication passes across machines. In an NTLM Network you cannot authenticate once then hijack as many machines as you want. You are challenged for your credentials each new machine. A Process cannot just keep passing along your credentials. There is a single server jump limit. So from your workstation to the Windows Server housing SSRS takes up that one jump. It can't just pass along to the Database back end.

Resolution

The best solution here is to determine the user in the SQL Report itself, build the select statement on the fly in the report  pasting the user id in the statement  or to use parameters to pass the user to the sql statement. SQL Reporting Services provides an internal way to grab the windows user id:  User!UserID.   In Our Original Example we would have:

Select * from ProjectTable where Manager = suser_sname()

Modified to evaluate in SQL Reporting Services:

="select * from ProjectTable where Manager = '" & User!UserID & "'"

Notice you can build a SQL Statement in the Data Window in a String and paste in the evaluation of User.UserID in key parts to make it work. This makes Reporting Services Determine the User First, then pass it to the sql statement to pull the appropriate records.

 Image of Original Report Select:



Image of New Report Select:

Conclusion 

Using Report Services to determine the user and dynamically pass it to the SQL statement avoids the two server jump issue with NTLM in a Two Server Environment. You can also pass it in a hidden parameter, same concept.

 I am going to stick with this two server jump issue and talk about subscriptions and running reports that need to determine the user as well next.