Forum Discussion
Retrieve data from an external source
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!
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.
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.
4 Replies
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
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.
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.
Hi Wingmen ,
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.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.
