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

Bava, Alberto 0

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

Navya 12,175 Reputation points Microsoft Vendor

2024-10-04T06:17:48.3366667+00:00

Hi @Bava, Alberto

Thank you for posting this in Microsoft Q&A.

You get this error message when the requested resource (resembled by a service principal) does not exist in the tenant. Maybe the application, website, or service you’re logging in to is trying to log in to the wrong Azure AD instance, or it’s logging in to the right Azure AD, but it’s a multi-tenant application that hasn’t been consented to by the administrator at the said tenant.

Please follow the below steps

1.Verify that it’s your tenant (or the one you’re using for this scenario anyway). If the tenant ids don’t match, the application is either misconfigured, in which case you need to change the configuration of your authentication.

2.Verify you’re missing your required scopes in application. Go to permissions and click Grant admin consent.

3.If you’re trying to log in from an application that doesn’t support user consent flow or you’re unable to use it.

Your consent URL will look something like this...

https://login.microsoftonline.com/{your-tenant-id}/oauth2/v2.0/authorize?response_type=code&client_id={resource-id}&prompt=admin_consent&scope=openid

Ensure you provide the correct {your-tenant-id} and {resource-id}

Hope this helps. Do let us know if you any further queries.

Thanks,

Navya.
Navya 12,175 Reputation points Microsoft Vendor

2024-10-07T11:03:01.0333333+00:00

Hi @Bava, Alberto

Just checking in to see if above information was helpful. If you have any further updates on this issue, please feel free to post back.
Bava, Alberto 0 Reputation points

2024-10-09T08:30:40.84+00:00
Hi Navya,

The tenant ID matches in fact when I click on sign in the logo of my company shows up. The application is not a multitenant application.

I'm not fully sure what you mean but the the app has SSO enabled in code with msal library and redirect flow and the url is: https://login.microsoftonline.com/fbc7ad4e-59c4-494d-aad7-214fb4e80573/oauth2/v2.0/authorize?client_id=e415e76a-2764-4228-b760-bbe0607c6b83&response_type=code&redirect_uri=http%3A%2F%2Flocalhost%3A5000%2FgetAToken%2F&scope=User.ReadBasic.All+offline_access+openid+profile&state=HDfIQkyPgGVampcB&code_challenge=sy1wY7k1VJTOMfVeQn1gdAIQK5XLMb2Q5K6m6gt2X4I&code_challenge_method=S256&nonce=f06bc240e22feb0feadf91a12b846b66d04e47d0a62a440c8e042a90389e4a58&client_info=1 so I think it has all the scope necessaries and granted.

I don't know what you mean. I'm trying to log in from Microsoft Excel.

My www-authenticate in Python looks like this:

raise Unauthorized( "Bearer Token not provided or user not logged in.", www_authenticate=WWWAuthenticate( auth_type="Bearer", values={ "authorization_uri": f"{AZURE_CONFIG.authority}/oauth2/v2.0/authorize", "client_id": "e415e76a-2764-4228-b760-bbe0607c6b83", "scope": "openid", "response_type": "code", "prompt": "admin_consent", }, ), )

and in the browser looks like this:

I still think that the issue (as explained here: https://learn.microsoft.com/en-us/power-query/connector-authentication) is this:

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 don't know where to set the Application ID URL which in this case is just the localhost:5000
Bava, Alberto 0 Reputation points

2024-10-09T08:46:29.0333333+00:00

deleted comment

Share via

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

Your answer