Forum Discussion
Which credentials are used when using direct query
We are using a data warehouse type data source, which has user-specific security. This means that Person A and Person B may see different tables when viewing this warehouse based on their individual access rights.
For security reasons, when developing reports, we elect to connect to the warehouse using Direct Query. The belief is that doing so will ensure that the warehouse security restricts the users to seeing what they are meant to see only.
However, when we set up a report and publish it to the service, we make use of a data connection (set up under "Manage Connections and Gateways") and this uses OAUTH to authenticate. As I understand it, if I...
- create a report based on a table that Person A can see, but Person B cannot
- publish this report to a workspace in the service where both persons have viewer rights
- set the semantic model up to use a data connection that I have authenticated using OAUTH
...when these 2 persons attempt to view the report, they will BOTH be able to see the data. That's because the direct query will NOT use the viewers details, but instead, will use my credentials (since I authenticated the connection) and therefore Person A and Person B will both see the data (even though Person B is not meant to).
At least, this is what I am seeing when doing some testing, and I am hoping someone can confirm that my theory is correct.
Is there any way we could enforce the marketplace's secruity to be applied based on who is opening the report?
With thanks
6 Replies
In the Power BI Service, a DirectQuery semantic model will usually send queries to the source using the stored credentials configured under Manage connections and gateways / Data source credentials. That means every viewer can effectively query “as the credential owner”, unless you explicitly set up SSO / identity delegation.
Yes your understanding is correct.
There are two options you must consider
Option 1:
Enforce Warehouse Level Security, like User A can see the table / User B should not be allowed to see the table
This option is only applicable if Power BI can get the viewers identity level secrets from Warehouse.
Option 2:
Enforce RLS or OLS at Power BI that is the best approach to go for.
Hi FabScone75
If you can, let us know which data source you are connecting to so that we can validate. If you can use Single Sign On, which will then pass the user's credentials through to your Directquery data source.
Hello GilbertQ , the source is Starburst. Thank you.
Hi FabScone75 ,
Thanks for confirming the source as Starburst.
As already explained by cengizhanarslan , GilbertQ and Murtaza_Ghafoor , DirectQuery in the Power BI Service uses the stored connection credentials unless Single Sign On is supported and configured.
After validating against the official Power BI SSO documentation, Starburst is not currently listed as a supported data source for SSO in Power BI Service:
Overview of single sign-on for on-premises data gateways - Power BI | Microsoft LearnThere is also an existing Power BI Ideas request requesting Azure AD based SSO support for the Starburst connector, which confirms that this capability is not available today.
SSO for Starburst connector in Power BI Service (A... - Microsoft Fabric Community
As a result:
->Power BI cannot delegate the report viewer identity to Starburst
->DirectQuery executes using the configured connection credentials
->Starburst user specific security is not applied per report viewer
Given the current limitations, enforcing security using RLS or OLS within Power BI is the supported approach.
If this functionality is important for your scenario, you may want to vote for the existing Power BI Ideas request to help prioritize support for Starburst SSO in the future.
Thank you.
