SQL Server Reporting Services and Subscriptions: Who are you again?

Goal 

In the last article I dealt with the one hop limit of authentication in NTLM and how it pertained to SSRS and determining who the user is that is running the report. In this article we will deal with the same need to show a user thier data and also the need to do Subscriptions.

We still have the need to write SSRS Reports that identify who a user is by their network identity and display appropriate information they should see. This information can be Projects that are assigned to a project manager or maybe Grants to a Grant Manager. As you remember we are using the User!UserID in SSRS to determine the user and paste their name into the where clause.  We also want to allow these users to schedule reports through subscriptions is SSRS. Key requirement here is you want the user to schedule reports NOT push reports to the user when you want (Not Data Driven Subscriptions, Event Driven Subscriptions).

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.  IIS 6.0 or 7.0. You are using Shared Datasource in SSRS to stored database info.

There are two types of Subscriptions in SSRS 2005 and 2008: Standard and Data Driven. Data Driven allow you to have a table and push out reports to users in that table when you set it up to run. That will not work here.  We have to use Standard Subscriptions that allow users to setup their own schedule.

Issue

            The problem with subscriptions is that it requires you to use a hard coded user to access the data. It has to use Stored Credentials in our shared datasource. You cannot use windows authentication when hitting the database. Well actually you can but it will use whatever SQL Reporting Services runs under. And using the User!UserID we used before, if we change nothing and someone schedules a report, the user will get a report for the owner of SQL Reporting Services because that is the user that will evaluate the report. SSRS runs the subscription, not the user. So to get the same results as before, evaluate User in the report, show them data only they should see and do it with subscriptions where SSRS is running the report for us is not possible.  

            So to summarize and make the problem crystal clear:

1)      With SSRS Subscriptions you have use stored credentials in the datasource.  You cannot use the users Windows Credentials because they are not running the report SSRS is.

2)      User!UserID will not work running under subscriptions in the main select of the report because SSRS is running the subscription using the stored credentials to hit the data and using the SSRS account to hit the actual report.

It may seem that our two requirements cannot be met under standard subscriptions but that is not the case, there is hope yet.

Resolution

            Using User!UserID in the main select of the report will not work, because that is evaluated when the report is run by SSRS at the time the Subscription was set to run. BUT using User!UserID in the Parameter Select of the report will work. The one part of the report that is run by the user in Subscriptions is to select parameters. If you use User!UserID to filter the entities like Projects or Grants that a user can select, then you can have the user select their projects first (or have it default to select all) and you never have to worry about what account is running subscriptions or used to hit the database. 

           
            To show you exactly what I mean lets use a real world example. I had a client that needed budget vs actual reporting. The report had to show a range of period selected by the project manager and let them run any project (called an RC for them) that is assigned to the project manager. The project manager and RC relationship was saved in the xBPReportAccess table. It had the windows id and the RC.
            What I did was dynamically setup the RC Dataset in the report to query that table using the User!UserID as shown below. But I also setup the RC Dataset to supply the parameter to the report for RC shown below. It is required so a user has to setup the parameter even for subscription. They can select one to all (allows multiples).

Setup of RC Parameter

The user has to select that parameter but I build it like this:

="Select subacct as RC, rtrim(ltrim(subacct)) + ' ' + rtrim(ltrim(dbo.raffaGetSegmentDescription(subacct, 2))) as RCDescription from xBPReportAccess where userid = UPPER(rtrim(ltrim(Substring('" + User!UserID + "', charindex('\', '" + User!UserID + "') + 1, 30))))"

Insuring the user can only select from their RC’s.



Dynamically building RC Select using User!UserID.  Users have to select this to setup subscription so User!UserID will be evalued as the Project Manager properly.



The actual select from the main report is just a stored procedure that accepts the begin and end period and the list of RCs as a parameters.

Conclusion 

Using Report Services to determine the user and dynamically pass it to the SQL statement to select parameters by using User!UserID allows you to setup subscriptions and have a report determine what data a user can see at the same time.

 I am going to move on to SQL Server and Using Linked Server and how the one hop limit affects it next.  

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.