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.
SQLSailor 
Thanks It Help me.
Glad to know,and thanks for taking time to write this feedback.
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
Great,thanks for the info !
Simply superb. Thanks for sharing.
Thanks for the feedback,glad that you liked it !
trying delete master key
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.
Dear Tanya
I got the same error, it just means that you didnt change back to the MASTER database
you need to run the following commands under the user database:
ALTER DATABASE TestEncryption SET ENCRYPTION OFF
DROP DATABASE ENCRYPTION KEY
Then you need to run the below under the MASTER DATABASE:
USE MASTER
DROP CERTIFICATE MyServerCert
DROP MASTER KEY
Hope this helps
KY
Perfect. Thank you sir. I was trying to set up TDE with db mirroring. I got things mixed up and wanted to start over. This did the trick.