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:
- SSCM -> Protocols (right click) -> Selected Properties and set
- 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.