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.