I have a requirement to remove the SSL certificate that is being used by a SQL Server 2012 SP4 database instance for data in transit encryption.
The client wants the SQL Server to load its self-generated certificate instead of the SSL certificate that is currently loaded.
After following the correct steps for removing the certificate by
opening SQL Server configuration manager, selecting SQL Server network Configuration, select Protocols for the database instance, right click, properties, select certificate tab, clicking clear, and then restarting the database instance I get the following error along with a failed start of the database instance which is,
"Unable to load user-specified certificate [Cert Hash(sha1)
"***removed****"]. The server will not accept a connection. You should
verify that the certificate is correctly installed. See "Configuring
Certificate for Use by SSL" in Books Online."
The thumb print id matches the certificate that we cleared.
It appears that the database instance is failing to load its self-generated SSL certificate or the procedure for removing the currently loaded SSL certificate is not fully removing it.
I've never encountered this problem before. I have been unable to find
any blogs or BOL that address this issue and how to resolve it. Additionally, the certificate value in the registy is blank. Which makes me question how SQL Server even knows to look for the SSL certificate after clearing it.
How do you get SQL Server 2012 SP4 to go from using a loaded SSL certificate to using its self-generated SSL certificate?
Best Answer
Discovered that a service broker endpoint was created using the same cert that we were trying to remove. After querying DMVs for sessions and connections we discovered that no connections were using the service broker endpoint so we deleted the server broker, dropped the certificate, then followed all of the steps for removing the cert in SQL Server Manager, removed the certificate from the Windows certificate store, then restarted the database instance. Confirmed that it loaded by reading in the SQL Server error log... "A self-generated certificate was successfully loaded for encryption."