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

  1. Hi All,

    I have developed an SSRS report from SSAS cube which already have roles and members defined and then deployed on POWER BI server. After deployment i have added customdata property to the data source. But i am not able to see particular clients data. Is there anything that i need to change in SSRS report. Please suggest.

    Thank You.

  2. Hi Paul,

    I am actually trying to do this exact thing in SSRS and I’m not sure, based on your article, how to implement this. I see that I can add the CustomData string to the data source connection string, but how would i then use that to connect to my bridge table that contains the UserIDs? Can you elaborate?



    1. Hi Jeff,

      Did you get your answer here? Actually i am not getting how to implement this from this article. I just understood to define the custom data property in the data source but how to pass the user ID from SSRS report or in SSRs report i am not getting.

      Please let me know if you get any information regarding this. You can share if any helpful link you have.

      Thank You.

      1. Hi Aashu,

        I was able to work around this by storing user credentials in a SQL table, and joining my source queries for the parameter selections to that table. I then just added custom code to the report to extract the username of the user running the report and passed it to the data sets as a parameter value, which filtered the parameter selection.

  3. 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.


  4. 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?

    Kannan Kandasamy

  5. 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


Leave a Reply

%d bloggers like this: