Share via

The certificate 'instance' cannot be dropped because it is bound to one or more database encryption key.

Question

Monday, October 20, 2014 12:48 PM

my question is the as this one on this link https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55deada2-95f1-46a9-82be-c7e684a4bddb/the-certificate-certname-cannot-be-dropped-because-it-is-bound-to-one-or-more-database-encryption?forum=sqlreplication. but there is no clear answer what to do . would anyone please help me and give me guidance?

i had create a master key and a certificate under master database. and now i want to drop these certificate and master key from  this database and face with this error  :sg 3716, Level 16, State 15, Line 1
The certificate 'TDECert' cannot be dropped because it is bound to one or more database encryption key .

thanks in advance

All replies (5)

Wednesday, October 22, 2014 6:24 AM ✅Answered

Hi sqlfan,

>how can i know that which databases are encrypted, would you plz give the query?
Please refer to the following query:

SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
WHERE db_name(database_id) not in('tempdb')

> and how i can solve the above problem?
If you find the right encrypted user database (not any system database), and follow my original reply step by step, then there won’t be any problem.

If you have any question, please feel free to let me know.

Regards,
Jerry Li

Tuesday, October 21, 2014 6:09 AM | 1 vote

Hi sqlfan,

The error states that the certificate cannot be dropped as it is related to the Database Encryption Key. So we need to drop the Database Encryption Key fist.
In order to drop the Database Encryption Key, we need to know on which user database the Database Encryption Key was created, and we need to turn off encryption on this user database before dropping the Database Encryption Key.

Please follow steps below to drop the certificate and master key:
Step 1:  Turn off encryption on your user database

ALTER DATABASE your_user_database SET ENCRYPTION OFF

Step 2: Drop the Database Encryption Key of your user database

USE your_user_database
GO
DROP DATABASE ENCRYPTION KEY

Step 3: Drop the certificate and master key on master database

USE master
GO
DROP CERTIFICATE TDECert
DROP MASTER KEY

If you have any question, please feel free to let me know.

Regards,
Jerry Li

Wednesday, October 22, 2014 5:59 AM

thanks Jerry Li for your guidance .i executed step by step what you wrote above and i pasted the answer right here:

ALTER DATABASE master SET ENCRYPTION OFF

Msg 33102, Level 16, State 1, Line 1
Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

USE master
GO
DROP DATABASE ENCRYPTION KEY

Msg 33102, Level 16, State 7, Line 1
Cannot encrypt a system database. Database encryption operations cannot be performed for 'master', 'model', 'tempdb', 'msdb', or 'resource' databases.

USE master
GO
DROP CERTIFICATE TDECert
DROP MASTER KEY

Msg 3716, Level 16, State 15, Line 1
The certificate 'TDECert' cannot be dropped because it is bound to one or more database encryption key.

how can i know that which databases are encrypted, would you plz give the query?

and how i can solve the above problem?

thanks in advance,

sqlfan

Wednesday, October 22, 2014 6:32 AM | 1 vote

Have you enaled TDE for any user database? if yes, and you do not want to continue with having TDE encryption, then you need to run the first command by changing the dbname to that user database instead of master.

ALTER DATABASE DBName SET ENCRYPTION OFF

You can run below command to see if any database are encrypted using TDE

Select is_encrypted,* from sys.databases

Keerthi Deep | Blog SQLServerF1 | Facebook

Monday, November 13, 2017 8:26 AM

Hi sqlfan,

Use the following command to find the encryption on the databases:

Select DB_NAME(database_id) Database_name,key_algorithm, key_length,* from sys.dm_database_encryption_keys WHERE db_name(database_id)<>'tempdb

You will get the databases which are encrypted.

Use Master
GO
ALTER DATABASE <Encrypted_Database_Name>
SET ENCRYPTION OFF;
GO

Use Master
GO     
ALTER CERTIFICATE <Certificate_Name>
REMOVE PRIVATE KEY
GO

Use <Encrypted_Database_Name>
DROP DATABASE ENCRYPTION KEY
GO

Use Master
DROP CERTIFICATE <Certificate_Name>
Go

This may solve your problem.

Thanks & Regards,

Rahul Gulekar

  • Last updated on