Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Refreshing report with Web and SQL on-premise data source

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/Verhuur/DataMaatwerk.xlsx.)
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:https://waardwonen365.sharepoint.com/sites/werkgroepinformatiemanagement/Gedeelde%20documenten/Verhuur/DataMaatwerk.xlsx
Microsoft.Data.Mashup.CredentialError.Reason:AccessUnauthorized
Microsoft.Data.Mashup.MashupSecurityException.DataSources:[{"kind":"Web","path":"https://waardwonen365.sharepoint.com/sites/werkgroepinformatiemanagement/Gedeelde%20documenten/Verhuur/DataMaatwerk.xlsx"}]
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?

Need Help
  • arvindsingh802's avatar
    arvindsingh802
    5 years ago

    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

     

5 Replies

  • arvindsingh802's avatar
    arvindsingh802
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.