Acheiving row level security with DirectQuery option

[dilkushpatel]

Hi,

So we have direct query to SQL Server and SSAS,

Is it possible to acheive row level security with SQL Server?
With tabular analysis server we can achieve this using live connection option I think, now I have another problem with this.

My power bi user name is dilkush.patel@xyz.com and my windows login is abc\dpatel

with this configuration is it possible to acheive row level security?

Thanks,

Dilkush

[ashishrj]

@dilkushpatel We have given a webinar on implementing row level security as provided here !

[dilkushpatel]

Thanks @ashishrj

[Seth_C_Bauer]

@dilkushpatel RLS is only available for use with a SSAS connection atm. For your other question, I'm not 100% on termanolgy here, but essentially you need to be using Azure AD and have synced your user accounts to be able to pass your creds. Second, once you set up the Analysis Services Connector or Enterprise Gateway you should have access to see your models. (Provided you have the appropriate permissions on the back end. 

[greggyb]

Direct Query = DAX -> SQL conversion layer to connect to SQL-based sources.

Live connection / explore = live queries against SSAS.

Only the latter passes EffectiveUserName to the source DB where it can be used to determine security.

As @Seth_C_Bauer said, the only data source in PBI that allows role-based security is SSAS.

[dilkushpatel]

Thanks @Seth_C_Bauer and @greggyb

With SQl 2016 i think row level security is possible so If I use windows authentication and my users are synced with active directory will row level security work?

just a curiosity.

Thanks,

Dilkush

[greggyb · Accepted solution]

@dilkushpatel, no. The only sources that can be utilized with RLS in PBI are SSAS Multidimensional and SSAS Tabular. This may change in the future, but for now this is the case.

SQL 2016 supports RLS, but the Enterprise Gateway, which PBI uses for Direct Query against SQL sources will only use stored credentials for a SQL data source. This may change in the future, but for now this is the case.