topic Re: Direct Query and Row Level Security - Do Viewers need to have read access to SQL Server database? in Report Server

Direct Query and Row Level Security - Do Viewers need to have read access to SQL Server database?

Anonymous — Mon, 21 Mar 2022 02:58:15 GMT

Hello all,

I have a question regarding the viewer authentication in PowerBI report using DirectQuery and having Row Level Security applied when it's published in a PowerBI Workspace/PowerBI Service.

I am trying to create a PowerBI report using Direct Query storage mode, with RLS applied using USERPRINCIPALNAME() in a dimension table to filter the data in fact tables related to viewers, and there is an enterprise power bi data gateway with a service account authenticated to SQL Server database.

So in this case, I wonder whether all viewers are required to have read access to the SQL Server database, so they can see the report with filtered underlying data on PowerBI workspace or not. Or the SQL Server database will use the authenticated service account from the Enterprise Data gateway to run the queries and return the filtered data of the viewer?

Because the data in SQL Server database is restricted to give read permission to many users, I wonder if there are any solutions that allow viewers (who don't have the permission to SQL Server database but have the view permission to PowerBI Workspace) to view the PowerBI report with filtered underlying data.

Many thanks.

Re: Direct Query and Row Level Security - Do Viewers need to have read access to SQL Server database?

d_gosbell — Mon, 21 Mar 2022 21:31:05 GMT

It depends on how you have configured the credentials for the connection in your tabular model. If the connection is using the fixed account or impersonate service account options the that account will be used to execute the SQL queries. Your RLS restrictions will work fine with either of these options as the RLS filters are effectively translated into extra conditions on the WHERE clause for the SQL queries that are generated

Re: Direct Query and Row Level Security - Do Viewers need to have read access to SQL Server database?

Anonymous — Mon, 21 Mar 2022 22:28:03 GMT

Thanks for your confirmation.