Tableau Initial SQL Equivalent in PowerBI

[yoavcohen]

Hi,

When using DirectQuery, I'm looking for a way to send the name of the user that is currently logged in to PowerBI to the database before the queries to the data are sent.

In Tableau, there's a feature called Initial SQL that supports that functionality: https://help.tableau.com/current/pro/desktop/en-us/connect_basic_initialsql.htm#to-use-initial-sql.

Thank you,

Yoav.

[Icey]

Hi @yoavcohen ,

The operating logic in Power BI is different from that in Tableau. Your requirements can be achieved by Row Level Security.

Any user connecting to a report with Direct Query is using the credentials stored on the enterprise data gateway and therefore will have access to whatever the data gateway has access to. So if you have enabled Row Level Security on the tables for your database, this is not going to work with Direct Query.

However, you can configure Row Level Security to your Power BI Dataset. Then it will limit access to data as what you want.

Reference:

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs;

Restrict data access with row-level security (RLS) for Power BI Desktop - Power BI | Microsoft Docs;

What is an on-premises data gateway? | Microsoft Docs;

Manage your data source - SQL - Power BI | Microsoft Docs

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yoavcohen]

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.

[Icey · Accepted solution]

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: 

Configure Kerberos-based SSO from Power BI service to on-premises data sources - Power BI | Microsof....

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.