first up, an apology for cross-posting into the
SP BI forums, but I'm not sure where the answer will be found so I'm casting my net wide.
In essense, I have a SharePoint 2007 based Extranet that uses forms based authentication (using the AspNetSqlMembershipProvider provider) and I want some of these users to have access to some SSRS 2008R2SP1 (integrated mode)
The problem is that because I'm not using Windows authentication for the users on the Extranet, I have to use a trusted account for SSRS to access the datasource. And while I can set a specific account up in AD for this, it means that each different Extranet
user can see the same data, which I don't want. Each user has specific data that is available to them, so when they view the same report, they get differing results.
The documentation talks about SP/SSRS using a SharePoint User Token (in the "header"?), but there's no metion of how to leverage this in the report or it's impact on data coming from the datasource.
A potential solution is to parameterise every report and somehow push the current user into a parameter and then deal with it in the report, but even this isn't ideal.
Turns out that at the report level you can lookup User!UserID which will return the SharePoint User, even when connecting using a Trusted Account.
So I'm getting "aspnetsqlmembershipprovider:email@example.com", which we're going to leverage in the reports, even if the cube itself doesn't understand/see this user. So essentially we're doing our own filtering over the top of the cube.