Unable to create a Data Source Type for SharePoint in a Gateway Cluster.

[Bhoga]

Hello everyone,

I have a Power BI Report which is extracting a portion of data from our On-premise MS SQL Database and a portion from SharePoint (specifically a Folder on SharePoint Online Site with about 15 Excel Files).

I tried to create a Data Source with Data Source Type = SharePoint in a Gateway Cluster. However, it is throwing an error as per the

screenshot below:

I have referred to many links and have also bumped into an idea link wherein Microsoft seems to have resolved this issue of pooling up data from on-prem (SQL Database in our case) and cloud (SharePoint in our case) data sources but this doesn't seem to work as yet.

As soon as I select Authentication Method = OAuth2, and Privacy Level setting for this data source = None or Private or Organizational or Public, the Add button is greying out or Disabled per the screenshot above.

However, when I tried to create a fresh new Gateway Cluster, everything just works fine.

Requirement is to create a this new SharePoint Gateway under the existing Gateway Clusters.

FYI - Gateway is updated from September to the latest (October 2020) as of yesterday.

Can anyone please help me with what I'm doing wrong or is there any settings I need to check/change on the configured On-Premise Gateway or on the SharePoint Site?

Your time and help is greatly appreciated.

Thanks, C

Below is the complete Error Message if that helps:

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:	3xxxxxxxxxxxxxxxxxxxxxxxxxxc5e
Request ID:	4xxxxxxxxxxxxxxxxxxxxxxxxxxxxx0
Cluster URI:	https://wabi-australia-southeast-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Wed Oct 28 2020 12:58:14 GMT+1100 (Australian Eastern Daylight Time)
Service version:	13.0.14553.65
Client version:	2010.2.03456-train MWC PowerBI Gateway: Unable to connect to the data source undefined. Underlying error code: -2147467259 Underlying error message: SharePoint: Request failed: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.ValueError.DataSourceKind: SharePoint Microsoft.Data.Mashup.ValueError.DataSourcePath: https://domain.sharepoint.com/sites/Pbidata Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error Troubleshoot connection problems

[mahimabedi]

Can you applied the setting "Allow user's cloud data sources to refresh through this gateway cluster " ? Its mentioned as a prerequiste below.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud#prer...

PS: Please mark this post as a "solution"  if this helps solving the issue and/or hit the "thumbs-up" button if you found this insighful. Thanks.

[Bhoga]

@mahimabediThanks for your reply. Yes, I've ensured those 3 options there are all checked/ticked. Still got that error message.

Anymore takes.. anyone..!

Thanks in advance.