Cannot validate anonymous credentials from Desktop to set up scheduled refresh in Service

[user180618]

I am working with an API in Power Query and have this query step that works fine in Power BI Desktop (Anonymous authentication method and Organizational privacy):

Source = Json.Document(Web.Contents("https://api.website.com", [Headers=[Authorization="Basic abcdef12345", #"User-Agent"="PostmanRuntime/7.36.3", Accept="*/*", Connection="keep-alive"]]))

I want to schedule a refresh but when I try to validate the credentials in settings in Power BI Service, it does not let me select Anonymous and gives this error: "Failed to update data source credentials: The credentials provided for the Web source are invalid."

If I try configuring it with basic username and password (basically my above authorization string decoded), it accepts the credentials but at the next scheduled refresh returns this error:

"Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer. . The exception was raised by the IDbCommand interface. Table: tags."

Is there no way around this?

[Anonymous]

Hi @user180618 

The error message you're seeing, "Failed to update data source credentials: The credentials provided for the Web source are invalid," typically occurs when Power BI Service does not support the authentication method you're trying to use. As per the Power BI documentation, Power BI Service has specific requirements for data source authentication and does not support anonymous credentials for certain types of data connections, including DirectQuery connections (Data Sources in Power BI Report Server).

For Web data sources, using the 'Authorization' header is indeed restricted to connections made using anonymous authentication. This is why you're encountering the second error when attempting to use basic authentication with the 'Authorization' header included.

If possible, adjust the API to accept a different form of authentication that is supported by Power BI Service. This might involve using API keys passed as part of the query parameters or within the request headers that don't require the 'Authorization' header.

For data sources that require credentials not supported directly in Power BI Service, using an on-premises data gateway in personal mode might provide a solution. The gateway acts as a bridge and can handle different types of authentication methods. However, note that while the gateway can facilitate connections, it still might not support the use of the 'Authorization' header directly.

On-premises data gateway - Power BI | Microsoft Learn

Develop a custom connector for Power BI that handles the API authentication in a manner compatible with Power BI Service. This is a more advanced solution and requires familiarity with the Power Query M language and the Power BI Custom Connectors SDK.

Custom connectors overview | Microsoft Learn

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[user180618 · Accepted solution]

Thanks for the explanation, I will try what you suggested. In the meantime I have got a workaround working by setting up a scheduled flow in Power Automate that refreshes the dataset.