Forum Discussion

mrohde's avatar
mrohde
Regular Visitor
9 years ago
Solved

Does the data gateway allow security inheritance?

We have row level security enabled on a number of tables that limits records returned based on the user ID that works in conjunction with reporting services.  If we use the data gateway will it imper...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi mrohde,

     

    As you are connecting to SQL Server database, Power BI service uses the credential configured under on-premise data gateway to access the SQL Server database. It means all users access the same report will see the same data, as it uses the same credential to access database. If you want to limit users to see their own data, you need to apply RLS mentioned in my original post.

     

    Best Regards,
    Qiuyun Yu

     

     

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi mrohde,

 

In Power BI, data gateway acts as a bridge between the Power BI Service and on-premises data sources. The row level security is not defined in data gateway, it need to be set on dataset. data gateway settings will not affect row level security permission.

 

In your scenario, to set users access the report can see his/her own data, you can use username() function when you set role in Power BI desktop. As within the Power BI service, username() will return the user's User Principal Name (UPN). This looks similar to an email address. There need to have a column field contains the UPN like <user>@<domain>.com, create a role like below:

 

 

 

After you publish the report to Power BI Service, select the dataset then click Security, add users under this role.

 

 

Reference:

Row-level security (RLS) with Power BI

 

Best Regards,
Qiuyun Yu

mrohde's avatar
mrohde
Regular Visitor
9 years ago

To clarify,

 

In the on-premise SQL 2016 server we actaully have the secrity enable. This security is working fine when we use reporting services on-premise.

 

What I was trying to understand is if I allow users access to the on-premise data using Power BI will the same restrictions apply in Power BI that already exist in the SQL 2016 server.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi mrohde,

     

    As you are connecting to SQL Server database, Power BI service uses the credential configured under on-premise data gateway to access the SQL Server database. It means all users access the same report will see the same data, as it uses the same credential to access database. If you want to limit users to see their own data, you need to apply RLS mentioned in my original post.

     

    Best Regards,
    Qiuyun Yu