Drop Master Key ! – Understanding encryption hierarchy


Recently I had a blog post related to Transparent Data Encryption(TDE) on SQL Server 2012 RC 0 and today I wanted to clean up my test environment and started the whole process.

Accidentally I wrote the command to delete the Master Key as a first setup and that resulted this blog post-

DROP MASTER KEY

As soon as I executed this command there came this error

Msg 15580, Level 16, State 1, Line 1
Cannot drop master key because certificate ‘TDECertificate’ is encrypted by it.

Wow! That message is super clear.It clearly states that look you have a certificate named TDECertificate which is encrypted by me (Master key).

I went ahead and tried to delete the certificate TDECertificate.This time I was sure what error I am going to get,however was  happily testing this out

DROP CERTIFICATE TDECertificate

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

Again,the message is pretty clear and it states that the certificate cannot be dropped as its related to the Database Encryption Key.

What next? Lets go ahead and delete the Database Encryption key to simulate another error

DROP DATABASE ENCRYPTION KEY

Msg 33105, Level 16, State 1, Line 1
Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

Oh Oh! The error message says that I need to turn off encryption before I do this.So lets go ahead and do this

ALTER DATABASE TestEncryption SET ENCRYPTION OFF
Command(s) completed successfully

Now as I turned off encryption,I will be able to go in reverse order and do this.

DROP DATABASE ENCRYPTION KEY
DROP CERTIFICATE TDECertificate
DROP MASTER KEY

I hope that you all liked this simple and easy to understand concept of encryption hierarchy,and I am looking forward to hear from you.

Thanks for reading.


		
Advertisements

8 thoughts on “Drop Master Key ! – Understanding encryption hierarchy

  1. I’m using SQL 2012 and I had to do the following for the final step:

    USE mydb;
    DROP DATABASE ENCRYPTION KEY
    GO

    USE master;
    DROP CERTIFICATE MyCertificate
    DROP MASTER KEY
    GO

  2. Im working with sql server 2012
    I keep the following error when I was trying to delete master key, certificate key

    Msg 15151, Level 16, State 1, Line 1
    Cannot find the symmetric key ‘Ms_servicemasterkey’, because it does not exist or you do not have permission.
    Msg 15151, Level 16, State 1, Line 2
    Cannot drop the symmetric key ‘Ms_servicemasterkey’, because it does not exist or you do not have permission.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s