Execute query on sql server as current user

[Indra_n]

Hi,

I have configured a PowerBI report using Gateway and data source connection to SQL Server (the SQL server in Azure VM) with my Windows service credentials . I want to render customized data based on the logged-in user privileges. Is there a way to configure PowerBI to connect directly to a SQL server based on the logged-in user information? Basically, I am trying to see from PowerBI if I can directly query as the logged-in user (passing the user identity to the SQL server) without using the service account configured in the gateway. Note: PowerBI communicates with the SQL server through a view, and the view currently does not provide us with the username (could not attempt roles + row-level security). Please kindly share your thoughts.

Thanks!

[marcelsmaglhaes]

Hey @Indra_n ,

Unfortunatelly, when using Power BI with a direct connection to a SQL Server, you can't directly pass the logged-in user's identity to the SQL Server unless (and maybe) you are using the "Effective Username" feature in combination with Row-Level Security (RLS).

Please note that setting up Effective Username and RLS may require changes to your SQL Server and may depend on your specific setup. It's a powerful feature for security but can be complex to configure based on your use case.

If you are unable to access the username in the view directly, you may need to consider modifying your SQL Server setup to capture or map user identities. Additionally, working with a local service account configured in the gateway for secure communication is a common practice, as it isolates the data source from direct user access.