A Connection Was Successfully Established, But Then an Error Occurred During the Pre-login Handshake in SQL Server

A Connection Was Successfully Established But Then An Error Occurred During The Pre Login HandshakeA Connection Was Successfully Established But Then An Error Occurred During The Pre Login Handshake

One of our web servers also hosts the databases running Microsoft SQL Server. There was a requirement from PCI DSS that TLS 1.0, SSL 3.0 and SSL 2.0 should be disabled for security reasons. This is a problem for Microsoft products as most of them use either SSL 3.0 or TLS 1.0. For example, Remote Desktop Connection uses TLS 1.0 by default. If TLS 1.0 is disabled, you will not be able to login using RDP.

Another example is of MS SQL Server. SQL Server also uses TLS 1.0 by default. If it is disabled, SQL Server services will refuse to start. In this article, we will discuss how to keep SQL Server running after disabling TLS 1.0. We will discuss RDP solution in another article.

The error message

First of all, let’s see what the error message looks like:

SQL Connection Error

Cannot connect to the server.
Additional information:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

The reason why we want to move away from SSL 3.0 or TLS 1.0 is that they are not considered secure in the modern internet world. Most modern browsers move beyond these legacy protocols to the more secure TLS 1.1 and TLS 1.2. The PCI card industry has also abandoned TLS 1.0 meaning that the sites that accept credit cards will not be compliant with PCI DSS if they are using TLS 1.0 or lower.

There are two ways to enable/disable any version of SSL/TLS.

Using Windows Registry

This guide from Microsoft gives a detailed overview of disabling cryptography algorithms in Windows. We will take a simple step by step approach to disable TLS 1.0.

  1. Go to Run –> regedit
  2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\
    SecurityProviders\SCHANNEL\Protocols
  3. Create a new sub-folder (if not already created) TLS 1.0. Under TLS 1.0, create two sub-folders, Server and Client.
  4. Under Server folder, create a key DWORD (32-bit) with the name Enabled and contents as 0x00000000 (0).
  5. Restart the computer and check which ciphers are enabled using foundeo weak ssl ciphers or Qualys SSL Labs.

Using a tool IISCrypto

Another simpler way to enable or disable any ciphers is to use a portable tool called IISCrypto.

[ddownload id=”19828″ style=”link” text=”Download IISCrypto”]

Using IISCrypto is safer than making changes in the Registry as you only have to check or uncheck to enable or disable protocols.

IISCrypto to enable or disable SSL TLS protocols

Although just unchecking the checkbox of TLS 1.0 should have immediate effect on the server, it is always recommended to restart.

The SQL Error

Now let’s come to the error which we were getting after disabling TLS 1.0. SQL Server will give the following error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: – No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

When you encounter this error, you have to do two things for the complete solution:

Install cumulative update for SQL Server

You may go here and download and install the required update for your SQL Server. Please note that in these updates, Microsoft only supports SQL Server 2008 or later to enable TLS 1.2. Once you have installed the cumulative update, you will be able to connect to the SQL Server using the management studio. If you are unable to connect with the SQL Server Management Studio, you may go to the next step.

Install .NET Framework 4.6

For connecting SQL Server Management Studio without any problems, you will need .NET Framework 4.6 to be installed on the server. Just download .NET Framework 4.6, install it on the server, restart and SQL Server should run without any problems.

You may also need to update the SNAC/ODBC drivers on all client systems that are connecting to the server to successfully negotiate using TLS 1.2 instead of the older 1.0 version.

If you liked this post, Share it on:
Usman Khurshid is a seasoned IT Pro with over 15 years of experience in the IT industry. He has experience in everything from IT support, helpdesk, sysadmin, network admin, and cloud computing. He is also certified in Microsoft Technologies (MCTS and MCSA) and also Cisco Certified Professional in Routing and Switching.

2 comments

  • Fernando Cabal
    Fernando Cabal

    Enabling TLS 1.0 goes against security efforts, try this:
    This problem can be related to a firewall in the middle that is doing SSL inspection.

    I Suggest you either try again using another connection not doing SSL inspection, or ask your firewall admin to create an exemption for the source and/or destination you are connecting to,

    Cheers!

  • HD

    Thank you so very much, enabling TLS 1.0 helped solved this sql error !!

    Thanks again !!

Get Updates in Your Inbox

Sign up for the regular updates and be the first to know about the latest tech information

Talk to us now

Talk to us straight and get your questions answered right away

Tell Us About Your Project
Web Dev Service Contact Form (Popup)