On-Premise Data Gateway error for Web Data Source - underlying error: (404): File Not Found

[Anonymous]

I have a Power BI Desktop file published in the Power BI Service that uses 3 different data sources, however these all point to lists in the same SharePoint 2013 (on-premise) site. I have found that by using different data connectors, I can achieve everything I need to for this report. Details of connectors used, are:

1. SharePoint list (good for accessing basic list data): http://<servername>.com/<sitepath>/<sitename>
2. OData feed (good for accessing and formatting list data such as "Person or Group" field values): http://<servername>.com/<sitepath>/<sitename>/_vti_bin/ListData.svc
3. Web (to access all version history for all list items in one table): http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll?XMLDATA=1&List={<ListID>}&View={<ViewID>}&RowLimit=0&RootFolder=%2f<SitePath>%2f<SiteName>%2fLists%2f<ListName>Selection={<ItemID>}-{<ItemID>}EditWebPage=Formatting=NonePreFormattedTextToColumns=TrueConsecutiveDelimitersAsOne=TrueSingleBlockTextImport=FalseDisableDateRecognition=FalseDisableRedirections=FalseSharePointApplication=http://<ServerName>.com/<SitePath>/<SiteName>/_vti_binSharePointListView={<ViewID>}SharePointListName={<ListID>}RootFolder=/<SitePath>/<SiteName>/Lists/<ListName>&IncludeVersions=TRUE

I have successfully run this report on a scheduled refresh (twice daily) for the last 6 months via a personal data gateway. We are now setting up an on-premise data gateway and while I have managed to establish two of the three sources successfully (listed above 1 & 2), I am seeing a "File Not Found" error for the third (listed above 3). Here are the details of the error I am seeing:

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

Activity ID:	<Activity ID>
Request ID:	<Request ID>
Cluster URI:	https://wabi-south-east-asia-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Mon Jan 13 2020 11:19:35 GMT+1300 (New Zealand Daylight Time)
Service version:	13.0.11747.315
Client version:	1912.2.031

GGLAZAUEPBIVM02-Gateway:	Unable to connect to the data source undefined.
Underlying error code:	-2147467259
Underlying error message:	Web.Contents failed to get contents from 'http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll' (404): File Not Found
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Web
Microsoft.Data.Mashup.ValueError.DataSourcePath:	http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Microsoft.Data.Mashup.ValueError.Url:	http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll

Is there any way we can get this data source successfully added to the new gateway? Please let me know if any of the blanked information above is required. Many thanks in advance 🙂

[Anonymous]

I have a Power BI Desktop file published in the Power BI Service that uses 3 different data sources, however these all point to lists in the same SharePoint 2013 (on-premise) site. I have found that by using different data connectors, I can achieve everything I need to for this report. Details of connectors used, are:

1. SharePoint list (good for accessing basic list data): http://<servername>.com/<sitepath>/<sitename>
2. OData feed (good for accessing and formatting list data such as "Person or Group" field values): http://<servername>.com/<sitepath>/<sitename>/_vti_bin/ListData.svc
3. Web (to access all version history for all list items in one table): http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll?XMLDATA=1&List={<ListID>}&View={<ViewID>}&RowLimit=0&RootFolder=%2f<SitePath>%2f<SiteName>%2fLists%2f<ListName>Selection={<ItemID>}-{<ItemID>}EditWebPage=Formatting=NonePreFormattedTextToColumns=TrueConsecutiveDelimitersAsOne=TrueSingleBlockTextImport=FalseDisableDateRecognition=FalseDisableRedirections=FalseSharePointApplication=http://<ServerName>.com/<SitePath>/<SiteName>/_vti_binSharePointListView={<ViewID>}SharePointListName={<ListID>}RootFolder=/<SitePath>/<SiteName>/Lists/<ListName>&IncludeVersions=TRUE

I have successfully run this report on a scheduled refresh (twice daily) for the last 6 months via a personal data gateway. We are now setting up an on-premise data gateway and while I have managed to establish two of the three sources successfully (listed above 1 & 2), I am seeing a "File Not Found" error for the third (listed above 3). Here are the details of the error I am seeing:

Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

Activity ID:	<Activity ID>
Request ID:	<Request ID>
Cluster URI:	https://wabi-south-east-asia-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Mon Jan 13 2020 11:19:35 GMT+1300 (New Zealand Daylight Time)
Service version:	13.0.11747.315
Client version:	1912.2.031

