30

Today I could not log into my local SQL Server 2012 instance with the following error message

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

provider: SSL Provider, error: 0 - An internal error occurred. (Microsoft SQL Server, Error: -2146893792)

I found many similar questions here and on the forums, but nothing helped. Please notice that in my case it just says

0 - An internal error occurred

  • There are no errors in Event Viewer;
  • I don't use Encrypted Connections;
  • In SQL Server Configuration Manager, Force Protocol Entryption is set to False, Trust Server Certificate is set to Yes (Originally it was No, but in both cases it didn't work);
  • I CAN connect to SQL Server using UDL;
  • I tried to re-install SQL Server with all related components;
  • Tried to uninstall .NET 4.5;
  • In SQL Management Studio, in connection properties, Encrypt Connection is unchecked.

It worked fine yesterday, I have not installed any software since then.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
user2816785
  • 301
  • 1
  • 3
  • 3
  • Please see if this helps: http://serverfault.com/questions/423013/sql-an-error-occurred-during-the-pre-login-handshake – Manikandan Sigamani Sep 25 '13 at 20:17
  • No, it does not help. There is a meaningful exception detail in this question - provider: SSL Provider, error: 0 - The wait operation timed out. Anyway, thank you! – user2816785 Sep 25 '13 at 20:22
  • Are you using NT Security or SQL server security? – Thomas Kejser Jan 04 '14 at 19:08
  • Try looking at the ring buffers for more info - http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx – Quantum Elf Feb 16 '14 at 22:20
  • 1
    If you can connect via UDL, are you able to query any of the tables? In particular, does SELECT @@SERVERNAME return the correct server name? – Rocksalt Feb 22 '14 at 00:06
  • Access to port 1433 ? Did you configured the Windows Firewall to Allow SQL Server Access. [See this](http://technet.microsoft.com/en-us/library/ms175043.aspx) – Lin Apr 02 '14 at 06:09
  • possible duplicate of [A connection was successfully established with the server, but then an error occurred during the pre-login handshake](http://stackoverflow.com/questions/3270199/a-connection-was-successfully-established-with-the-server-but-then-an-error-occ) – abatishchev Aug 23 '14 at 20:26
  • I am getting the same thing, but it is not consistent. Is that what you experienced? – H20rider Oct 11 '16 at 13:06
  • We see this error very rarely and intermittently with powershell scripts and it works the next time we run the script – Action Dan Jun 20 '19 at 21:33

7 Answers7

6

check that TCP/IP protocol is not enabled for clients. Without TCP/IP protocol you can not access the sql server from other clients. Following is the method to enable the protocol.

Go to the: Start-->Programs-->SQL Server--> Configuration Tools-->SQL Configuration Manager.

Expand the SQL Network Configuration and click on the PROTOCOLS node

Right click on TCP/IP and open up the PROPERTIES panel

Select the IP ADDRESS tab

http://sqlanddotnetdevelopment.blogspot.in/2013/07/a-connection-was-successfully.html

BAdmin
  • 927
  • 1
  • 11
  • 19
jignesh
  • 1,639
  • 1
  • 16
  • 18
5

I got this error connecting to localhost with the newest version of Microsoft.Data.SqlClient.

Turns out that Microsoft really wants you to encrypt your database, for security.

This can be fixed client side by changing the connection string, by adding an Encrypt=False parameter, eg:

SERVER=.\SQLEXPRESS;DATABASE=MyDatabase;Integrated Security=True;Encrypt=False;

See Rick strah's blog post on the subject

Loris
  • 1,940
  • 3
  • 19
  • 27
3

In our case it was the problem with the SSL/TLS. We just disabled tls 1.0 to make sure we were compliant with PCI DSS but it stopped SQL Server. Then we had to install one update from Microsoft and then did the disable thing using IISCrypto instead of using the registry. Here's the steps we followed: https://www.itechtics.com/connection-successfully-established-error-occured-pre-login-handshake/

Usman
  • 61
  • 2
1

In our case same error occurred due to spn not set.Try checking if spn is set. As others suggested in comments you can see ring buffer entries which holds more info .if you are able to repro more often try running netmon or wireshark in parallel

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

None of this stuff worked for me.. in the end I had to install the cumulative update for SQL Server 2014 SP1

SQL Server Cumulative Updates

Tim Davis
  • 524
  • 6
  • 17
0

In my case the error was fixed by adding "Encrypt=False" like this:

    Server=localhost\\SQLEXPRESS;Database=*YOUR_DATABASE*;Trusted_Connection=True;Encrypt=False
Santiago Zaidan
  • 1
  • 1
-2

Disable TLS 1.2 protocol and enabled 1.0 /1.1 . Re-installed SQL server post restarting the server

saran
  • 57
  • 4
  • 2
    Sorry for the downvote, but nobody should leave TLS 1.0 enabled anymore. It's vulnerable with a capital "V." – chris Apr 30 '21 at 01:26