Issue
Recently, I was forwarded a request from our Product Support team to prepare the database for a third party application to be sent to the vendor for troubleshooting. This particular database has TDE enabled, so I figured that is easy, let me knock that out.
So I proceeded to restore the database to one of our non-production systems that had the storage space to accommodate the database. That took 90 minutes. Then I proceeded to run my query.
|
1 |
ALTER DATABASE CompanyDatabase SET ENCRYPTION OFF; |
Now I wait. I monitor the progress of the decryption with this query.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT db.name, db.is_encrypted, dm.encryption_state, --0 = no encryption, 1 = Unencrypted,2 = Encryption in progress,3 = Encrypted,4 = Key change in progress,5 = Decryption in progress,6 = Protection change in progress dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id |
Six hours later Encryption state was Unencrypted and Percent Complete was 0.
Time to back this sucker up and SFTP it over to the vendor.
Solution
Two days later I get an e-mail from my boss, We sure TDE was off on these? . Duh!!!
So I proceed to look at the error message.
|
1 2 |
Cannot find server certificate with thumbprint '0x11D2009EBC241712B2E04E51386982A08F4242D4'. RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 33111) |
Wait, what??? I disabled TDE, why would the restore be looking for this certificate. So I do a little research and find that to actually fully disable TDE you have to drop the key as well.
So this little bit of knowledge and code put me in my place.
|
1 |
DROP DATABASE ENCRYPTION KEY; |
Conclusion
I recommend that if it is your first time performing an action and you are sure you know the steps, do a little research. This helps identify if there are any little things that might come back to bite you.
