PowerBI scheduled refresh fails sometimes while connecting to Athena data source

[anand_mohan]

Hello Guys,

I have an issue with PowerBI scheduled refresh .The Refresh fails(2-3 times a day) sometimes but it also works fine during the rest of the day . I am using standard Gateway for connecting PowerBI Service to the data source . The data source is Amazon Athena .The error message shown "Data source error : The credentials provided for the Odbc source are invalid. (Source at dsn=xxxxxxxx)"

Can you please tell me how to fix the issue?

Following is the detailed PowerBI scheduled refresh error :-

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the Odbc source are invalid. (Source at dsn=xxxxxxx.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"dsn=xxxxxxx"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Odbc\",\"path\":\"dsn=xxxxxxx\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}} Table: new.

Cluster URI: XXXX-NORTH-EUROPE-F-PRIMARY-redirect.analysis.windows.net

Activity ID: af6eb99e-8ed4-49df-b3db-85a5aXXXXXX

Request ID: cd3364cd-ecb4-468d-9df8-78105eXXXXX

Time: 2023-06-02 03:32:38Z

[anand_mohan]

Thanks for the response Neeko Tang....
Just a quick question if these  above listed points are the reason , it should fail always but in my case the refresh works most of the time ,while it also fails 2-3 times a day.
could you please suggest me a direction?  

[v-tangjie-msft · Accepted solution]

Hi @anand_mohan ,

One possible cause for this type of behavior could be resource issues, such as memory. Here are some additional steps to investigate intermittent refresh failures:

(1)Check for resource issues: Monitor the memory and CPU usage on the machine running the gateway during the refresh process. If you notice high resource usage or low available memory, it could be causing the intermittent failures.

(2)Examine the logs: Review the logs from the Power BI service and the gateway for any additional information or error messages that might help diagnose the issue. Look for patterns or specific times when the failures occur.

(3)Network stability: Intermittent network issues could also cause refresh failures. Check the network connectivity between the gateway and the data source (Amazon Athena) during the refresh process.

(4)Gateway updates: Ensure that the gateway is up-to-date with the latest version. Outdated gateways might have known issues that have been resolved in newer versions.

(5)Schedule adjustments: If the refresh failures occur during specific times of the day, consider adjusting the refresh schedule to avoid peak usage times or periods of high resource demand.

Best Regards,

Neeko Tang

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

[v-tangjie-msft]

Hi @anand_mohan ,

You can try the following steps to resolve this issue:

1.Verify that the credentials provided for the ODBC source are correct.

2.Check if the ODBC driver is installed on the machine where the gateway is running.

3.Ensure that the user credentials used to add the data source to the gateway are the same as the administrator of the machine where the gateway is running.

4.Collect procmon traces on the gateway machine while trying to refresh on the service and look into procmon traces to see if there are any "ACCESS DENIED" entries.

5.Provide read/write access to the user to the path C:\Users\PBIEgwService\AppData\Local\Temp.

6. The local data gateway is incorrectly configured or does not support authentication methods. You may need to check the gateway connection, and the supported authentication mechanisms.

Best Regards,

Neeko Tang

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