How to connect Power BI Service to an API with separate token authentication using service principal

[SreeSL12121]

Question:

We're working on integrating Power BI with a REST API that requires authentication via a service principal (client ID and secret). However, the setup has some challenges:

• The token endpoint is separate from the actual data API endpoint (i.e., they're on different domains).

• We can successfully retrieve an access token by calling the token endpoint using client credentials.

• The data API requires this token to be included in the Authorization header of each request.

• This token flow does not follow the standard OAuth2 pattern expected by Power BI, and Web v2 connectors cannot be used in Power BI Service.

• We want to set up this connection to support scheduled refresh in Power BI Service (cloud).

Given this setup:

1. What is the recommended way to securely get the token and pass it along with API requests within Power BI, considering this is not a standard OAuth2 flow?

2. If we build a custom data connector that handles both token generation and API requests:

   • Will it require an on-premises data gateway, even though the API is publicly accessible over HTTPS?

3. What’s the best approach to support automated refresh in the Power BI Service for this authentication model?

We’re trying to avoid unnecessary complexity or infrastructure (like a gateway) if possible, but want to keep the authentication secure and reliable.

Any guidance or recommendations would be appreciated.

Here is our sample powerquery code:

let
// === CONFIG ===
api_url = apiurl,
token_path = tokenuri,
scope =scope_,
ClientID = clientid,
Secret = clientsecret,

// === TOKEN REQUEST ===
token_body =
"client_id=" & ClientID &
"&client_secret=" & Secret &
"&scope=" & scope &
"&grant_type=client_credentials",

tokenResponse = Json.Document(
Web.Contents(
token_path,
[
Content = Text.ToBinary(token_body),
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"]
]
)
),
accessToken = tokenResponse[access_token],
AccessTokenHeader = "Bearer " & accessToken,

// === API REQUEST ===
response_body = "{ ""ShowOnlyCompleted"": ""true"" }",

dataResponse = Json.Document(
Web.Contents(
api_url,
[
Content = Text.ToBinary(response_body),
Headers = [
#"Content-Type" = "application/json",
#"Authorization" = AccessTokenHeader
]
]
)
),
#"Converted to Table" = Table.FromList(dataResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

in
#"Converted to Table"

[SreeSL12121]

Hi @v-sgandrathi 

Thank you for the information. The client has opted to connect directly to the database for now, as implementing Azure Functions is considered too involved.

[v-sgandrathi]

HI @SreeSL12121,

Good to hear that you have opted to connect directly to the database for now. This approach definitely simplifies the setup and avoids additional infrastructure like Azure Functions or gateways, especially when you want to move quickly and keep things straightforward. Just keep in mind that if your database is on-premises or behind a firewall, you may still need an on-premises data gateway for scheduled refresh in Power BI Service.

Please consider marking the useful reply as accepted solution if it helped you, this will make it easier for others in the community to find the solution. 

Thank you for using Microsoft Fabric Community Forum!

[v-sgandrathi · Accepted solution]

Hi @SreeSL12121,

Try the below suggestion:

To avoid using an on-premises data gateway, you can use Azure Functions or Logic Apps as a secure middleware proxy. This setup allows Power BI to connect to a single, simplified public endpoint.

The Azure Function handles the complex parts: it first calls the token endpoint using client credentials, gets the access token, then makes the actual API request with the required headers, and finally returns clean JSON data to Power BI.

In Power BI, you just use a basic Web.Contents() call to the Azure Function URL with anonymous authentication, since all authentication logic is handled within the Azure Function itself. To keep it secure, you should store secrets in Azure Key Vault or the function’s App Settings, and protect the function using an API key or IP restrictions.

This method ensures secure, reliable, and gateway-free scheduled refresh in Power BI Service.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thanks for using Microsoft Fabric Community Forum.

[v-sgandrathi]

Hi @SreeSL12121,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you.