User Identity/Principal Authentication for Sql Analytics endpoint

[koltonist]

Does anyone know how to authenticate to Lakehouse's SQL Analytics Endpoint, I have an On-behalf-Of flow, where the user signs in desktop app -> web api -> fabric endpoint. On the desktop application part I am issuing user principal authentication token, then authenticating to web api, then web api needs to authenticate to sql endpoint with that same user permissions. Security is set up on Sql Analytics endpoint part.
Web API is written in Python. Possible places I could've misunderstood are: are there any specific scopes I need to request to be able to query Fabric's sql endpoint?

client_app = ConfidentialClientApplication(client_id="x?",authority="y",client_credential="z")

token = client_app.acquire_token_on_behalf_of(token_issued_by_desktop_app,[scopes?])

return token["access_token"]

self.connection = pyodbc.connect(self.connection_string, attrs_before={1256: self.parm_access_token()})

Currently what I am seing is - Failed to connect to db1: ('28000', '[28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Could not login because the authentication failed. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Could not login because the authentication failed. (18456)')

[Anonymous · Accepted solution]

Hi @koltonist 
Thank you for reaching out microsoft community forum.

To address the query about authenticating to Lakehouse's SQL Analytics Endpoint using an On-behalf-Of flow in a Python Web API, here are some key points and steps to consider:

• Ensure that you are requesting the correct scopes for accessing the SQL Analytics Endpoint. Typically, you need to request scopes related to Microsoft Fabric and SQL.
  For example:scopes = ["https://database.windows.net/.default"]
•  Your client application setup looks correct. Ensure that the client_id, authority, and client_credential are correctly configured.
• When acquiring the token on behalf of the user, make sure the token issued by the desktop app is valid and has the necessary permissions.

 The error Could not login because the authentication failed. (18456) indicates an authentication issue. Ensure that:

• The access token is valid and not expired.The token has the necessary permissions to access the SQL Analytics Endpoint.The SQL Analytics Endpoint is correctly configured to accept the token.
• Verify that the SQL Analytics Endpoint is properly set up and that the security settings allow access using the provided token.

I have attaached MS docements for reference:

Microsoft Fabric SQL Analytics Endpoint Documentation
Microsoft Entra Authentication for SQL Analytics Endpoint

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community.

Thank you.