Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 | select database_name = db.name,dek.encryptor_type, cert_name = mycert.name from sys.dm_database_encryption_keys dek left join sys.certificates mycert on dek.encryptor_thumbprint = mycert.thumbprint inner join sys.databases db on dek.database_id = db.database_id where mycert.name is not null |
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.
1 2 3 | ALTER DATABASE denemeveri SET ENCRYPTION OFF; GO |
Then wait until the result of the following query arrives 1.
1 2 3 | SELECT encryption_state FROM sys.dm_database_encryption_keys; GO |
Then we delete the dek(database encryption key) in the following way.
1 2 3 4 | USE denemeveri; GO DROP DATABASE ENCRYPTION KEY; GO |
After deleting dek, you can delete your certificate without error.
