Power BI: Using anonymous authentication on Web data sources with a API key in URL

Currently, there are some challenges on Power BI service when trying to Schedule Refresh from a Web API data source that uses an API key for authentication. On this post, I will talk about a way to work around this problem by using an Azure Functions Proxy. The proxy will be used the override the request to the Web API and successfully use anonymous authentication.

Scenario overview

I have some reports on Power BI that get the data from WorkflowMax API, they have been published on Power BI service and embedded on SharePoint Online. These reports have the correct data when they get published to Power BI service, but the dataset can’t be refreshed after being published, either by using the Refresh Now or Schedule Refresh options. I get an error when trying to configure the dataset credentials to connect to WorkflowMax API due to the use of a key in the URL.

Problem

Steps to reproduce

Step 1: From Power BI desktop use the get data from Web to connect to WorkflowMax API.
Problem-step1
WorkflowMax API requires AccountKey and ApiKey for Authentication which need to be provided on the URL as parameters on the following format:

https://api.workflowmax.com/ [yourPath]? accountKey=[yourAccountKey]&apiKey=[yourAPIKey]

Step 2: Clean and format the dataset, create report and publish it to Power BI Service.
Note: This is not covered on this blog.

Step 3: On Power BI service click on the three dots of your dataset and select Schedule Refresh, under Data source credentials following error appears:
problem-step31.png
If you edit credentials and try to log in using anonymous access, you will get the error “Failed to update data source credentials: The credentials provided for the Web source are invalid” because the API key is missing.
problem-step32

Solution using an Azure Functions Proxy

A possible solution is to create an Azure Function Proxy to override the request to WorkflowMax API, by using this approach the Power BI dataset will connect to the proxy instead of the WorkflowMax API.

Note: You will need an Azure subscription for this solution

Steps to create the Azure Function Proxy

Step 1. Log into your Azure Portal and create an Azure Function.
solution-function
Step 2. Expand your Azure Function and create a Proxy with the following configuration for connecting to the WorkflowMax API.
Solution-proxy.png
After the creation you will get the proxy URL that is going to be called instead of the API.
https://sample-wfm-proxy.azurewebsites.net/{apiKey}/{accountKey}/{*restOfPath}

Steps to update the data source from API to the Proxy

Step 1. On your Power BI solution, open the Power Query Editor that lists all the datasets and add apiKey, apiAccount and fromDate as parameters.
solution-parameters
Step 2. Add toDate as empty query and set it to current date.
solution-toDate
Step 3. Change source of all the dataset sources from calling the API to calling the Azure Function Proxy.
From:
from.png
To:
time-to
Step 4. Click done, use anonymous authentication to connect to the Azure Function Proxy if prompted.
login-anonymous
Step 5. Publish your report to Power BI service and Schedule Refresh on the dataset, you will get the same error as before, but now you will be able to log in with anonymous access.
solution-step5 publish
Login with anonymous access.
solution-step5 login
Step 6. Now, you will be able to schedule the refresh.
solution-step6

Conclusion

By using an Azure Function Proxy it is possible to bypass the Power BI restriction of not being able to do anonymous authentication on Web data sources that require an API key.

Follow ...+

Kloud Blog - Follow