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.☹️
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.
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.
Then create the following DWORD registry values under the key
If there is no such entry called AuthRoot/ EnableDisallowedCertAutoUpdate /DisableRootAutoUpdate then please create it
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.