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

• feersum_endjinn

  SSCommitted

  Points: 1966

  More actions

  October 18, 2010 at 4:16 am

  #149028

  If you get the following error message when you try to connect to a SQL Server 2005 (maybe 2008?) database try the following fix.

  Error message:

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (.Net SqlClient Data Provider).

  I spent 1.5 days trying to fix this on one of our databases and ultimately found this post which helped me solve the issue. Your situation maybe different and I can say from first-hand experience that there are a hundred different solutions suggested on a hundred different SQL forums on the internet. However I can almost guarantee that if you used SelfSSL to generate a self-signed SSL certificate, either to use with SSRS report manager or for some other purpose, chances are the below fix will work for you. In addition to the above error message you may have also found that the SQLServerAgent service refuses to start. This is was a very misleading symptom since I spent 2 to 3 hours investigating the cause and trying out various fixes. But, as I said before, the below fix is what worked for me and the reason why I’m posting this fix is because I managed to find only a single post about this issue and hopefully more people will find this helpful. And someone should tell Microsoft to sort their IIS, SQLServer and SelfSSL incompatibility issues out! On a side note, if you are having problems with SSL and Report Manager you may want to read this MS KB article: http://support.microsoft.com/default.aspx?scid=kb;en-us;896861

  The gist of the problem is that when you create a self-signed SSL certificate using SelfSSL it not only adds this to your (i.e. current logged-in) user account certificate store it also adds it to the “Service account” as well as the “Computer account” certificate store. Then SQL Server (either by design or due to a bug) picks up this certificate and creates a copy for itself in the registry. It then (I think) decides to encrypt all incoming connections even if you have set the “Force Encryption” setting to “NO”.

  As a matter of course, take a backup of the server and the registry and data/log files before you make any of these changes!

  What finally worked for me was:

  1)Remove all SSL certificates and encryption options from IIS – both at the website level (for example from the “Default Web Site”) and at virtual directory level. Basically if you enabled encryption put all the settings back to their original non-encrypted settings.

  2)Remove all SSL self-signed certificates from the server. These are the certificates that SelfSSL generated for you. Your server may have genuine certificates – don’t touch these as they may be used by other software and anyway should be harmless.

  These certificates need to be removed from all certificates stores. To do this start MMC and (one at a time) add the Certificate Manager snap-in to “My user account”, “Service account” and “Computer account”. Locate every single copy of the self-signed certificates wherever they may be (Personal, Trusted Root Certificates Authorities, etc) and delete them.

  3)Now, although you have deleted the actual certificates, SQL Server still continues to use encryption. This is because it has created a copy of the certificate in the registry. You need to locate these and delete them.

  These are located in:

  HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Microsoft SQL

  Server\MSSQL.x\MSSQLServer\SuperSocketNetLib

  AND/OR

  HKEY_LOCAL_MACHINE\SOFTWARE\wow6432node\Microsoft\Microsoft SQL

  Server\MSSQL.x\Client\SuperSocketNetLib

  4)Restart the server and this should have fixed the initial error and you will find that SQL agent is running and things are back to normal.

  All this means is that DO NOT use SelfSSL with SQL Server 2005. At least this was my experience. Perhaps there were other contributing factors that I’m unware of but I find it hard to believe that this was all due to pure coincidence.