Row-Level Security in SQL Azure - Direct Query in Power BI

[adcolani]

Is it possible to set the Session_Context from Power BI with Direct Query mode so that we can use the row-level security feature in SQL Azure?

[v-qiuyu-msft]

Hi @adcolani,

In Power BI, we can connect to Azure SQL database in Direct Query mode. See: Use DirectQuery in Power BI Desktop.

Then in Power BI desktop, we can set a role to filter data, after publish to service, we can apply this role to users. For more information, see: Row-level security (RLS) with Power BI.

Best Regards,
Qiuyun Yu

[adcolani]

Thanks Qiuyun.

This seems we will be duplicating the row level security in both sql and power bi. Is there a way that the current user's username transparently flows from power bi to sql ( via Session_Context ) so the data is always secured no matter if we implement this at Power BI or not. Is this something in pipeline? 

Something like how it transparently works with AS https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-row-level-security-onprem...

https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security

[v-qiuyu-msft]

---

This seems we will be duplicating the row level security in both sql and power bi. Is there a way that the current user's username transparently flows from power bi to sql ( via Session_Context ) so the data is always secured no matter if we implement this at Power BI or not. Is this something in pipeline? 

---

Hi @adcolani,

Whether we connect to SQL Server database in Import mode or DirectQuery, the Power BI RLS is filtered based on retrieved data. It means, when we enter credential for SQL data source, assume this user is limited to retrieve 10 rows, if you set RLS role on Power BI side, it will filter on those 10 rows. SQL Server can't work as in the same as SSAS.

Best Regards,
Qiuyun Yu