Intermittent On-Prem refresh failure: "The credentials provided for the Web source are invalid"

[kjohnson25]

I have a datasource connecting to web REST API endpoint passing an API key as follows:

Json.Document(Web.Contents("https://myendpoint.com/api?myargs=myvals", 
    [Headers=[#"api-key"="mykeyvalue"]]))

authentication type = Anonymous.

This refresh always succeeds when I run locally, however only intermittently (occasionally) fails once uploaded to the On-Prem server.

[0] -1055784932: The credentials provided for the Web source are invalid. (Source at https://myendpoint.com/api/). The exception was raised by the IDbCommand interface.
[1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.
[2] -1055129594: The current operation was cancelled because another operation in the transaction failed.
[3] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.

I cannot figure out why this is an intermittent failure and why it only fails when attempting a refresh on the server.  If I trigger the refresh manually it may fail a couple times, then pass again without changing any settings.  I don't believe it is due to an unstable endpoint because I cannot reproduce the failure using other mechanisms such as postman or python connections of the same manner.

I've tried wrapping the M-code call with a 10x retry loop (using 'try' handling) with 10s wait time between calls with no success either. 

Any tips or advice would be appreciated as I cannot understand what possible reasons for this error could be.

thank you!

[v-jialongy-msft]

Hi @kjohnson25 

Given the intermittent nature of the failure and the fact that it only occurs on the On-Prem server, here are a few steps and considerations that might help resolve or isolate the issue:

1. Verify Gateway Configuration: Ensure that the On-Premises Data Gateway is correctly configured and running. The gateway acts as a bridge for data refreshes from on-premises sources to the cloud. For detailed steps on setting up and configuring the gateway, please refer to the official documentation: Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn
   
   

2. Check for Gateway Updates: Make sure that your On-Premises Data Gateway is up to date. Outdated gateways might have issues with certain types of connections or authentication methods.
   
   

3. Review API Rate Limits: Although you mentioned that the endpoint seems stable when tested with Postman or Python, intermittent failures on the server could still be related to API rate limits or throttling. It's possible that the server's refresh attempts are being rate-limited. 
   
   

4. Investigate Network Issues: Since the issue is specific to the server environment, it might be worthwhile to investigate any network-related issues that could be affecting the connection to the API endpoint. This includes checking firewall settings, DNS resolution, or any network security appliances that might be interfering with the connection.

Best Regards,

Jayleny

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

[kjohnson25]

thanks for the tips, I do appreciate them.  suggestions #1-2 don't apply as the dashboard is hosted on the on-prem server and does not use a cloud gateway.  RE #3, the number of refreshes are very low, approximately less than 10 per day.  I can run this many with postman and not hit such a limit.  If #4 then it would have to be on the server side since this is the only place it is reproduced. 

Unfortunately this still leaves this issue unsolved.  I may also add that there other dashboards that access this same endpoint which fail far less frequently than this particular dashboard.  I'm thinking perhaps there is something in the data model which could exacerbate this symptom.  Investigation is still open.