Power Query/PowerBI connecting to Custom oDATA feed secured with AAD

Question

I have created a custom OData feed using ASP.NET Web API. This service is configured using Azure Active Directory for Authentication. The issue I currently have is when I try and connect Power BI or Power Query up to the OData feed. Once I have entered my credentials I get the following error:

Invalid_resource: AADSTS50001: The application named https://localhost:44320 was not found in the tenant named XXX.onmicrosoft.com. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You might have sent your authentication request to the wrong tenant.

I'm pretty sure that I have configured AAD correctly as I can connect up through the web browser with no issues. I'm not sure if it's even possible to connect up with Power Query as I have seen conflicting posts in various forums!

Any help would be greatly appreciated.

Answer 1

I also experienced this error when trying to use PowerQuery to get API data into an Excel spreadsheet, and had a hard time getting past it mainly as the documentation around all this is somewhat slim.

Problem - How to import data from an API in Azure into an Excel spreadsheet?

I had a ASP.NET API running in Azure and exposed on my own domain at a URL e.g. https://api.myapp.net (rather than a built-in azure URL). This API was hooking into Azure AD via the OWIN middleware: UseWindowsAzureActiveDirectoryBearerAuthentication, i.e. the App Service in Azure had AAD authentication turned OFF. The application was registered in AAD as multi-tenant, in a different tenant to the where the app service resource was hosted.

Issue 1 - Credentials

So first off in Excel do Data > From Web > https://api.myapp.net/Products and select "Organisational Account" and click "Sign-in", giving this error:

There are two different fixes for this:

1. Return WWW-Authenticate response header on all 401s

If you have enabled AAD on your API in code using the owin middleware, then you need to ensure the service returns the correct ​WWW-Authenticate header in the 401 response to the client, specifically we must specify the AAD sign-in end-point as the authorization uri, e.g.:

WWW-Authenticate: Bearer realm="", 
authorization_uri="https://login.microsoftonline.com/<<tenant id of your users>>"

See: this TechNet question which suggested this solution

2. Turn on AAD authentication for the API app service in the Azure portal

• Alternatively in the Azure Portal for the tenant where the App Services themselves are hosted
• Go to App Services and locate the API app service
• In Authentication / Authorization turn App Service Authentication ON
• For "Action to take when unauthenticated" select "Log in with Azure Active Directory"
• Under "Authentication Providers" click "Azure Active Directory" and select "Advanced" settings
• Under "Client id" enter the application id for the API app registration
• Under "Issuer Url" enter the sign-in end-point for the tenant the users of the API originate from
• Under "Allowed Token Audiences" ensure you have added the actual url of your API e.g. https://api.myapp.net
• Save the changes

Essentially this config is described here

Issue 2 - App Registration

Now back in Excel when you click sign-in on the query a pop-up will open and take you to the Microsoft sign-in page for the tenant you configured. When you enter credentials and sign-in you may then get this error (the one in the question):

To fix this issue you need to ensure the the application is registered correctly with AAD.

Here is how...

• In the Azure Portal for the tenant where your applications are registered
• Go to Azure Active Directory > App Registrations and locate the registration for the API service
• Edit the Manifest and ensure the actual deployed API URL is configured in the list of identifierUris e.g. ​https://api.myapp.net (there will be an Azure built-in URL already configured)

{
  "identifierUris": [
    "https://api.myapp.net",
    "https://<mytenant>.onmicrosoft.com/<myappregname>"
  ]
}

• If the application is multi-tenant you will need to ensure the domain used in this URL is verified with Azure
• You must also ensure the user_impersonation scope is available for the application:

{
  "oauth2Permissions": [
    {
      "adminConsentDescription": "Allow the application to access myapp on behalf of the signed-in user.",
      "adminConsentDisplayName": "Access myapp",
      "id": "xxxxx-xxx-xxx-xxx-xxxxxxx",
      "isEnabled": true,
      "lang": null,
      "origin": "Application",
      "type": "User",
      "userConsentDescription": "Allow the application to access my on your behalf.",
      "userConsentDisplayName": "Access my app",
      "value": "user_impersonation"
    }
  ]
}

• Save changes.

Issue 3 - Allowed token audience

Now back in Excel, you should be able to get past the sign-in but when clicking on "Connect" you may get this error:

Now looking in fiddler you will see the AAD login works and returns a token but when this is sent to the API you get a 401.

This is only an issue if you have enabled AAD via code rather than through the Azure portal (see Issue 1 above!). To fix it you need to ensure the TokenValidationParameters class passed to the owin middleware has ValidAudience set to the actual url of your deployed API.

Run the query

With all that set-up everything should now work, back in Excel ...

• Click sign-in a pop-up will open and take you to the Microsoft sign-in page for the tenant you configured, sign-in with you credentials
• Click Connect
• PowerQuery editor will then open and display the retrieved data from the API
• Click Home > Advanced Editor you will be able to view the raw query - this is in ​M-query syntax the query language used by PowerQuery, in my case the data was a flat array so this sufficed:

let
    Source = Json.Document(Web.Contents("https://api.myapp.net/Products")),
    #"Converted to Table" = Table.FromRecords(Source)
in
    #"Converted to Table"

• Click Close & Load to return the data to the Excel worksheet

How this works

In case you care (and are still reading this!), the way this works seems to be:

1. PowerQuery requests access to your API under the "Microsoft PowerQuery For Excel" built-in application (client id a672d62c-fc7b-4e81-a576-e60dc46e951d)
2. When you sign-in AAD grants the dynamic scope user_impersonation on your API (identified by the resource URL https://api.myapp.net) to the "Microsoft PowerQuery For Excel" app
3. You can see this in the portal by going to Enterprise Applications, checking Microsoft Applications and searching for Microsoft PowerQuery For Excel

Sorry for the long post but hopefully this helps somebody do something seemingly quite trivial - pull data from an API in Azure into Excel!

Answer 2

I have managed to get past this issue by publishing my WebAPI to an Azure Web App. Interestingly, when its hosted on Azure it prompted to allow "Power Query for Excel" access. The issue could be limited to the fact I was running it on IIS Express on my Dev box.