Refreshing report with Web and SQL on-premise data source

[Anonymous]

I have a report uploaded to the Power BI service and I want to be able to refresh this report on powerbi.com. The report contains queries that use an on-premise SQL server data source and queries that use a web data source (the data is retrieved from Excel files on a Sharepoint site). I installed an enterprise gateway on an on-premise server. Using my login I can connect to my SQL source via the gateway. However, I cannot connect to my web sources via the gateway. The following error occurs:

Unable to connect: We encountered an error while trying to connect to . Details: ""

Activity ID:	761905a4-23a4-4a2b-b6cf-1e58f96589f4
Request ID:	6403aec8-7ee1-9da9-492e-dcc322ac931b
Cluster URI:	https://wabi-west-europe-d-primary-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_UpdateClusterDatasourceCredentialsErrorCode
Time:	Mon Jun 14 2021 15:55:38 GMT+0200 (Midden-Europese zomertijd)
Service version:	13.0.16158.72
Client version:	2105.4.06405-train AX-SQL-P1 - PowerBI gateway: Invalid connection credentials. Underlying error code: -2147467259 Underlying error message: The credentials provided for the Web source are invalid. (Source at https://waardwonen365.sharepoint.com/sites/werkgroepinformatiemanagement/Gedeelde%20documenten/Verhu... DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.CredentialError.DataSourceKind: Web Microsoft.Data.Mashup.CredentialError.DataSourcePath: https://waardwonen365.sharepoint.com/sites/werkgroepinformatiemanagement/Gedeelde%20documenten/Verhu... Microsoft.Data.Mashup.CredentialError.Reason: AccessUnauthorized Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Web","path":"https://waardwonen365.sharepoint.com/sites/werkgroepinformatiemanagement/Gedeelde%20documenten/Verhu... Microsoft.Data.Mashup.MashupSecurityException.Reason: AccessUnauthorized How can I solve this error and be sure I can refresh my SQL sources as well as my Excel sources from my Sharepoint sites?

[arvindsingh802]

As per error message the issue is with crdentails, to validate this try to refresh the PBIX on your local desktop and see if it fails.

also under data source setting make sure you have update the credentails for source.

If this post helps, then please consider Accept it as the solution and give it a thumbs up

[Anonymous]

In Power BI Desktop I can refresh my report as it should. However, I cannot do this in the service.

The problem is that refreshing my SQL Server source is working while it does not work for my web sources. In the data set of the report I have mapped my SQL data source in the gateway to the SQL Server using my credentials. This works. However, the mapping of the Sharepoint Excel sources does not work because it shows the above issue. I find this message strange because I connected the web source using Anonymous (so no credentials) and now suddenly credentials seem to be the problem.

[arvindsingh802]

Are you merging the data from SQL and Web together, if yes then you have to enable "Allow user's cloud data sources to refresh through this gateway cluster" option in Gateway setting. (Merge or append on-premises and cloud data sources - Power BI | Microsoft Docs)

If this post helps, then please consider Accept it as the solution and give it a thumbs up

[Anonymous]

This report indeed has a data set in which data from the SQL Server and from the web are refreshed all at once. The option you mentioned was already enabled. My configuration looks like this:

and

where the last picture is my configuration of one of the Web data sets I try to establish.

[arvindsingh802 · Accepted solution]

You have to use OAuth2 as authentication.

If this post helps, then please consider Accept it as the solution and give it a thumbs up