Forum Discussion
Tableau Initial SQL Equivalent in PowerBI
Hi yoavcohen ,
Sorry to reply late.
When using DirectQuery, by default, all users who consume a published report connect to the back-end data source using the credentials entered after publication to the Power BI service. This process is the same for data that's imported: all users see the same data, regardless of any security rules defined in the backend source.
If you want per-user security implemented with DirectQuery sources, you should either use RLS or configure Kerberos-constrained authentication against the source. Kerberos isn't available for all sources.
In your scenario, using Kerberos constrained delegation to enable seamless SSO connectivity may meet your requirements. Enabling SSO makes it easy for Power BI reports and dashboards to refresh data from on-premises sources while respecting user-level permissions configured on those sources. For the detailed operations, please refer to this document:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for answering my question Icey. I'd like to better clarify our use-case:
- When each user uses a unique set of credentials - we are fine.
- When all users share a single set of credentials - this is very common in BI tools, the report creator sets the credentials on the datasource and then everyone uses the same set of credentials. For this case, we want to have some form of auditing that identifies the real user that consumes the data from the database.
Some examples I've seen in other tools:
- Tableau Initial SQL - we can run a SQL statement with the username after the connection is established and before any query to data is executed.
- Looker - sends the user ID as a SQL comment on each query
- Custom JDBC connection parameters - I can add a custom parameter in an application that specifies the user ID.
Does PowerBI have any mechanism in which the username that is currently consuming data can be sent ot the database?
Thank you,
Yoav.
Community SupportHi yoavcohen ,
Sorry to reply late.
When using DirectQuery, by default, all users who consume a published report connect to the back-end data source using the credentials entered after publication to the Power BI service. This process is the same for data that's imported: all users see the same data, regardless of any security rules defined in the backend source.
If you want per-user security implemented with DirectQuery sources, you should either use RLS or configure Kerberos-constrained authentication against the source. Kerberos isn't available for all sources.
In your scenario, using Kerberos constrained delegation to enable seamless SSO connectivity may meet your requirements. Enabling SSO makes it easy for Power BI reports and dashboards to refresh data from on-premises sources while respecting user-level permissions configured on those sources. For the detailed operations, please refer to this document:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.