Row level security SQL and Power BI

In my previous post I explained how to enable row level security on your SQL database. However, if you use Power BI as a live connection to your database, it will filter according to the user that is used in the data gateway. This effectively means that you cannot rely on sql row level security if you use Power BI on top of it. With SSRS it is not a problem, as you can impersonate the original user rendering the report, even if you use stored credentials for your dataset.

This also applies to Azure SQL: live connection with Azure Active Directory authentication appears to be not possible yet (thanks to my colleague Jerrold Stolk for trying that out 🙂 ).

What does this mean?

It means you may still want to implement your security in either Power BI or in SSAS. I would recommend SSAS Tabular model because end users cannot alter the row level security. If you define it in Power BI itself, you would have to prevent users from directly downloading a pbix file and altering the security. Personally I think it is much wiser to define the row level security on the source/backend and not in the reporting tool.

And at this moment, the only way to do this nicely is SSAS.

 

BI consultant and trainer at Motion10

Leave a Reply

Next ArticleData Warehouse Automation Thoughts