Post reply

Dimension Security in SSAS & SSRS

  • February 19, 2009 at 11:35 am

    #201322

    Hi!!

    I am stuck with a problem of implementing security at dimension level in SSAS. Here is what I did - 1. Defined a role in SSAS and applied security at dimension level (Unchecking cube dimensions that I don't want this role to access and setting Allowed & denied Sets). 2. Tested using Cube Browser, it worked fine. 3. Tested using SSRS, no change, I was still able to query the dimensions & get results that I don't want.

    Question - Is it possible to propagate the security I define at Cube level to SSRS (using report models say)? I would like to believe yes it is.

    If yes then here is what I need -

    Users will logon to the Report Manager using Windows Identity (Integrated Authentication on IIS turned on -done)

    Capture this identity to find out SSAS role that they belong to - I guess this would be through a query, does not seem to work automatically (How to do this?)

    User works within the restrictions of this role in SSRS (role based security applied at SSAS level) i.e. if dimension X is not available to user, he/she should not be able to query it. (How to do this?)

    I have referred quite a few blogs on this and even found one - http://www.sqlmag.com/Article/ArticleID/96763/sql_server_96763.html

    but this one seems to have more information on how to set it up within SSAS, rather than how to use this in SSRS.

    Anyone who has worked on this approach OR have an understanding of this please let me know.

    Thanks in advance!!

    Cheers!!

    Vineet

  • February 19, 2009 at 3:12 pm

    #947191

    Assuming you are in a distributed environment and you are utilizing Kerberos to pass the user credentials, this should work just fine for you. To verify the credential information being used with SSRS during your testing you should run Profiler against SSAS and see what is being passed. You should also verify that the user is only part of the restricted SSAS Role defined in the database.

    For additional testing you could actually modifiy the connection string to include the Roles information - Analysis Services 2000 and 2005 Connection string properties.

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply