The underlying connection was closed: An unexpected error occurred on a send(Sharepoint data Only)

[shekhar_shres]

Hi all,

Thank you in advance for taking the time to read this post.

I have built some dashboards where the data comes from Sharepoint as well as our SQL server. So prior to 9/12/2022 everything was working perfectly. But later, I started to get "The underlying connection was closed: An unexpected error occurred on a send." in all the data that is coming from Sharepoint. 

First I thought it was microsoft issue, but then I realised this issue following.

1. Home WIFI - The error only occurs when I am working from home with my  home wifi.
2. Home Lan/Office Wifi/Office Lan - The error doesn't occur when I use Lan at home or Office Wifi or Office Lan.
3. Mobile Hotspot - Surprisingly, the error doesn't occur when I connect my WIFI by releasing hotspot from my mobile phone. 

I thought it was an internet issue, but my download speed is 55 mbps and upload speed is 18 mbps (home wifi).
Home Wifi/Lan download speed - 54 mbps
Home Wifi/Lan Upload speed - 17 mbps
Home Wifi/Lan Ping - 18

Home Wifi/Lan Download Latency - 23
Home Wifi/Lan Upload Latency - 24
Mobili Hotspot download speed - 54 mbps

Mobile Hotspot upload speed - 21 mbps
Mobile Hotspot Ping - 24
Download Latency - 215
Mobitl Hotspot upload latency 177

Clearly speed is not an issue as my home wifi/lan have metrics similar. 

I contacted my internet provider and they've provided me with new and better router/modem but it is not working. 

Perhaps some setting on my Laptop that relates to my home wifi. 

Note : The issue is only when I refresh query in Power BI desktop or the query editor window, but it refreshes all well in Power BI online.

Has anyone experienced a situation like this before?

[shekhar_shres · Accepted solution]

Hi everyone, I raised the ticket with Microsoft and finally found the solution this problem.

Follow the following steps.

1. Go to Control Panel > Network and Internet > Network and Sharing Center > Change adapter settings 
2. Double Click on Wifi 

3. In General Tab, click properties.
4. Uncheck Internet Protocol Version 6 (TCP/IPv6)

Click "OK"

Hope this solves your problem.

[AhmedArabi]

can I do the same steps with ethernet ? 

[kennedy311]

This solved my problem but holy crap...why is changing my WiFi settings the solution to this problem???

[shekhar_shres]

Yeah! It is weird, isn't it 

[Arie]

This is also working for me, thanks a lot!

[scook002]

Same error.

I source data mainly from sharepoint. 

Dataset refreshed finer before.

So is this a connectivity issue/bug ? or can it be cuased by something I define on my side ie Power Query ?

[KyleWebb]

I was able to resolve this issue by replacing my home internet router. I previously was using the Verizon FIOS provided router. I changed firewall settings, TLS, Packet sizes, everything mentioned in any thread about this issue. I was also noticing other issues with Microsoft products on that same router.  
Examples: In MS Teams, someone shares a file location , click the link and immedtiately get an error in the browser. Refresh it, nothing. Refresh it again, and it loads perfectly.  
Also, just to be sure it wasn't a PowerBI Specific error, I was getting the same "failure on a send" message when using PowerQuery in Excel to reach an ODBC connection.  

With FIOS ( my ISP), the actual modem is in a permenant terminal attached to the house, so it cannot be changed. The home router can be changed.  I changed it to a stanrd, commercialyl available mesh router system and all of the errors immediatetly were resolved. 

My only advice is is that if you are experiencing this error on a particular connection, and its happens to be a FIOS provided router, the only current answer seems to be changing the hardware.

[Jason_C]

Hi Kyle,

unfortunately Microsoft couldnt resolve for me. They wanted to run a SARA on my laptop (to record the error live) however, the SARA also errored! I gave up in the end. I now set up queries to my c drive and create file path and name queries so that any user can change to their username just once and then refresh all queries (if you search for dynamic file path in power query you should find some videos)

[KyleWebb]

I am having the same issue. I get this "The underlying connection was closed: An unexpected error occurred on a send".

It only happens on my home wifi. My office wifi and hardwired work, as well as my wifi hotspot on my phone. Home internet only I get this error.  Did you ever get any guidance on what could cause this?

[Jason_C]

Thanks Kyle.

I think Microsoft has a responsibility to be aware of this problem so that it can quickly advise users who run into the same issue and so that it can work on making its software functional with all routers.

[shekhar_shres]

Luckily, it was due to some Client issues when connecting to VPN. We moved from VPN to cloudfare and the problem fixed.

I read couple of blogs, try uninstalling and re-installying VPN, if it doesn't work do some digging on Transport Layer Security.  

Cheers

[Jason_C]

Hi Shekhar,

I am having a similar experience. I use power query mainly in excel but I'm seeing the same behaviour in PowerBi.

The problem started c. Feb 6.

Refreshing using my mobile phone hotspot works and refreshing from external locations, cafes etc works. At home, when I start my laptop and connect to my home internet I get the same error as you re connections to SharePoint files.  If I temporarily switch to my mobile phone hotspot and then switch back to my home internet then the queries refresh without error until I shut down my laptop and then start again.

I have a colleague with the same internet provider as me with and she experiences the same behaviours and other colleagues with different providers don't have any refresh problems! However, I have a colleague in a different country who does experiences the errors. I wonder is there some setting in Sharepoint that has changed and causes a conflict with certain internet connections/providers?

[Anonymous]

I am also experiencing this same error trying to refresh locally with Sharepoint input, just recently started

[pratyashasamal]

Hi @shekhar_shres ,

Check transport layer security (TLS) 1.0 and 1.1 for Power BI. Upgrade the TLS version of your Power BI application to TLS 1.2 before then. 

Solution: Adding followung code  

System.Net.ServicePointManager .SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; 

Follow below post suggested by microsoft support team:

https://azure.microsoft.com/en-in/updates/power-bi-support-for-transportlayer-security/

Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[shekhar_shres]

Hi pratyashasamal, 

Thank you for your response. Where do I add the follwoing code 

System.Net.ServicePointManager .SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;  

The link provided does not direct me as to where to add the code. 


Also, I'm already in TLS 1.2