The issue is a simple one. Reporting services gives you the ability to schedule a report for off line delivery. A great feature but there is a snag. If your data resides in an Analysis Services Database, the only method of connecting to the database is via an AD user name. However an AD requires that either a user is logged onto the system or that a service is impersonating a user. If you try to create a subscription to a report that uses Analysis Services as a datasource, you will get a message box with the following message.
“Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid”
Now a user logging onto the system defeats the whole point of a scheduled report. How about impersonating a user? You sort of have some options here. While reporting services does not give you the option of saving credentials with your subscription, you can set the specific credentials a data source must use. So if you had a data source per user, you could set the current user credentials per data source, check the “Use as NT credentials” and you are away. The problem is all those data sources and yes, you guessed it, multiple copies of the same report, with each report set to an individual data source. This would be a nightmare to maintain with anything more than a few users. You could go do the route of a data source per Analysis Services role, but you still end up with the potential for a lot of duplicate reports for every unique role.
The solution we came up with is not idea, but overall, we thought it was better than the above. Let me know if you agree.
Reporting services loves expressions. Almost every object has an expression box, including the connection string field. In addition, inside in Reporting Services there is the User object. The function User!UserID will return the current network name of the logged in user. When you connect to an Analysis Services cube, you can use the “roles” connect string property to connect as a particular role and not user. Now the problem is that we have to associate that user ID with particular role. For the client this was a fairly straight forward task and they provided a SQL table that I could connect to that would associate a user ID with a role. With all of this in place, the steps are
1) Create a new data source and dataset on your report. This should connect to the SQL Database that contains the user id to roles map. The SQL for this should look something like; “SELECT role FROM AS_ROLES WHERE (UID = @GetUID)” I called this dataset “role”
2) Assuming you are working in RS 2008, you can switch to the Dataset Parameters tab and for the parameter value set it equal to User!UserID
3) Create a new report parameter (called UserRole in my case) and set its available values and default values from the dataset role. For now you might want to keep this as a visible parameters, but don’t forget to unset this prior to deployment to production.4) Now in your connection that connects to the cube, edit the connection string using the expression box and set it equal to ="Data Source=;Initial Catalog='Adventure Works DW';Roles=" & Parameters!UserRole.Value
5) Compose the rest of the report as normal.
When you run the report, the following sequence of events kicks in
a) The data set described in 1 above will execute to return a single row that contains the role of the current user
b) The parameter UserRole get’s it value from a) above
c) The connection string described in 4 now connects using the parameter described in b
d) Your data set return data
Now you are not quite there yet. After deploying the report, you have to not edit the data sources of the report. For t he data source that connects to the Analysis Services cube, select the “Credentials stored securely in the report server” option and enter in a user name and password that has access to the cube. Now check the “Use as Windows Credentials when connecting to the data source.” option.
a) The data set described in 1 above will execute to return a single row that contains the role of the current user
b) The parameter UserRole get’s it value from a) above
c) The connection string described in 4 now connects using the parameter described in b
d) Your data set return data
Now you are not quite there yet. After deploying the report, you have to not edit the data sources of the report. For t he data source that connects to the Analysis Services cube, select the “Credentials stored securely in the report server” option and enter in a user name and password that has access to the cube. Now check the “Use as Windows Credentials when connecting to the data source.” option.
Finally now you should be able to run your report.
And when you click on “New Subscription” you now subscribe to this report. Notice the userRole field that is populated with the PPS role
Now this was a solution that we came up with some time ago. Since then I have discovered the EffectiveUserName connection option in Analysis Services. While I have not had a chance to test this method, using EffectiveUserName might be a lot simpler as it allows a person with sufficient privilege to connect as another user and not role. This would remove the need for a separate system to maintain the association between User ID’s and Role. I will look into this and report back.
Regards
Mark
