Tuesday, November 09, 2010

Cannot log into SQL Server following SSL certificate installation

I managed to lock myself out of sql server on one of our servers. I got the following error on login with the following error "A connection was successfully established with the server, but then an error occurred during the pre-login handshake"

After a lot of googling I finally found that the issue was that I had installed a self signed certificate using the Windows 2003 resource kit. Due to the fact SQL server uses ssl certificates to secure the login my certificate had broken my ability to log in.

Following the steps listed in this Microsoft Knowledge base article fixed the problem. Essentially stop sql server, removed the certificates regenerate (ensuring to use the identifier as listed in IIS manger on the S parameter of selfssl e.g. C:\Program Files\IIS Resources\SelfSSL>selfssl.exe /N:CN=YOURSEVER /K:1024 /V:7
/S:1899305700 /P:443) then restart sqlserver.