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

    Question

  • 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


    Monday, October 20, 2014 12:48 PM

Answers

  • 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

    Wednesday, October 22, 2014 6:24 AM
    Moderator

All replies

  • 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


    Tuesday, October 21, 2014 6:09 AM
    Moderator
  • 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 5:59 AM
  • 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

    Wednesday, October 22, 2014 6:24 AM
    Moderator
  • 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

    Wednesday, October 22, 2014 6:32 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

    • Proposed as answer by Rahul Gulekar Monday, November 13, 2017 8:28 AM
    Monday, November 13, 2017 8:26 AM