5 thoughts on “Row-level Security Just Got Better: Passing Report Credentials to Analysis Services in SQL Server 2016

  1. Hi,

    The role DAX command won’t work if we use the Tabular model in both Excel and SSRS. I coded the following and still can’t seem to get it to work properly.

    =IF (
    CUSTOMDATA () = “”,
    GLPlant[GLPlantID]=LOOKUPVALUE(GLPlantSecurity[GLPlantID],GLPlantSecurity[Username],USERNAME(), GLPlantSecurity[GLPlantID],GLPlant[GLPlantID]),
    GLPlant[GLPlantID]=LOOKUPVALUE(GLPlantSecurity[GLPlantID],GLPlantSecurity[Username],CUSTOMDATA(), GLPlantSecurity[GLPlantID],GLPlant[GLPlantID])
    )

    Any suggestions appreciated.

    Regards,
    Ferris

    Like

  2. Thanks Paul,

    Your steps of using CUSTOMDATA is awesome. After implementing custom authentication and Row Level Security in 2016 cube I was really stuck to wire these two.

    Still we require to provide one windows account to connect to cube, Is there any possibility to avoid this?

    Thanks,
    Kannan Kandasamy

    Like

  3. Hi Paul,

    really interesting article, my colleague and I went through the pain of setting up Kerberos in the past so this is great news!

    We have followed through your example, but in order to get it to work we had to check “log in using these credentials, but then try to impersonate the user viewing the report” when setting up our data source to analysis services (which In your screen shot above is not ticked).

    In ticking this though, we then cannot setup a cache plan against that data source. Leaving it as “always run this dataset with the most recent data” doesn’t work either, the value doesn’t ever change even after reprocessing the cube. We understand why it doesn’t work as it doesn’t know the list of users that would connect to be able to store a cache plan for each, but we seem to be stuck with either not being able to retrieve an up to date value of a KPI or not being able to only return the part of the KPI that is relevant to the logged on user.

    We don’t know if the “always run this dataset with the most recent data” not working is a bug to be fixed or just a “feature”, but do you have any thoughts on how we may be able to achieve our goal?

    Many thanks in advance

    Lee.

    Like

  4. Pingback: Row-Level Security With Reporting Services – Curated SQL

  5. Pingback: Row-level Security Just Got Better: Passing Report Credentials to Analysis Services in SQL Server 2016 - SQL Server Blog - SQL Server - Toad World

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s