Removing TDE

Question

Post reply

Removing TDE

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

sperry-750868 SSCommitted Points: 1505 More actions · Answer 1

Yes, that is correct but make sure to follow the steps in the article to remove TDE. 🙂

Thomas Mucha SSC Eights! Points: 885 More actions · Answer 2

Thanks for the article Steve.

I had heard about the difficulty of removing TDE but not the resolution. I have mirroring setup so it's not that easy to set the principal db to simple recovery mode. Do you have any tips or resources on the best way to handle TDE removal when mirroring is involved?

Thanks,

Tom

In SQL there are no absolutes, it always depends...

sperry-750868 SSCommitted Points: 1505 More actions · Answer 3

Hi Thomas

Since totally purging the transaction log is the key step of this fix, i do not think you are going to be able to do this without taking mirroring down.

You will need to restart the instance anyway as part of the steps so you will need downtime anyway. My suggestion would be the following (you will need to test this thoroughly as its only a best guess.)

1. Take databases out of mirroring and keep traffic pointed at primary.

2. Backup the database and the log of the primary and then restore to the secondary.

3. Before putting the secondary back into mirroring follow the steps to remove TDE but do not remove the Key of the certificate.

4. Put the databases back into mirroring and then manually failover

5. Repeat steps 1 - 3 on the secondary (This was the primary until you failed over)

6. Put the databases back into mirroring and then failback if needed.

Is i said, the it just my best guess so please excuse me if there are some glaring oversights. It goes without saying that you will need to test this before running on production, while you are doing this ensure that you remove the certificate and restart the test instance after you have followed steps 1 - 6 to confirm that TDE has been removed.

Hope that helps.

Steve

brad.corbin SSC-Addicted Points: 405 More actions · Answer 4

When you say, in step 4, to truncate the log file, do you mean simply to do a

DBCC Shrinkfile (name = 'databaselog', TRUNCATEONLY)?

And then in step 5, switch to SIMPLE mode, and do the same again?

Or do you mean something different by "truncate the log file"?

(You can't do a "BACKUP LOG WITH TRUNCATE_ONLY" in SQL 2008, that backup flag has been removed.)

brad.corbin SSC-Addicted Points: 405 More actions · Answer 5

I did forget to mention, thanks for this article! This is a gaping hole in the current documentation!

One more question:

Besides dropping the certificate and restarting the server while I cross my fingers, is there any way to CONFIRM that all of the encrypted bits are gone from a log file after following the steps as you've provided them?

I'd hate to think I'd taken care of the problem when there really were some leftover encrypted pieces in the log file.

Thanks!

sperry-750868 SSCommitted Points: 1505 More actions · Answer 6

Hi Brad

I Believe that this issue has been addressed in one of the service packs and should now work as expected. I will confirm tomorrow.

Steve

sperry-750868 SSCommitted Points: 1505 More actions · Answer 7

Brad,

Should all be fixed as of service pack 3 http://redmondmag.com/articles/2011/10/07/sql-server-2008-service-pack-3-available.aspx

I have tested this and found no issues but I would advice that you so the same.

Steve

brad.corbin SSC-Addicted Points: 405 More actions · Answer 8

Thanks, Steve.

For reference, the KB article on the issue is found here: http://support.microsoft.com/kb/2463682

Looks like the fix was released for 2008 in SP3, and for 2008R2 in SP1.

Note, though, the nature of the fix is to prevent someone from dropping a certificate that is currently in use: "After applying the fix, a user is not allowed to drop the server certificate if certificate is used to secure a DEK."

So it doesn't sound like it entirely resolves all concerns when your goal is to decrypt a database to restore it to another server (and don't want to also move the cert).

So your steps to remove encryption still seem useful.

Thanks!