SQL Server 2005, self signed SSL certificates, issue from SelfSSL

I was working on a project today and ran across a painful delay.  I have Windows 2003 Server with Microsoft SharePoint 2007, SQL Server 2005, and IIS 6.0.   I was finally to the point (actually long past it) where I should have setup SSL for some testing.  Naturally I hit the search engines to find the easiest way to setup a self signed certificate under IIS.  That is when I ran across this post on SelfSSL which is part of the IIS 6.0 Resource Kit Tools.  Unfortunately, that's where my trouble began.  I followed the instructions installing just SelfSSL and the log analyzer tool.   Then I proceeded to use the SelfSSL to create certificates for my SharePoint site(s).  Installing the certificates using the tool was painless.  No errors, it just went smoothly.  I have form based authentication and I was able to connect via SSL but something weird was happening with AD site and it was preventing me from actually working.  Since I couldn't afford to be delayed on some of this work I decided to revert the system back to non-SSL state and keep working figuring that I would get back to this when I had more time to spare on solving whatever issue was keeping things from truly working.  That's when all the fun began.   I removed the certificates using IIS manager and then restarted the server thinking I'll get a clean start to ensure it would be working again.  I was indeed wrong.   When the system came back up I wasn't able to connect to the SQL Server instance.

The first indication came from my SharePoint AD site with the message

"Cannot connect to the configuration database."

I received a slightly different (and much longer) message from try to connect using SQL Server Management Studio.  Here's the important parts.

"error occurred during the pre-login handshake ... No process is on the other end of pipe"

Naturally I was confused as to why uninstalling SSL certificates would affect my SQL Server connection but it did.  As it turns out, SQL Server uses a certificate to encrypt the login information even if your not going to use a secure connection. In the absences of an assigned certificate SQL server will create a self signed server certificate to use.  Unfortunately, if there are certificates available then SQL Server will "apparently" grab a certificate to use providing it meets certain criteria which apparently my discarded SelfSSL certificates did.  After reading numerous posts to understand what was happening I was able to clean it up on my system.  See below for some of the more useful posts I found on this topic.  

What must I do to fix this?

Make sure you have the same problem.  Apparently there are a few ways to get these same error messages from SQL Server so unless you have recently installed or un-installed self signed certificates on IIS where IIS is on the same box as SQL Server 2005 you might find this fix won't do anything for you.

Even though I used IIS Manager to remove the certificates there were still around on the system and SQL Sever was still finding them.  So, I basically did the following:

1. Click the Windows Start button and ran MMC to get the Microsoft Management Console.

2. Choose File --> Add / Remove Snap-in...  from the main menu.

3. Click the "Add" button on the Standalone tab.

4. Choose "Certificates" and click the Add button.

5. Choose "Computer account" and click the Next button.

6. Choose "Local computer" radio button and clicked Finish.

7. Click the "Close" and "OK" buttons on the previous two dialogs.

8. In the list I navigated to "Certificates (local computer) -> Personal -> Certificates"

NOTE: Your self signed certificates might be some place else but they're most likely here.

CAUTION: Be sure you SELECT the right certificates in the next step!!!

9. Click on certificates to select them and use the option to delete. BE SURE TO get the self signed ones.  There could be others here!!!

After I did this my SQL Server 2005 instance was all good again. I was able to connect.  

Hopefully find this post helpful and it save you some of the 2 hours I lost on this little expedition.

References

www.learnsqlserver.com - Force SQL Server to use particular certificate (SSL)
social.msdn.microsoft.com/forums - post on SSL certificate
www.eggheadcafe.com - error on pre-login handshake
blogs.msdn.com - no process is on the other end of pipe - bad certificate