Unable to configure an Excel data source from Enterprise Gateway

[PatrickB]

Hi,

I am trying to set up a connection to my Enterprise Gateway for one of my datasets. The dataset includes 3 data sources, one SSAS connection, one SQL Server connection and one Excel connection to a Excel file. Now I am having problems setting up the data source for the Enterprise Gateway to the Excel file. The file is located on OneDrive for Business and when I created the dataset in PBI Desktop there is no problem connecting to it. But when trying to connect from the PBI service I get the following error message,

Aktivitets-ID:	00b39063-3a24-d49b-fe83-8d880e90bfdc
Begär ID:	be16275e-987b-61f6-e259-e0a573ca806d
Kluster-URI:	https://wabi-south-east-asia-redirect.analysis.windows.net
Statuskod:	400
Felkod:	DM_GWPipeline_Gateway_InvalidConnectionCredentials
Tid:	Tue Aug 22 2017 14:21:57 GMT+0200 (W. Europe Daylight Time)
Version:	13.0.2136.181

Underliggande felkod:	-2147467259
Underliggande felkodsmeddelande:	The credentials provided for the Web source are invalid. (Source at https://pernodricard-my.sharepoint.com/personal/patrick_baumann_pernod-ricard_com/Documents/File.xls....)
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:	Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:	https://pernodricard-my.sharepoint.com/personal/patrick_baumann_pernod-ricard_com/Documents/File.xls...
Microsoft.Data.Mashup.CredentialError.Reason:	AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources:	[{"kind":"Web","path":"https://pernodricard-my.sharepoint.com/personal/patrick_baumann_pernod-ricard_com/Documents/File.xls..."}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:	AccessForbidden

I have got it to work by setting up a personal gateway on my local machine but since this solution is going to be used by multiple other users I want to use the Enterprise Gateway instead.

I have used the Web data connector for the Excel file. 

Rgs,

//Patrick

[v-haibl-msft · Accepted solution]

@PatrickB

Currently, on-premise data gateway doesn't support OAuth2 based authentication. We are not able to add the online data sources in data gateway as well as on-premise data sources, so that we can't configure this kind of combined dataset to use data gateway. The workaround is to use personal gateway.

PG have started working on the data gateway to fix this issue. See: Combine enterprise gateway and online data sources. Their current ETA is end of August, so we just need to wait for the new version of on-premises data gateway.

Best Regards,
Herbert

[v-haibl-msft · Accepted solution]

@PatrickB

Currently, on-premise data gateway doesn't support OAuth2 based authentication. We are not able to add the online data sources in data gateway as well as on-premise data sources, so that we can't configure this kind of combined dataset to use data gateway. The workaround is to use personal gateway.

PG have started working on the data gateway to fix this issue. See: Combine enterprise gateway and online data sources. Their current ETA is end of August, so we just need to wait for the new version of on-premises data gateway.

Best Regards,
Herbert

[PatrickB · Accepted solution]

Hi Herbert,

Thanks for you swift reply! Ok, then I know and will use the personal gateway or put the file on a network disk instead. But it feels important that the functionality to combine on-prem and online data since many of my customers are moving there files over to OneDrive.

Rgs,

//Patrick