SQL Server cannot find Certificate

Question

Background Information:

I have a single Window VPS at example.com. I have it running IIS and SQL Server. I have a certificate for example.com that works fine with IIS. I want to use the same certificate for SQL Server to allow encrypted connections with clients. SQL Server Configuration Manager does not present the certificate in the drop down.

I believe the problem is that SQL Server does not think the certificate is valid, because what SQL Server thinks the server name is does not match the certificate (example.com).

How do I check what SQL Server thinks the server name is? If it is wrong how would I change it?

Answer 1

Okay I found out the issue.

1. The hostname on my machine was wrong. It wasn't "example.com", but some name randomly generated by windows. You can set this in the computer's properties window.

2. The certificate was not registered to be used on port 1433. I had to use netsh to enable the certificate to be used on port 1433. Instructions here: http://msdn.microsoft.com/en-us/library/ms186362(v=SQL.100).aspx

Those two steps where complete I got the certificate to show up in SQL Server Configuration Manager, but I still had a problem went I attempt to run SQL Server. It would not start with a message from the logs saying it could not find or read the SSL Certificate.

3. The last step was making sure the account running SQL Server had permission to read the certificate. I went into the certificate snap-in and then went to properties under the certificate, then on the Security tab I gave the Network Services account read permission on the certificate. (NOTE: I did not find any tutorials online reference this step. It was just some dumb luck that I found this.)

After those steps where complete the SQL Server Service start up with out any problem.