Reporting Services and Analysis Services Integration Passing UserID - by JohnMKelly

Status : 

 


24
0
Sign in
to vote
ID 509940 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 11/11/2009 1:53:28 PM
Duplicates 278651 Access Restriction Public

Description

Enable the Reporting Services built in field "User ID" to be passed to Analysis Services to be used to filter dimension data as part of a user role.  The RS User ID would be used as part of the "allowed member set" MDX to filter the results returned from the query so that only those records that that user should see are returned from the query - standard dimension level security scenario.

I know that this works if you are using active directory security and you can use the AD user name.  This is a problem for us because we have implemented an RS Security Extension so our User ID actually comes from our application database.

We have a multi-tenant application that we would like to deploy Report Builder to our clients to allow them to perform ad hoc reporting.  Problem is that one particular cube has multiple clients data in it and so we need to filter each query so that the client only sees their data.  To support this now, we have to setup a seperate shared data source to the cube for each client and pass the client organization id into the cube/dimension security through the "customproperties" attribute in the connection string.  

As SaaS and multi-tenant applications become more and more prevalent, this will become a bigger and bigger requirement.
Sign in to post a comment.
Posted by Microsoft on 2/26/2010 at 11:48 AM
Thank you for your further clarification. I verified with the Analysis Services team - there is no way to do this unless the UserId corresponds to a Windows users.

An alternate architecture would place a gateway device that transitions the authentication from Forms to Windows before the access is made to the report server. Then using Kerberos authentication and with delegation enabled, the end-to-end security aspect would be handled.

Hope that helps,
-Lukasz