Wednesday, April 11, 2007
How to Force SQL Server 2005 to Use a Particular Certificate (SSL) on Your Local Machine
I don't know how helpful this will be for anyone but, today when my SQL Server 2005 default instance was not accepting any connections of any kind, it saved me. I'm on a web dev machine and was toying around with the certificates installed for SSL and I deleted the self-signed certificate that SQL Server 2005 generated for me automatically. From that point on, I could no longer connect to SQL Server. Also, because I had created a self-signed certificate in my local computer certificate store with the CN=, SQL Server 2005 was no longer auto-generating a new certificate on startup.
Bummer.
Well, here's how you can change your SQL Server from using a self-generated certificate to any other certificate:
1. At the DOS prompt, type MMC.exe to launch the MMC
2. Add new span=ins for "Certificates" (I use Local computer but you could use personal as well) and "SQL Server Configuration Manager" (File-Add/Remove Snap-Ins)
3. Ensure that the certificate you wish to use for SQL Server is in the "Personal/Certificates" folder for either the Local machine or your personal use (you kinda need to know where you installed your certificate to know which to use)
4. In the SQL Server Configuration Manager, drill down to the "SQL Server 2005 Network Configuration" and right-click on "Protocols for MSSQLSERVER" and select "Properties"
1. MSSQLSERVER is your default instance name. Change this if you are not assigning this certificate for your default instance
5. Flip to the "Certificate" tab and choose the certificate you wish to use
Hopefully this helps someone some day. I have to thank Il-Sung Lee for the "inspiration" for this post though. His post on SSL in SQL Server 2005 is what helped me resolve my issue.
I was toying around with the certificates on my machine and generated a few new certificates using OpenSSL and SelfSSL and, for whatever reason, SQL Server 2005 stopped accepting incoming connections after a restart. I suspect that I deleted the self-signed certificate that SQL creates when it starts up but it should've auto-generated one - or at least that's my understanding - and it didn't. I think it's a bug - SQL Server stopped accepting connections and would no longer auot-generate a certificate.
In order to help people find this solution in case they get into the same problem, I'm going to enter some of the errors I received when trying to connect to my SQL Server 2005 default instance that (1) has remote connections enabled, (2) the SQL Browser service was running, and (3) all protocols were enabled for both client and server:
Here's two great resources for diagnosing SQL Server connection errors:
Bummer.
Well, here's how you can change your SQL Server from using a self-generated certificate to any other certificate:
1. At the DOS prompt, type MMC.exe to launch the MMC
2. Add new span=ins for "Certificates" (I use Local computer but you could use personal as well) and "SQL Server Configuration Manager" (File-Add/Remove Snap-Ins)
3. Ensure that the certificate you wish to use for SQL Server is in the "Personal/Certificates" folder for either the Local machine or your personal use (you kinda need to know where you installed your certificate to know which to use)
4. In the SQL Server Configuration Manager, drill down to the "SQL Server 2005 Network Configuration" and right-click on "Protocols for MSSQLSERVER" and select "Properties"
1. MSSQLSERVER is your default instance name. Change this if you are not assigning this certificate for your default instance
5. Flip to the "Certificate" tab and choose the certificate you wish to use
Hopefully this helps someone some day. I have to thank Il-Sung Lee for the "inspiration" for this post though. His post on SSL in SQL Server 2005 is what helped me resolve my issue.
I was toying around with the certificates on my machine and generated a few new certificates using OpenSSL and SelfSSL and, for whatever reason, SQL Server 2005 stopped accepting incoming connections after a restart. I suspect that I deleted the self-signed certificate that SQL creates when it starts up but it should've auto-generated one - or at least that's my understanding - and it didn't. I think it's a bug - SQL Server stopped accepting connections and would no longer auot-generate a certificate.
In order to help people find this solution in case they get into the same problem, I'm going to enter some of the errors I received when trying to connect to my SQL Server 2005 default instance that (1) has remote connections enabled, (2) the SQL Browser service was running, and (3) all protocols were enabled for both client and server:
- Named Pipes Provider: No process is on the other end of the pipe.
- Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
- My SQL Server Agent would not start due to a "Cannot connect to SQL Server 2005" error
- I tried odbcping.exe and using ODBC to connect - no luck
- I tried connecting through both osql and sqlcmd with no luck
- An error occurred during the pre-handshake login process
Here's two great resources for diagnosing SQL Server connection errors:
- http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx
- This post includes the solution to this problem but I didn't find it until after I had "fixed" the server! Dang...
- http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx
