SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing TDE


Removing TDE

Author
Message
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3463 Visits: 3401
Excellent article. My proof of concept showed that it couldn't be removed, I'm glad you wrote this.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
SequelDBA
SequelDBA
SSC Veteran
SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)SSC Veteran (244 reputation)

Group: General Forum Members
Points: 244 Visits: 1066
Well done Steve... Thank you


KU
sperry-750868
sperry-750868
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 362
Thanks for all the comments. I have just come back from holiday and seeing all the positive comments has helped chase away the post holiday depression!
sperry-750868
sperry-750868
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 362
crazy4sql (11/2/2011)
Hi Steve, how about if we have 5 user database enabled with TDE and want to remove TDE permanently only on one/few database?


Hi Ashish

The steps apply in the same way to a single database, just don't remove the certificate if it is used by the other encrypted databases.

Steve
crazy4sql
crazy4sql
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4271 Visits: 4514
Many Thanks Steve.
So if I have to move the TDE enabled database to other server where TDE is not enabled, is my approach correct;
1) Remove TDE on Source database
2) Backup Databse
3) Restore databse

----------
Ashish
sperry-750868
sperry-750868
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 362
Yes, that is correct but make sure to follow the steps in the article to remove TDE. Smile
Thomas Mucha
Thomas Mucha
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 438
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
sperry-750868
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 362
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
brad.corbin
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 216
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
brad.corbin
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 216
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search