“… an error occurred during the pre-login handshake.” & “[DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error” when connecting to SQL Server.
This blog is regarding one of most commonly faced issues that you may receive when connecting to the SQL Server. Mostly you may run into this issue after some improper Windows security update (say KB2655992 in my case) or improper application of Poodle security fix.
ISSUE DESCRIPTION FROM SQL CONNECTIVITY STANDPOINT:
When we try to connect to the SQL Server instance using the SQL Server Management Studio, it may fail with the following error message,
Error:
TITLE: Connect to Server
------------------------------
Cannot connect to <mySQLServer>.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Using a UDL file, we saw the following error message,
---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
---------------------------
OK
---------------------------
Note: Forcing the connection to use any other protocol like TCP, Named Pipes & Shared Memory also throws the same error message. The Dedicated administrator connection (DAC) was also throwing a “Login timeout expired.” error.
OUR FINDINGS & INFERENCE:
There are numerous reasons on why you may witness these error messages. But usually, if you see these 2 specific error messages from SQL Server Management Studio & UDL file. Then it is worth checking the below settings.
As per the error message received, “… an error occurred during the pre-login handshake .” and “ [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.” means that the client application was able to complete the TCP 3-way handshake properly (hence you notice “A connection was successfully established with the server”), but during the pre-login handshake, the client application checks with the SQL Server on the TDS protocol version to be used henceforth for the communication, the login passed by the client application (Windows authenticated login or SQL Authenticated Login), whether there is any client-side or SQL Server connection encryption using SSL certificates or TLS etc. If the SQL Server doesn’t respond to this request from the client in a timely fashion or fails to respond due to any internal machine-level issues, we end-up at this particular error message. (wiz. “… an error occurred during the pre-login handshake” )
These error messages are thrown from the actual SQL Server drivers / providers that are used to establish the connection to the SQL server. E.g (OLE DB provider for SQL Server / SQL Server Native Client etc.). So different SQL Drivers/ Providers throws different error messages for the same issue. When we tried the connection from the UDL file, we see a different error message for this reason, but this error message was more straightforward.
Error:
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.
Hence, we directly jumped to the SCHANNEL registry hive to check the values. Wiz.
Target hive:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL
CAUSE:
By default, you may not find the below registry keys, which is completely fine. But in my case, when we checked the values for these registry keys on the target SQL Server, the following were the values.
i) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server
DisabledByDefault was set to 1
Enabled was set to 0
ii) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server
DisabledByDefault was set to 1
Enabled was set to 0
iii) HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0 \Server
DisabledByDefault was set to 0
Enabled was set to 0
Based on these values, we figured-out that none of the security provider protocols were enabled. We confirmed that all SSL 2.0, SSL 3.0 & TLS 1.0 were disabled and this is not an ideal scenario.
RESOLUTION:
We enabled the TLS 1.0 protocol by setting the following value.
Under,
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0 \Server
Set Enabled to 1
Rebooted the SQL Server machine for the changes to reflect.
After the successful reboot, we were able to connect to the SQL Server locally without any issues. Also tested the connection from a remote server machine and confirmed that the connections to the SQL Server worked as expected without any issues. SQL server was just a victim here like any other application that uses windows security providers and since all the security provider’s protocols were disabled, the SQL Server wasn’t able to accept any new connection request.
Please drop in your comments or connect with Microsoft BI-ONE CSS team if you are still encountering the same issue even after performing the above steps.
Happy troubleshooting!!!!
Author: Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft
Reviewed by: Sunil Kumar B.S, Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft.
Comments
Anonymous
November 25, 2015
Hello I have performed all the steps and still receive the error, i´m using Microsoft Data Link, any help would be appreciated. thanks.Anonymous
February 04, 2016
Hi Claudio, Can you post the exact error message and text content of the backup of the registry key HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL on the failing server.Anonymous
June 07, 2016
Grateful for this post. I have enabled TLS 1.0 and the problem has been resolved. We found that we have disabled SSL & TLS because of some "hardening" measures.- Anonymous
November 28, 2017
You are welcome! Happy to help
- Anonymous
Anonymous
June 29, 2016
PCI DSS Rules now forbid the use of TLS 1.0http://blog.pcisecuritystandards.org/migrating-from-ssl-and-early-tls "Consistent with the existing language in PCI DSS v3.1, all new implementations must be enabled with TLS 1.1 or greater. TLS 1.2 is recommended."Are you suggesting that ODBC cannot be made to work at all if TLS 1.0 is disabled? If so, no organization that uses ODBC will be able to pass a PCI Scan and we all have a major problem on our hands.- Anonymous
September 15, 2016
I'd be also interested to know the position of MS on supporting TLS 1.2 in light of requirements coming from PCI compliance
- Anonymous
Anonymous
June 01, 2017
That's work, thank you.Anonymous
August 02, 2017
Hi there, thanks very much. I had this exact behaviour when configuring a log shipping. the secondary server was unable to copy (and eventually restore) the transaction logs. Oddly enough, he was able to setup up the first database synch. After updating the registry and rebooting the secondary server it worked like a charm. Thanks again.- Anonymous
November 28, 2017
Thanks. Happy to Help!
- Anonymous
Anonymous
October 09, 2017
re: PCI we have excatly the same issues but from multiple vectors. App written in .NET versions that cannot use TLS1.2 or need to have the 'switch' turned on to use and favour it. So many systems need to be de-scoped because of this, with alternatives found. The environment i'm in at the moment is using the other conflicting setting of FFFFFFFF for enabled. MS themselves give BOTH as an example to use so i'm wondering if it's any non 0 value...If people are still receiving errors after re-enabling protocols they should check thier networking and ensure that they haven't got a device which is blocking ciphers, protocols etc. with a security profile. If in an LB environment check that it's allowed across the device, ideally internally only, thouhg I don't know your route to get to said device in teh 1st place. Also, for anyone unsure re: registry you may need to create keys under each category. there is 1 each for both Client and Server. So for the Server offering TLS1.2 the path would need to be: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL\Protocols\TLS 1.2\ServerAt this point you create a Dword for "Enabled" with a value of 1. You can also specifiy an additional Dword named "DisabledByDefault" with a value of 0. To switch off simply reverse the values. Security compliance , especially re: PCI can be a minefiled with legacy systems due to their inherant inability to use stronger encryption methods.- Anonymous
October 13, 2017
Additionally, update KB4019213 was found to deprecate security methods that some of our old systems were relying on. Have a look here: https://support.microsoft.com/help/4010323 in case you encounter any problems in a similar scenario.
- Anonymous
Anonymous
November 26, 2017
I use Mac, and I use docker to install sql server 2017, and use sql operations studio to connect sql server on docker, how can I do to resolve this issue?- Anonymous
November 28, 2017
Are you able to connect to the SQL Server outside your Docker. Please try to narrow this down to client-side (or) SQL Server side issue.
- Anonymous
Anonymous
March 01, 2018
Hi, I'm seeing this error (years later i know, we're still using windows server 2012 r2)But here's the thing. I have 2 servers Load Balanced. One of them works fine the other throws this error once every minute (not all the time).If i use the UDL i can't replicate the issue at all, i press test connection one after the other.The application that we use is .net and i have all tls's and all ssl's disabled.Any ideea? I checked with network team they said connection was fine with tcp fin.I checked with FireWall Team same.I checked the application on both servers. It's the same. (I copy pasted the app from good server to bad server).I checked app pool rights on the folder. It's good.I'm lost. At this moment i don't know who to ask for help.