Cloud & SQLSailor

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.