Leverage SQL Server 2016 Row-Level Security for Power BI Desktop published reports/datasets
As SQL Server 2016 now supports Row-Level Security, we would like the ability to leverage this RLS when publishing a report/dataset from Power BI Desktop (using DirectQuery connection to the database) so that users only see the data that they have access to see per the RLS defined in the database.
One should expect this to be supported out of the box. Which organization could possibly want RLS to be administered on a report(ing layer) level??? All we need is the user's credentials being forwarded to the data source, as is the case when you connect to SQL with Excel...
SQL Server 2016 has row level security with the use of Security Profiles and Session Context. If PowerBI could set a session context variable after connecting with Direct Query then RLS would be properly applied in SQL Server and not PowerBI.
We could set the approriate session context variable as part of the Assess Token passed to the embedded report.
It is an appropriate ask as we are now forced to use SSAS Live connector for achieving this. This is also available in SSRS reporting.