Friday, January 09, 2009 6:52 PMHi All,
Actually this is the ANTI-security question.
We want to deploy TDE at our installation. A use case has appeared that client databases are sent to the client's site.
when we deploy TDE, we will have to give the clients our DEK etc. if they have to restore the backup. we cant do this!
then i thought,
we can take the prod backup (with tde) and restore it to another cluster,
remove TDE (turn encryption off)
and then do
DROP DATABASE ENCRYPTION KEY
- backup the database
- then send it off to the client
is this the proper methodology to follow?
does this methodology sound logical?
is there an easier way to do this? besides replication...
Thanks for your thoughts.
Wednesday, April 18, 2012 10:41 PM
on sql server 2008, traces of the encryption are kept meaning that even if you remove the encryption, you will still need the certificate to restore it.
This sounded like a bug to me, I am curious to know if this behaviour has been changed in sql 2008 r2 or sql 2012.
Thursday, April 19, 2012 2:22 AM
This steps sound logical, AFAIK this is the only path you can follow to disable encryption:
1. Disable Encryption database using ALTER ..... ENCRYPTION OF
2. Take a Backup and Restore on another server..
3. Drop Encryption key.
This is a similar thread; http://www.sqlservercentral.com/Forums/Topic673845-391-1.aspx
You can check id database is fully decrypted using:
SELECT name, is_encrypted
where name = 'MyDatabase'
- Edited by Vishal Gajjar Thursday, April 19, 2012 2:23 AM
Thursday, April 19, 2012 2:25 AMCheck this blog post : SQL Server Knowledge | How to enable/remove Transparent Data Encryption (TDE)
- Proposed As Answer by Antoine F Thursday, April 19, 2012 3:20 PM
Thursday, April 19, 2012 3:19 PMAwesome Vishal. Thanks for the info, the note at the end of the blog is priceless.