Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 impersonate the logged in use or inherity the authority of the service account?
Obvioulsy since we have go to the trouble of securing the table if the gateway doesn't adhere the the security it we will need to put effort into recreate it for reports that are published with Power BI.
Solved! Go to Solution.
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
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
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.
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