May 2021 Gateway Error: SharePoint: Request failed: The underlying connection was closed.

[Anonymous]

Some where around May 13th, all of our datasets that connect to SharePoint O365 started failing with the following message: Unable to connect to the data source undefined. (Details below). On May 16th we upgraded our gateway to the latest May 2021 gateway which was version 3000.82.2 (Maybe .4) and everything worked for a couple of days. Then on the 19th we noticed it was failing again. We noticed that the gateway download for May 2021 had a new release and was now at version 3000.82.6. So we downloaded and installed and it seemd to resolve the issue. However, we are still seeing it fail. This morning it failed, so then I was able to manually refresh and it worked, however, it just failed again for the scheduled refresh and a manual refresh is also not working. Ideas? Thoughts? What's going on? Not sure what to do. 

Underlying error code:	-2147467259
Underlying error message:	SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind:	SharePoint
Microsoft.Data.Mashup.ValueError.DataSourcePath:	https://sharepoint.com/oursite
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.Error
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	0b95ab8e-3780-4df9-9af1-7d2edd5cfd55
Request ID:	1464c48e-1da5-ed3e-d5a3-956abbbc3971
Time:	2021-05-21 21:42:35Z

[v-chuncz-msft]

@Anonymous 

This could be related to a query which imposes undue memory pressure. You may try Optimization guide for Power BI.

[wjs]

Hi ,

We have been seeing very similar issues since mid May in two seperate tenants. If we query SharePoint and MS SQL Server.

The issue is not related to query optimisation.

[wjs]

Hi,

SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send:

The following replicates the above error we are seeing both with SharePoint List and Excel Online:

The data is too small to be an optimisation issue.

I have set up a very simple pbix and connected it to a SharePoint List and to a SQL On Premises table.

SharePoint has 32 rows , SQL has 330 rows. 

I publish this pbix to our Premium Service and select our Gateway for the SQL and oAuth2 for SharePoint.

Scheculed Refrsh works - All Good.

If I take the same file and Merge SharePoint and SQL, everthing works as expected and refreshes in the desktop.

I publish this pbix to our Premium Service and again select the Gateway for SQL and oAuth2 for SharePoint. I also ensure Privacy Levels are the same : ( Both Organizational or Both Private or Both Public)

Scheduled Refresh FAILS each time.

SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send

If I wrap the SharePoint List with a Dataflow and update the merge to use SQL and the Dataflow instead, the Scheduled Refresh works.

All this happens for Excel Online aswell.

[Anonymous]

@v-chuncz-msft, these have been running fine for over a year without any issues. The volumn of data that we are pulling from Sharepoint is very small, 10-20 rows at best. As @wjs indicated, I can refresh without issue in desktop but have issues when publishing to the service. 

It just seems like this issue occurred once the last changes were pushed to the service. We initially started seeing this because our SQL connections were failing. And another thread/issue indicated that this was a known issue and was fixed, which is why a new gateway version was published. So updating to the latest gateway solved our SQL issue but now we are stuggling with Sharepoint. And as I indicated, these reports have been running for well over a year without any issues untill the last 2 weeks which I beleive coinsides with changes that were pushed to the service. 

Any chance we can get more info on the actual error: SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send

What does this actually mean? Was I able to make the connection to SharePoint and it failed or is this error implying it couldn't even connect with SharePoint.

Also, can anyone expand on what the FullDomainNameResolution is? As posted by Arthi Iyer on the updated post for the gateway, she states the following:

Note : We identified a bug in the May release which leads to the Gateway not being able to impersonate users for SSO (DirectQuery or Import) if FullDomainNameResolution is enabled. May gateway release 2  with version “3000.82.6” addresses this problem.

Is this something that I need to look at on the Power BI Side or the SharePoint side. Or is this something that I can't controll at all. 

Thoughts? Ideas? 

[Anonymous]

Not sure if this helps or not but out of the 3 reports that are still consistently failing, all of them can be manually refreshed each morning but then continue to fail on scheduled refreshes. When I say manual I mean clicking on the fresh icon on the dataset from Power BI Service. 

Is something different happening on a manual refresh vs a scheduled refresh? Are the connections not being closed? 

Also what does this error actually mean: SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send

[SELECTDBA]

We're seeing the exact same issue, starting around that same date.  

[md_colocoal]

We have the same issue. I reported the issue to Microsoft on 5/24 but they closed it stating there wasn't a problem. 

It's very hit or miss. Sometimes the Sharepoint datasources connect and sometimes they don't. 

[Anonymous]

I'm experiencing the same error that was previously not an issue. Went into the gateway to check the clusters and noticed an error on all SharePoint sources. "Unable to connect: This data source cannot connect to any gateway instances of the cluster. Please find more details below about specific errors for each gateway instance."

Updated and rebooted the gateway, did not solve.

[Anonymous]

@md_colocoal, I agree that it's hit or miss but I have more misses than hits at this point. At the moment, my scheduled refresh fails every time (2 times a day). When trying to manually refresh the dataset, it will fail any where from 1-10 times and then all of a sunden it will refresh. 

Was there a big O365 office/sharepoint update that happened about the same time as the Power BI update? Maybe the issue is on the SharePoint side??

[SELECTDBA]

From my understanding (with a Microsoft ticket I have open) its on the SharePoint side, but they haven't been able to give me specifics yet.  Just that they acknowledged there is an issue effecting multiple customers.