GGLAZAUEPBIVM02-Gateway:	Unable to connect to the data source undefined.
Underlying error code:	-2147467259
Underlying error message:	Web.Contents failed to get contents from 'http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll' (404): File Not Found
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	Web
Microsoft.Data.Mashup.ValueError.DataSourcePath:	http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Microsoft.Data.Mashup.ValueError.Url:	http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll

Is there any way we can get this data source successfully added to the new gateway? Many thanks in advance 🙂

[v-kelly-msft]

Hi @Anonymous ，

The 400 ERROR means a bad http request. Please updata on-premise gateway to the lastest version. If you already have updated the gateway to the latest version, then you can refer to steps below for troubleshooting.

1. Check if the server is using a proxy and the proxy. Some proxies restrict traffic to only ports 80 and 443. By default, communication to Azure Service Bus will occur on ports other than 443.

2. You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. You will need to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. Change the value from AutoDetect to Https. This file is located, by default, at C:\Program Files\On-premises data gateway.

3. Restart the gateway and check if the issue persists. 

• To stop the service, run this command:

  ''' net stop PBIEgwService '''

• To start the service, run this command:

  ''' net start PBIEgwService '''

4. You may export the data gateway log by selecting Diagnostics and then selecting the Export logs link near the bottom of the page. Reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#tools-for-troubleshooting.

Hope this would help.

Best Regards,

Kelly

[Anonymous]

Hi @v-kelly-msft 

Thanks for your reply, we have the latest version of the software and have completed the checks you suggested:

1. Port 80 is being used.
2. On this version of SharePoint, we don't use https. Is that an issue? Like I said I have successfully configured the other data sources which query this same site. 
3. My colleague restarted the gateway and I am still seeing this error.
4. We have exported the data gateway log, should I send this to you directly to help further? 

Many thanks,

Olivia

[v-kelly-msft]

Hi @Anonymous ，

If so ,pls try to shift to  HTTPS instead of direct TCP,then restart the gateway  and see if the error still occurs.

Best Regards,

Kelly

[Anonymous]

Hi @v-kelly-msft 

I have checked and HTTPS was already enabled. Is there anything else we can do?

Thanks

Olivia

[v-kelly-msft]

Hi @Anonymous ，

Try to use an empty .pbix file to connect with the following URL using a web connector(shown as below):

http://<ServerName>.com/<SitePath>/<SiteName>/_vti_bin/owssvr.dll 

Don't write any parameters inside,then publish to service to check whether it can configure the  datasource from sharepoint.

Sometimes it may happen when the gateway machine cant visit the datasource, using the above steps to rule out the factor.

Best Regards,

Kelly

[Anonymous]

Hi @v-kelly-msft 

I tried this and I get file not found errors in both Power BI Desktop and in a browser. When I enter the full URL I see the data in both places. I think this is due to the conversion of the data to XML? That's what the rest of the URL defines. 

My colleague configuring the gateway has also confirmed that when he puts the full URL into the browser on the server the gateway is configured on, he sees the XML data as well.

Thanks

Olivia

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Try to add the following query into query editor:

 let 
Source = Web.Contents("http://<ServerName>.com/<SitePath>/<SiteName>",[RelativePath = "/_vti_bin/owssvr.dll?XMLDATA=1&List={<ListID>}&View={<ViewID>}&RowLimit=0&RootFolder=%2f<SitePath>%2f<SiteName>%2fLists%2f<ListName>Selection={<ItemID>}-{<ItemID>}EditWebPage=Formatting=NonePreFormattedTextToColumns=TrueConsecutiveDelimitersAsOne=TrueSingleBlockTextImport=FalseDisableDateRecognition=FalseDisableRedirections=FalseSharePointApplication=http://<ServerName>.com/<SitePath>/<SiteName>/_vti_binSharePointListView={<ViewID>}SharePointListName={<ListID>}RootFolder=/<SitePath>/<SiteName>/Lists/<ListName>&IncludeVersions=TRUE"])
in
Source
    
    
  
  

if it's okay,then publish the .pbix file to service,and then configure the data source to see whether there's an error.

Best Regards,
Kelly

[rsborje1]

Hi @v-kelly-msft 

May I also get help for my web page data source? I have posted something similar on this page:

https://community.powerbi.com/t5/Service/DM-GWPipeline-Gateway-MashupDataAccessError-On-Premises-Dat...

Seeing that this post has been resolved by modifying the URL of the web source, it's possible that this can apply to my case as well ☹️

[Anonymous]

Hi @v-kelly-msft 

This has resolved the issue, this data source can now connect to the gateway successfully. Thanks very much for your help 🙂

Olivia