Retrieve data from an external source

[Wingmen]

Hello PowerBI experts,

I'm new to PowerBI and have a problem I can't quite figure out yet.

We work with a company where we enter data. The company created an API connection for us with a client certificate. We received the following from the company: URL, .PFX + password, .PEM, .CSR, and .Key.

What works:

- Visit URL through browser and login with the .pfx that was included. We can see the data.

- Via the webversion of PowerBI we can connect to the server (Entering url and the long key thats included in .key file. (PowerBI shows the "Online" symbol.)

What isn't working:

- Logging in with PowerBI desktop APP. We keep getting the error: Web.Contents could not retrieve the data. (400) bad request. There is not option to select the .pfx or .pem certificate.

I login as follows:

1. Open PowerBI ->Get Data->Web->Enter URL->OK

2. I get the following options: Anonymous, Windows, Basic, Web-API, Organisation account.

3. Select Web-API. Paste the very long key thats in the .key file that was included. (The same key i used for PowerBI webversion)

4. Connect.

5. Web.Contents could not retrieve the data. (400) bad request.

After a quick Google search, I discovered that PowerBI desktop cannot use client certificates to authenticate to an API.

I'm finding many different solutions online, but I have no idea which is the easiest and safest way. Can anyone give me some advice? (btw we have a Microsoft365 environment.)

Thank you very much!

[v-veshwara-msft]

Hi @Wingmen ,

Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.

Thank you.

[v-veshwara-msft]

Hi @Wingmen ,

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[v-veshwara-msft · Accepted solution]

Hi @Wingmen ,

Thanks for reaching out to Microsoft Fabric Community.

I understand the confusion, especially since the API works in the browser and Power BI Service but not in Power BI Desktop.

Power BI Desktop does not support client certificate based authentication for API calls, so it cannot directly use .pfx, .pem, or similar certificate files when calling an endpoint. Because of this, the behavior you are seeing in Desktop is expected.

Solved: Issues using a Web Certificate to authenticate API... - Microsoft Fabric Community

From an ease and security perspective, one option is to check with the API provider whether they can expose a token based authentication method such as OAuth2 or bearer tokens, which are fully supported in Power BI Desktop.

Power Query Web connector - Power Query | Microsoft Learn

Solved: How to pull data from Web API into PowerBI using A... - Microsoft Fabric Community

If certificate based authentication cannot be changed, another approach is to introduce an intermediate service such as Azure Function or Azure API Management. In this model, the intermediate service handles the client certificate and mutual TLS, while Power BI connects using a standard HTTPS endpoint with supported authentication.

Secure APIs using client certificate authentication in API Management - Azure API Management | Micro...

A custom connector can also solve this, as mentioned earlier, but it requires additional development and ongoing maintenance, so it is typically considered when the authentication method cannot be changed.

Develop a connector using the Power Query SDK - Power Query | Microsoft Learn

Hope this helps you decide on the safest and most practical approach for your setup.

Thank you.

[GeraldGEmerick]

@Wingmen It is very likely that you will need to create a custom connector for this source. Creating connectors is not super difficult but it is also not trivial either. There are a lot of samples available: TripPin 1 - OData - Power Query | Microsoft Learn