Data Source Security Without RLS

[Anonymous]

Hi, does anyone knows how to filter rows within a table based on their userprinciplename and organisation hierarchy without using RLS? There are so many roles need to be created in my situation and also I have heard they could not pass userprinciplename through a Power BI gateaway. Really need your help. 

In my case, I am trying to implement data flows and in the future there will be incremental refresh. User can only see data based on the rules or filter that have been set for each Power BI credentials

[v-zhenbw-msft]

Hi @Anonymous ,

Please refer this article.

https://www.red-gate.com/simple-talk/sysadmin/data-protection-and-privacy/introduction-to-sql-server-security-part-1/

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @Anonymous ,

If you connect to a SQL Server using Direct Query, and if you don’t want to configure the RLS, you can configure the security roles in your data source.

When a user opens a report Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials.

Or you can configure Kerberos-constrained authentication against the source.

But Kerberos isn't available for all sources.

Please refer this document.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sso-kerberos

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

How can I set a security rules to the data source based on user's credentials? Is it using Native Based Query?

Another question, how can I set the power bi report so that when user open the report, the report will automatically updated based on the security applied to the user credentials?

[amitchandak]

@Anonymous , Using userprincipalname means using RLS ?

Employee hierarchy -https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[Anonymous]

I don't want to use RLS having said that creating the roles and mapping users to a role is massive work. I want to implement the RLS principle by passing a Power BI user credentials as a parameter in SQL statement. Do you know how?

I know that we can use PATH() for hierarchy, however we will be using Direct Query and I have checked that we couldn't use Paren-Child Dax as this does not support for Direct Query.