Unnie Ayilliath

Accessing OAuth 2.0 protected APIs from Power BI requires you to built custom connectors which handles the OAuth flow. However, if the APIs are using Microsoft Entra ID based authentication/authorization, then with some additional configurations at the API side you can easily allow Power BI / Power Platform to connect to your APIs without building custom connectors.

This blog post explores the additional configuration needed which allows Power BI and other Power Platform applications to connect to APIs which are protected with Microsoft Entra ID using OAuth 2.0 .

Understand the Power Query supported workflow for Microsoft Entra ID protected APIs

The supported workflow for power query authentication is as follows:

1. PowerBI will send a GEt request to the APIs with an empty Bearer token in the Authorization header.
2. The API on receiving the request must check if there is empty bearer token in the Authorization header and return 401 response code along with below header:

WWW-Authenticate : ‘Bearer authorization_uri=https://login.microsoftonline.com/<tenant id>/oauth2/authorize‘

Prerequisites

Now, to implement this supported workflow , your set up needs to satisfy following prerequisites:

1. You should have an API which is integrated with Microsoft Entra ID for authentication.
2. The APIs must be hosted on your company sub domain e.g https://api.mycompany.com .
3. Your root domain e.g mycompany.com must be a verified domain of your Microsoft Entra ID tenant.

Additional Configuration

Now, if all the pre-requisites are met, let’s start with the additional configuration needed.

1. Update Microsoft Entra ID App's Application ID URI

Update the Application ID URI of the Microsoft Entra ID App used for API authentication to be same as the domain name of the APIs .

i.e if your APIs are hosted under https://api.mycompany.com then Application ID URI must be https://api.mycompany.com .

Visit Microsoft Entra ID App registration page for the app and in “Expose an API” page, update the Application ID URI.

2. Authorize client applications of Power Query & other Power Platform apps to your Entra App

Next, in the “Expose an API” screen under Authorized client applications section add Microsoft Entra Client IDs used by the various Power Platform apps.

Client ID	Title	Description
a672d62c-fc7b-4e81-a576-e60dc46e951d	Power Query for Excel	Public client, used in Power BI Desktop and the gateway.
b52893c8-bc2e-47fc-918b-77022b299bbc	Power BI Data Refresh	Confidential client, used in Power BI service.
7ab7862c-4c57-491e-8a45-d52a7e023983	Power Apps and Power Automate	Confidential client, used in Power Apps and Power Automate.

Table Source: https://learn.microsoft.com/en-us/power-query/connector-authentication#microsoft-entra-id-client-ids

3. Configure module in API to force authentication

Next step is to configure the APIs to return 401 response code along with WWW-Authenticate header whenever it receives any request with empty bearer token in the Authorization header. Now, this step varies depending on your actual implementation of the APIs.

Below is a sample implementation of it using Azure APIM:

Step 1: Power BI sends a GET request with empty Bearer token.

Step 2: Azure APIM policy fragment called “ForceAuthentication” checks the Authorization header for empty bearer token and responds with 401 unauthorized and WWW-Authenticate header.

<fragment>
	<choose>
		<when condition="@{
            return context.Request.Headers.ContainsKey("Authorization") && context.Request.Headers.GetValueOrDefault("Authorization").Trim() == "Bearer";
        }">
			<return-response>
				<set-status code="401" reason="Unauthorized" />
				<set-header name="WWW-Authenticate" exists-action="override">
					<value>Bearer authorization_uri=https://login.microsoftonline.com/{{tenantId}}/oauth2/authorize</value>
				</set-header>
				<set-body>Acces token is missing</set-body>
			</return-response>
		</when>
	</choose>
</fragment> 

Step 3: Once the response is received, Power BI will send a authorization request to the endpoint provided in the WWW-Authenticate header by the API and also use the domain of the API as Audience/ Scope. This is why it is important to match the API domain name and the Application ID URI of the Entra app.

Step 4: After successful authentication, Entra ID will return the necessary access tokens.

Step 5: Power BI will now send another GET request , but this time with the correct access tokens in the Authorization header.

Step 6: Once the Azure APIM, receives proper access tokens, they are validated and if succeeded data is returned to Power BI client.

Reference:

https://learn.microsoft.com/en-us/power-query/connector-authentication#supported-workflow