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

Question

We have developed a web application using Azure AD authentication. It works perfectly in browser and provides REST APIs that return JSON data.

In Excel (Version 2401 Build 16.0.17231.20236), under the Tab "Data", click "From Web" and input a web URL (which is working in browser) and click OK:

Then there is a pop up window "Access Web content", where a few options are available for signing into the web application. I choose "Organizational account" and click the "Sign in" button, I get the message "You're currently signed in":

Then I click the "connect" button, I get an error message "We couldn't authenticate with the credentials provided. Please try again":

I have already added power query for excel (a672d62c-fc7b-4e81-a576-e60dc46e951d) as authorized client applications in App Registration in Azure:

I checked the log file in the server and found the following error messages:

Graph service exception Error code: InvalidAuthenticationToken

Error message: Access token validation failure. Invalid audience.

From the documentation here (https://learn.microsoft.com/en-us/power-query/connector-authentication), it reads "When you select Sign-in in Step 2 above, Power Query sends a request to the provided URL endpoint with an authorization header with an empty bearer token. The service is then expected to respond with a 401 response with a WWW-Authenticate header indicating the Microsoft Entra ID authorization URI to use. This response should include the tenant to sign into, or /common/ if the resource isn’t associated with a specific tenant. Power Query can then initiate the OAuth flow against the authorization_uri. Power Query requests a Microsoft Entra ID Resource or Audience value equal to the domain of the URL being requested. This value would be the value you use for your Azure Application ID URL value in your API/service registration."

As you can see here, the sign in step was completed successfully. The next step, where power query initiate the OAuth flow against the authorization_uri, should be executed automatically and implicitly, if I understand correctly. But I got the above error. It seams that power query for excel got an access token with a wrong audience value from the authorization_uri (https://login.microsoftonline.com/{tenant_id}/oauth2/authorize).

I have tried https://login.microsoftonline.com/{tenant_id}/oauth2/authorize?client_id={client_id}&response_type=code&redirect_uri={redirect_uri}&response_mode=query&resource={resource_id}&state={state} and still got the same error.

How to get the right audience then? Any help would be greatly appreciated. Thanks in advance.

Answer 1

The issue was caused by using MS Graph to verify the access token getting from the authorization_uri. It is solved now. Thanks anyway.

Answer 2

Hi @Shiping Yang

Thanks for reaching out.

Did you tried to decode the access token using jwt.ms? What are the values you are getting there in scope and audience?

It seems scopes you are passing is not correct. It should be Application ID URI along with scope name. As per screenshot you have passed localhost URL in place of Application ID URI which is not able to identify your API.

Reference - https://learn.microsoft.com/en-us/entra/identity-platform/quickstart-configure-app-expose-web-apis#add-a-scope-requiring-admin-consent

Thanks,

Shweta