The certificate ‘xxx’ cannot be dropped because it is bound to one or more database encryption key(Deleting a Certificate)

 

You may need to delete the certificate that you created with TDE(Transparent Data Encryption) during the database encryption process. And during this deletion process, you may encounter an error as follows.

The certificate ” cannot be dropped because it is bound to one or more database encryption key. (Microsoft SQL Server, Error: 3716)

In order to delete the certificate without taking this error, you must first find and delete database encryption keys associated with this certificate on the instance. You can reach this list with the help of the following query.

The result of this query will be similar to the following screen output.

The certificate we are trying to delete is called “Sertifikam” The database name associated with this certificate is called “denemeveri”. In order to delete our certificate, we have to delete the database encryption key in the database “denemeveri”. We can do this as follows.

First, we are turning off the encryption on the database.

Then wait until the result of the following query arrives 1.

Then we delete the dek(database encryption key) in the following way.

After deleting dek, you can delete your certificate without error.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *