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.