On-premise gateway cannot connect to excel file on sharepoint

[MarcVndrhgn]

Hi,

I have been asked to add a datasource on our gateway for an excel file on a sharepoint server.

The url is like this :

http://teams.xxxxxxxx.be/sites/aaaaaaa/bbbbbb/cccccc/dddd/eeeeee/fffffffff.xlsx

On-premise gateway cannot connect to sharepoint (excel file), in PowerBI Desktop it works.

When I try to create the connection I get the error :

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."Hide details

Activity ID: 1e326995-7262-49b8-a6c6-250067496a32
Request ID: 62c7b0b4-c87c-1d7c-2f90-8111b63c20a1
Cluster URI: https://wabi-europe-north-b-redirect.analysis.windows.net/
Status code: 400
Error Code: DMTS_PublishDatasourceToClusterErrorCode
Time: Wed Sep 30 2020 08:02:42 GMT+0200 (Central European Summer Time)
Service version: 13.0.14342.77
Client version: 2009.2.02969-train
PowerCluster_DEV: Unable to connect to the data source undefined.
Underlying error code: -2147467259
Underlying error message: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found) OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found) OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: SharePoint
Microsoft.Data.Mashup.ValueError.DataSourcePath: http://teams.xxxxxxxx.be/sites/aaaaaaa/bbbbbb/cccccc/dddd/e/fffffffff.xlsx
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Microsoft.Data.Mashup.ValueError.SPRequestGuid: 7efc7e9f-b7a9-e0a6-24b2-16af7278de09, 7efc7e9f-a7ac-e0a6-24b2-15841ddca2c6, 7efc7e9f-97ae-e0a6-24b2-14ded924a321

We have multiple gateway, one with v2000.37.35 (2020-04-29) and one with 3000.31.3 (2020-03-01) version, and both show the same error.

I tried to make the connection to upper folder and I was able to go till there : 
http://teams.xxxxxxxx.be/sites/aaaaaaa/bbbbbb/cccccc/

Our sharepoint admins have assigned the following rights and told me it should be ok
http://teams.xxxxxxxx.be/sites/aaaaaaa/bbbbbb/cccccc/dddd/eeeeee/ in Contribute

Any idea how I can solve this ?

[v-yingjl · Accepted solution]

Hi @MarcVndrhgn ,

You can try to use the Web data source type to connect to the file on SharePoint, if it still not works, power bi service could not support configure the gateway to connect to the Sharepoint file.

Essentially, if you want to refresh Sharepoint file in power bi service, you need not a gateway becasure sharepoint is a could service which can be refreshed automatically in power bi service without gateways.

Best Regards,
Yingjie Li

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

[MarcVndrhgn]

Hello @v-yingjl 

Thank you very much for your answer, we tried the data source as Web and it worked (I completely missed that possibility).

Best regards

Marc

[amitchandak]

@MarcVndrhgn , is your Gateway and SharePoint logins are same ?

OR

Refer to the old solution: https://community.powerbi.com/t5/Service/We-could-not-register-this-data-source-for-any-gateway-inst...

[MarcVndrhgn]

Hello,

No they are not the same, it is not a personal gateway, I have received credentials to access the file on sharepoint and I use these credentials in the connection data source.

I have already set up other connections to sharepoint (with success) but it is the first time the data source points to a file directly.