Power BI Rest API authentication failed in Service

[Remo_]

Hi there folks,

To get actual automated insights in our dedicated Power BI environment (workspaces, apps, datasets, reports and users) we are trying to build a dataset which fetches this information on a schedule using the Power BI Rest API. In Power BI desktop everything works fine and I can refresh locally without any issues. But when publishing the file to the service it results in an issue regarding the data source credentials:

This notification tells me there is an issue in the identification, as navigating to https://api.powerbi.com gives back an authorization issue, but does not through an 404 error. However, we do get a token locally which is used when refreshing without any issues. Anyone got any suggestions why this mechanism is working locally, but not once published to the service? (I have already tried this option, doesn't help: https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/)

Our setup is as follows

We get a token via a custom function with the following m query. The clientid an secret that we use is a Service Principal (Admin ReadOnly service account) which should have read access to all that resided in our capacities.

This token we use in the Web.Contents() function in the m query which yields a table with data on our capacities (for this example):

We first create the httpHeader because the following blog: https://kohera.be/power-bi/the-hidden-gems-of-the-function-web-contents/ mentioned that due to static evaluation of m code in the Power BI service the token is not being evaluated before the web URL. However, this didn't make any difference whatsoever.

Lastly, the general authentication method for the web calls has been set to 'Anonymous'

I've gone through quite some posts already, but haven't come accross the answer yet. E.g. the following post: https://community.powerbi.com/t5/Developer/Power-Bi-rest-API-gives-errors-on-Power-BI-service-when/m... 

Hope anyone has encountered (and solved) the same issue!

[kjohnson25]

I'm running into the same problem and checking 'Skip test connection' did NOT work for me.  The issue still results in "Failed to update data source credentials" and then I am unable to configure a Refresh schedule.

Note that I'm using the same method of Anonymous authentication when the dashboard is uploaded to an on-prem server.  I get the same data source error however the server still allows me to configure a refresh schedule.  However when moving to the cloud I am unable to proceed to the Refresh configuration without surmounting this issue.  Any other suggestions for resolution?  thank you.

[Remo_ · Accepted solution]

For anyone stumbling into this topic. Making a data gateway for both connections and skipping the test connection solved the issue. It now refreshes without any issues in the server.

[zetazeta]

Thank you so much for sharing this Remo! It worked right away and saved me hours of trial and error. 

[ImkeF]

Hi @Remo_ ,
I usually use a custom connector for this task, but looking the query that generates the token, you have a dynamic data source in there. Check out Chris' blog you've mentioned and create a relative path for the tenant ID.
Although it doesn't explain the error-message, it might be worth a try.

[Remo_]

Hi @ImkeF,

Thanks for your reply. Unfortunately, the error message is not on linked to the generate token query but to the query that fetches the data from https://api.powerbi.com . I have tried anyway to replace the tenantId with a relative path in the Web.Contents function but this did not make any difference.

You mention using a custom connector. Before I start looking into that, why would this (theoretically) solve my problem?

Regards,

Remo