1

I need your expertise on one of my issues. I often get an intermittent issue from our Power BI on-premises Gateway to SQL connectivity

Error from gateway log

Error: 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.)

The difficult part here is it's very difficult to reproduce ☹️ Whenever I tried the connectivity from the gateway to SQL server, it succeeds but at some very rare case, it fails.

Steps we did to find the root cause

  • Checked in both the gateway server and SQL server TLS 1.2 only is enabled, other versions of TLS are disabled
  • created a .udl file and tried the connectivity but got the error like [DBNETLIB] ConnectionOpen( SECCreateCredentials().] SSL Security error.

Finally, we contacted our internal support team, they told to run the network tracer. So we did. After some long times, we had the luck to capture the error in the network tracer. (Below Image)

enter image description here

Support team told like:

We see that 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. We see the same pattern, where the client is causing delay on multiple instances of the communication. And such delay is generally caused by the Antivirus

My question:

  • Is this really the Antivirus issue? If so then why it's not happening all the times?

P.S I know this question is already asked in SO and possible for duplicate, but my real question is this antivirus would be a possible cause for this?

Jayendran
  • 9,638
  • 8
  • 60
  • 103

1 Answers1

0

The issue is finally resolved after so many attempts. The below is the solution worked for us

• 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.

https://learn.microsoft.com/en-us/azure/active-directory/device-management-troubleshoot-hybrid-join-windows-current

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

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

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

https://blogs.technet.microsoft.com/askds/2018/04/10/tls-handshake-errors-and-connection-timeouts-maybe-its-the-ctl-engine/

To disable it:
• Create a backup of this registry key (export and save a copy)
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\SystemCertificates\AuthRoot
• Then create the following DWORD registry values under the key
“EnableDisallowedCertAutoUpdate”=dword:00000000 “DisableRootAutoUpdate”=dword:00000001

I hope this helps someone in the future !

Jayendran
  • 9,638
  • 8
  • 60
  • 103