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

dbtut

6 years ago

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.

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

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.

ALTER DATABASE denemeveri 
SET ENCRYPTION OFF; 
GO

ALTER DATABASE denemeveri

SET ENCRYPTION OFF;

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

SELECT encryption_state 
FROM sys.dm_database_encryption_keys; 
GO

SELECT encryption_state

FROM sys.dm_database_encryption_keys;

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

USE denemeveri; 
GO 
DROP DATABASE ENCRYPTION KEY; 
GO

USE denemeveri;

DROP DATABASE ENCRYPTION KEY;

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