How can we access table data using a SQL Endpoint with a user's token?

[pavanramineni]

I am trying to access table data through the "SQL Endpoint" using a user's token. I initially used the Service Principal Details to generate an access token with the scope https://database.windows.net/.default. However, I encountered a limitation where the service principal needs to be added as a member of each workspace that the user wants to connect to.

I then attempted to use "GetAccessTokenOnBehalfOf" with the following scopes, which I verified with the token, but encountered an error: 'Login failed due to invalid authentication methods.'.

The scopes I used were:

Lakehouse.ReadWrite.All
OneLake.ReadWrite.All
SQLEndpoint.Execute.All
SQLEndpoint.ReadWrite.All
SQLEndpoint.Reshare.All
StorageAccount.ReadWrite.All
Warehouse.ReadWrite.All
Workspace.Read.All
Workspace.ReadWrite.All

Despite these efforts, the issue persists. Does anyone have insights on how to resolve this or alternative approaches to access table data using a user's token?

[michal-mano]

Hello,
Firstly, you may find some useful information in this doc: Working with customer data
The approach here described here can be summarized as follows:

1. Ensure you have Azure SQL Database provisioned. If not, follow the steps outlined here with the application ID for Azure SQL Database, 022907d3-0f1b-48f7-badc-1ba6abab6d66.
2. Add the delegated scope "Azure SQL Database -> user_impersonation" to your web app. Here is an example of the steps for doing this (for Azure Storage). You may need to give consent again as the user so that this scope is active, or "Grant admin consent" from the API permissions tab.
3. Frontend: Acquire an access token using the workloadClient.auth.acquireAccessToken method. Send it to your backend.
4. Backend: Call getWarehouse API to get the database name (you may already have this information by other means)
5. Backend: GetAccessTokenOnBehalfOf with the scope https://database.windows.net//user_impersonation. Yes, two forward slashes. You will be able to acquire an access token with one, but the access token won't be valid for the next step.
6. Backend: use the token obtained in the previous step to open a SQL connection using the Microsoft.Data.SqlClient library.
7. Use the open SQL connection to query the database and access whatever data the user has access to.

[jainr]

Why do they need to provision Azure SQL Database, they are trying to use the SQL Endpoint of the Lakehouse.

[michal-mano]

I might not be fully understanding the question, but the SQL Endpoint of a Lakehouse is functionally the same as a Warehouse.
Some more information:
https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity
If you look at the SQL Connection String of a SQL Analytics Endpoint, you'll see the string "datawarehouse" within it.

[jainr]

I understand the endpoint is same in both the cases, the question is why does the user need to provision a separate resource in Azure if all they want is to use the SQL Endpoint of Lakehouse, he is just wondering what scopes to request so that the token he receives allows him to use LH's SQL endpoint using OBO flow. They tried bunch of scopes but apparently there are more.

[SanchanaMohankumar]

Hi Team,
Upon adding "Azure SQL Database -> user_impersonation" scope to our web app we are able access the SQL Endpoint of Lakehouse.

[jainr]

Thanks Sanchana, I was hoping that adding scope alone should be enough, thanks for confirming. @michal-mano thanks for your response.