Could not get data from web using Azure AD authentication in Excel

Bava, Alberto 0 Reputation points
2024-09-24T15:00:06.21+00:00

I have a flask web app, and an app registration on my azure account (the tenant is "common"). One endpoint of my web app returns data that can be read by excel. The authentication happen at code level, not in Azure, providing an MS graph access token. In the app response i configured the response header to look like this:

@app.after_request
def after(response: Response):
    response.headers["WWW-Authenticate"] = "Bearer authorization_uri=https://login.microsoftonline.com/common/oauth2/v2.0/authorize"
    return response

returning 401 when the Bearer token is empty, following these instructions: https://learn.microsoft.com/en-us/power-query/connector-authentication

but when I try to sign in from excel I get this error:

User's image

in the same link i just posted it says something about application ID URI:

For example, if accessing https://api.myservice.com/path/to/data/api, Power Query would expect your Application ID URL value to be equal to https://api.myservice.com.

but i don't know how to achieve what it says. I showed this example with my personal app (localhost in azure tenant=common) but i have the same problem with my organization tenant ID.

I also tried with this WWW-Authenticate:

Bearer authorization_uri={AZURE_CONFIG.authority}/oauth2/v2.0/authorize client_id=00000003-0000-0ff1-ce00-000000000000 scope=https://graph.microsoft.com/.default resource_id=00000003-0000-0ff1-ce00-000000000000"

but still getting the same problem.
Here it says it could be due to excel hardcoding the resource_id: https://community.fabric.microsoft.com/t5/Desktop/AADSTS50001-Specify-resource-id-in-www-authenticate-response/m-p/323827

Is this the problem? any idea on how i can fix it?

Many thanks

Regards

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,904 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
21,788 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.