Transparent Data Encryption - how to Remove or Rollback?

Question

Hi 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.
 

---

Shelly P.

Answer 1

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.

Answer 2

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 
FROM sys.databases
where name = 'MyDatabase'

---

- Vishal

SqlAndMe.com

Answer 3

Check this blog post : SQL Server Knowledge | How to enable/remove Transparent Data Encryption (TDE)

---

- Vishal

SqlAndMe.com

Answer 4

Awesome Vishal.  Thanks for the info, the note at the end of the blog is priceless.