How to retrieve the data when RLS is implemented at back end

divyang_vyas — Tue, 23 Apr 2019 08:38:21 GMT

We have a multi-tanent Azure web app (Asp.net MVC 5) connected to Azure SQL database, used by around 400 users and growing. I want to implement Power BI for data visualization. We've implemented RLS using SESSION_CONTEXT in SQL. I've gone through the article https://docs.microsoft.com/en-us/power-bi/developer/embed-multi-tenancy and learnt that I can use either "Power BI workspace-based isolation" or "Row-level security-based isolation". It appears to me that RLS based isolation would be easier to manage and efficient (any expert's advice is always welcome). In order to retrieve the data, it is required to set a session context before running a query, as we have implemented RLS at database side. What should be the correct and efficient approach to retrieve the data in this scenario?

Re: How to retrieve the data when RLS is implemented at back end

divyang_vyas — Tue, 07 May 2019 12:53:48 GMT

I finally have a solution and posting it here for reference:

For "Power BI workspace-based isolation" I can use parameterized report and create a template

Help source: Jorge Segarra https://www.facebook.com/groups/powerbi/

For "Row-level security-based isolation" I can store a list of all tenant ids separately and then use the list as parameter

Help Source: https://www.youtube.com/watch?v=iiNDq2VrZPY

topic How to retrieve the data when RLS is implemented at back end in Developer

How to retrieve the data when RLS is implemented at back end

Re: How to retrieve the data when RLS is implemented at back end