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

Shiping Yang 45 Reputation points
2024-03-08T15:26:46.3633333+00:00

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:

Screenshot_from_web

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":

User's image

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

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

User's image

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.

User's image

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.

Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,395 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Shiping Yang 45 Reputation points
    2024-04-02T15:17:08.2833333+00:00

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

    2 people found this answer helpful.

  2. Shweta Mathur 27,216 Reputation points Microsoft Employee
    2024-04-02T04:39:19.09+00:00

    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

    0 comments No comments