Getting an "error occured during the pre-login handshake". in SQL Server?
I just spent the last few hours trying to figure out why I can't connect to my local SQL 2005 instance and getting the error above. I was using SQL Management Studio.
I tried tweaking Network/Client Configuration, Protocols using SQL ConfigurationManager, Surface Area Configuration and the error messages changed from one to another but still no luck. Some claim that this issue was solved by changes to protocols but it wasn't my case. [more]
I looked for answers in the net and didn't quite get what was wrong.
But this first link helped (common SQL protocol issues/errors)
http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
And then I ended up with these post and the symptoms matched my issue (especially when I connected using MDAC – Admin > ODBC Sources)
http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx
As soon as I saw the 2nd link above, I knew I'm close plus the fact that I've been working on creating and assigning SSL certifcate for my local IIS using SelfSSL (comes with IIS Resource Kit). It's weird though that it was only today that I encountered it, probably something I did before I slept last night.
It took me more time to fix the issue though (even after following the instructions; that is clearing my user certificates via certmgr.msc). When opening the SQL Configuration Manager and checking for the available certificates, two certificates still appear. I just deleted them using certmgr.msc but they're still there. After a few more minutes of headache, I opened mmc.exe, added the "Certificates" snap-in and there I saw that two other certificates that weren't removed (even if it no longer exists in certmgr.msc). I think this has something to do with "local machine versus user certificate personal store". certmgr.msc is the personal store, while you could open the local machine certs by the "Certificates" snap-in in MMC. Not sure but clearing the local certs from the MMC snap-in, then restarted SQL Service did the trick for me.
Also, it's worth noting that when generating a certificate using SSL, it seems to help avoid this issue if you DON'T use the machine name (it is the default) and instead specify the domain or maybe even the localhost (using the CN switch)
I'll revisit this post next time (yeah I know, my thoughts are scattered) but just wanted to post it incase someone comes across it and despite finding the links still got into the issues I encountered. If you have any questions, feel free to contact me.
I thought of restoring a previous image of my system but where's the thrill in that. And besides, did learn a lot from this.
Also, tried to "google" the "error occured during the pre-login handshake" again just now and did return some posts (on SSL issue hints) which I didn't see when I started poking into this. I think keyed in the wrong keywords. So be careful when googling for the right phrase. And when getting weird/vague errors in SQL, visit the 1st link I gave above, or try connecting using different approaches (MDAC, SSMS or ADO.NET).
Also check this out http://support.microsoft.com/kb/900497. If the above doesn't work then try clearing (not deleting) the Certificate registry entry mentioned in the article
Have to get back to work, been stalled long enough.