locked
Error occurred during the pre-login handshake, due to AntiVirus ? RRS feed

  • Question

  • Hi SQL Experts,
    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 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. Image Below

    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 to SQL Experts:
    • Did you ever come like this scenario? If so What did you do to resolve it?
    • Is this really the Antivirus issue? If so then why it's not happening all the times?
    Please provide your insights/recommendation on this.



    Saturday, August 3, 2019 3:27 PM

Answers

  • 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://docs.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

    Note: Without the traces it’s hard to say this can be a problem but nevertheless it will increase the connection performance so it’s worth trying.

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

    Note: As above, without the traces it’s hard to say this can be a problem but nevertheless it will increase the connection performance so it’s worth trying.

    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 ARUMUGAM

    Monday, November 18, 2019 9:32 AM

All replies