Remove expired certificate SQL Server

Question

I am trying to delete an expired certificate from my database server. The certificate was used to encrypt connections to sql server 2014 r2. We no longer have a need to encrypt connections and so we want to delete the certificate instead of renewing, however when we delete it, sql server fails to start because it is unable to establish an ssl connection because it can't find the certificate.

I have done the following:

1. SSCM -> Protocols (right click) -> Selected Properties and set force encryption to NO.
2. On the certificate tab the drop down is blank

But SQL will fail to start with the error above. No databases are encrypted, either.

I checked by running this query

SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

encrypted state returns a null for all.

I did "export" and password protect the service master key but I don't know if that has anything to do with it or not and not sure how to "undo" that if it does.

Answer 1

Sounds like you may have deleted the certificate from the certificate store prior to unbinding it from SQL Server. I haven't tested it yet, but I'm guessing that if you did that, no certificate would be shown in the SSCM dialog because it would be trying to find the certificate that matches the thumbprint stored in the registry, but wouldn't be able to find it.

You may be able to fix this by clicking on the "Clear" button in SSCM where the certificate is configured. IF this will clear the thumbprint that is stored in the registry, then SQL Server will start, but it's possible that the "Clear" button won't work if it can't find the certificate.

If that doesn't work, check the Certificate value in the registry at:

HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.12.MSSQLSERVER\MSSQLSERVER\SuperSocketNetLib

The registry path will be different if you're using a named instance.

If it's not blank, then SQL Server will try to find the certificate that matches the thumbprint that is stored there. If it doesn't find the certificate, then it fails to start. If the certificate has been deleted and the Certificate value in the registry still contains a thumbprint, then just save what's there and then delete the thumbprint so that it's blank. Once that is done, SQL Server should start.

Lastly, if you already have the new certificate, you should be able to install it in the certificate store, bind the service to it in SSCM, and SQL Server should start.