TDE Setup and Administration Scripts

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sean Elliott » TDE Setup and Administration Scripts

25 posts, Page 2 of 3

««1 2 3 »»

TDE Setup and Administration Scripts

Rate Topic

Display Mode

Topic Options

Author

Message

Karlier

Posted Thursday, March 17, 2011 3:43 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:49 PM
Points: 104, Visits: 256

First of all, many thanks for your time and for share these useful set of scripts.

Secondly, I really appreciate your help to understand some issues that I have.

My scenario:
I successfully enable TDE on a sample database called 'TDEDatabase' on "instance 1" with SetupTDE.sql, then I successfully restored the sample database on "instance 2", BUT when I tried to remove TDE with RemoveTDE.sql and restart the "instance 2", the database that was encrypted becomes "suspect", the only solution that I founded to bring it back, was restoring the certificate and its private key.

Here a sample of SQL Server Log:

Message
Cannot find server certificate with thumbprint '0x9C49052631B0487200617071C628E764135685B4'.
Message
An error occurred during recovery, preventing the database 'TDEDatabase' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Message
The database 'TDEDatabase' is marked RESTORING and is in a state that does not allow recovery to be run.
Message
Cannot find server certificate with thumbprint '0x9C49052631B0487200617071C628E764135685B4'.

Note: I skipped the restore of Master Key, instead, I just create a new one on "instance 2".

Did you know where I'm missing?

Thanks!

Post #1080086

Sean Elliott (UK)

Posted Friday, March 18, 2011 5:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42, Visits: 170

Why did you create a new master key on instance2? w00t

This is probably the cause of the problem.

The correct sequence to restore an encrypted database from one instance to another is:

Inst1
Run SetupTDE.sql
Backup database to db.bak
Copy db.bak, TDEServerMasterKey.key, TDEServerCertificate.cer and TDEServerCertificate.key to Inst2

Inst2
Run RestoreTDE.sql
Run StatusOfTDE.sql to check all OK
Restore the database

I have done this many times and it works fine.

To then remove TDE from Inst2:

Run RemoveTDE.sql
Possibly necessary now to restart SQL service so that tempdb is rebuilt definitly without TDE
You will not now be able to restore db.bak until you run RestoreTDE.sql again

Post #1080770

Karlier

Posted Tuesday, March 22, 2011 6:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:49 PM
Points: 104, Visits: 256

Sean, thanks for your reply.

I created a new master key because in real world maybe I need to restore a TDE DB backup on a server with encrypted content (secured by its own master key), so in fact, if I a restore the master key with the original TDE backup, the whole encrypted stuff on my Instance2 is lost. Correct me if I wrong.

However, I followed the steps just like you propose and I get the same results (suspect DB). Maybe I screw up the whole Service Master Key or something. w00t

Post #1082418

Sean Elliott (UK)

Posted Monday, April 04, 2011 4:56 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42, Visits: 170

This might help you:

http://msdn.microsoft.com/en-us/library/ff773063.aspx

It does explain how you can have a different master key on different servers. In my case I do not need to do this and it is easier for me to just have the configuration exactly the same. If you can avoid having a different master key I would recommend it as a pragmatic simplification.

I think you must have done something out of sequence or wrong at some point. You need to start again from the beginning and go slowly forwards. This technology is very secure and complicated so it is easy to get it wrong.

Post #1088351

Karlier

Posted Wednesday, April 06, 2011 9:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:49 PM
Points: 104, Visits: 256

Hi Sean, I think there are some misunderstood here. My issues are when a I remove TDE and restart the instance, not to configure, restoring or test TDE.

What I did successful:
* Configure TDE on a Demo DB on Instance 1.
* Restore TDE Demo DB on Instance 2 (without restoring the Master Key)
* Remove TDE Demo DB on Instance 1 and Instance 2 (runing script RemoveTDE.sql)

What I did not successful:
* Restart Instance 1 or Instance 2 after remove TDE (runing script RemoveTDE.sql).

I founded that you have to maintain the certificate and its private key "even" when TDE was turned off. If you remove those objects, you got a "suspect" DB after restart the instance. For some reason, its maintain a dependency forever.

Did you try to restart your instances after running the script RemoveTDE.sql?

Take a look over here. Explain very well why I get suspect DB when I restart the instance.

Greetings!

Post #1089363

UMG Developer

Posted Wednesday, April 06, 2011 10:13 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163, Visits: 2,148

Karlier,

Are you waiting until the DMV, sys.dm_database_encryption_keys, shows that the decryption has completed before restarting the instance? (Both encryption and decryption are done as a background process.)

Post #1089383

Karlier

Posted Wednesday, April 06, 2011 10:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:49 PM
Points: 104, Visits: 256

Yes, the RemoveTDE.sql script take control of that. Am I the only one that got "suspect" DB when run RemoveTDE.sql and "restart" the instance? Blink

Post #1089409

Sean Elliott (UK)

Posted Wednesday, April 06, 2011 3:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42, Visits: 170

I have not encountered this problem and do not want to set out to cause it either! It is probably a SQL Server bug.

I see that there is some evidence that once you have setup TDE on a server you may need to keep the server certificate and the Database Master Key (DMK) in the MASTER database even though you no longer have any databases setup for TDE. So if you have this problem with databases becoming suspect on restart you should try putting the server certificate and DMK back. Not really a problem to have them there if not being used.

Post #1089619

Sean Elliott (UK)

Posted Thursday, November 03, 2011 4:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 9:32 AM
Points: 42, Visits: 170

If you need to remove TDE you need to also do what it says here:

http://www.sqlservercentral.com/articles/Security/76141/

Post #1199810

Karlier

Posted Monday, November 07, 2011 4:04 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 12:49 PM
Points: 104, Visits: 256

I read it a few hours ago. It is a really helpful article. The BOL should state those recommendations.

Thanks for posting it!

Post #1201725

« Prev Topic | Next Topic »

25 posts, Page 2 of 3

««1 2 3 »»

Permissions