PowerBI intermittent Refresh Failure - Unable to connect to the data source undefined

[Jayendran]

There are many reasons for fixing the "Unable to connect to the data source undefined". Even in the community, there is an open thread that speaks about it. In this article, I'm going to show one of the most common reasons for this and the step-by-step fix to solve it.

My History 

Some time back in 2019, I faced a refresh issue with the message saying “Unable to connect to the data source undefined". There is no proper detailed error description. The most frustrating 😧 part of this issue is that it is so intermittent. Sometimes it happens and sometimes it doesn’t. Whenever I tried to reproduce the issue, I’m not able to reproduce it and it goes to successful the next run, like in the below image: 

Troubleshooting with Gateway Logs

As the Refresh message in the Power BI portal is not very useful, usually the next step is to look into the gateway logs. Once I looked into the gateway, I got some detailed error messages like below:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.

This error at least narrows down that there is some connectivity issue between my gateway server and the SQL server. But how to resolve this and how to make sure my solution is the correct fix, since it's an intermittent issue?

Root Cause :

For this kind of connectivity issue WRT pre-login handshake the main cause for us is this:

"Client (gateway server) is sending Client hello after 14 seconds for the TLS SSL handshake, this delay is causing the connection to fail as connection needs to get established in 15 seconds 

And such delay is generally caused by the some application e.g, some Antivirus installed on your computer that is using deprecated network protocols to intercept network traffic."

However, with this we couldn't simply un-install the Antivirus which would put our server in big trouble of security attackers. ☹️

Solution:

After a huge struggle and posting the query in Stack Overflow and MSDN nothing really helps. Finally, one solution that worked for us is to make some registry-level changes in the gateway server. Let’s see that in below steps.

Note: As a best practice before making any changes to the registry level, make sure to Create a backup of this registry key (export and save a copy).

Step 1:

Azure AD Join:

Azure AD join, where the connections head to the “login.microsoft.com” and delay the connections. There are few settings from registry and GPO that needs to be performed to disable this Auto Azure Workplace join.

Reference: https://docs.microsoft.com/en-us/azure/active-directory/device-management-troubleshoot-hybrid-join-w...

It talks about restricting the server from joining Azure AD through a GPO, which resolves to:

HKLM\SOFTWARE\Policies\Microsoft\Windows\WorkplaceJoin\  
key: autoWorkplaceJoin = 0

 If there is no such entry called WorkplaceJoin / autoWorkplaceJoin then please create it

Step 2:

Connections to http://ctldl.windowsupdate.com :

Connections headed to http://ctldl.windowsupdate.com , refer the below article that talks about this issue. 

 
Reference: https://blogs.technet.microsoft.com/askds/2018/04/10/tls-handshake-errors-and-connection-timeouts-ma...
 
To disable it:

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\SystemCertificates\AuthRoot
 
Then create the following DWORD registry values under the key
 
“EnableDisallowedCertAutoUpdate”=dword:00000000
“DisableRootAutoUpdate”=dword:00000001

  If there is no such entry called AuthRoot/ EnableDisallowedCertAutoUpdate /DisableRootAutoUpdate then please create it

Conclusion:

We continuously monitored after deployed the fix in the gateway server for more than 6 months, we have never faced the issue again, which makes us to feel a good breath after a long time. As I wrote this entire article with my experience of handling such refresh failures, there may/may not be the case of others, but it's worth to try the solution whenever you got the same behaviour as mine.