Data Display in Reports based on windows Authentication ID

Hi Experts,

I have a reporting scenario, where the reports are fetched from Analysis Services.

The reports should display data only spcecific to that user.

All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.

Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated

Thanks,

Hi,

Easier said than done. I guess that the easier path would be to use data filtering based on parameters values limited by userid.

It depends, if you access cubes directly as well, i.e. from Excel, then that would be different. You would have to create custom MDX dimension access formulas based on roles and map these roles to Windows groups.

Assuming you are only going after AS data from RS, then you would still need to create an access policy.

i.e. you can segregate access to data slices based on membership to Windows groups.

We have found that using cumulative membership to groups was the most flexible and did not require too many groups.

You have to find a way to load LDAP (or Windows AD groups and membership) data, then each time an user access the report, you use the userid info to check what is the acceptable list of values for your parameters.

This way, a given user will be able to see and select only parameters values acceptable for her job function.

That is quite a lot of code, functions and procedures to write, no quick fix.

First thing first, you need the AD or LDAP data, there are quite a few ways to get it. I remember that SQL Server magazine ran an article a few month ago about getting it with a SSIS package.

You can also settle for another easier way, like calling Exporter Pro from a Windows scheduled task. Exporter Pro is part of the Hyena software.

Your question is not the type of question where a quick and precise answer is available. I can only give some high level clues. The rest would take time and require insider knowledge of your situation and requirements.

Hope it helps somehow

Philippe