Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

TDE Setup and Administration Scripts Expand / Collapse
Author
Message
Posted Thursday, March 17, 2011 3:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 104, Visits: 316
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
Posted Friday, March 18, 2011 5:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 29, 2014 11:17 AM
Points: 42, Visits: 180
Why did you create a new master key on instance2? 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
Posted Tuesday, March 22, 2011 6:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 104, Visits: 316
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.
Post #1082418
Posted Monday, April 4, 2011 4:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 29, 2014 11:17 AM
Points: 42, Visits: 180
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
Posted Wednesday, April 6, 2011 9:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 104, Visits: 316
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
Posted Wednesday, April 6, 2011 10:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,160, Visits: 2,191
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
Posted Wednesday, April 6, 2011 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 104, Visits: 316
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?
Post #1089409
Posted Wednesday, April 6, 2011 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 29, 2014 11:17 AM
Points: 42, Visits: 180
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
Posted Thursday, November 3, 2011 4:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 29, 2014 11:17 AM
Points: 42, Visits: 180
If you need to remove TDE you need to also do what it says here:

http://www.sqlservercentral.com/articles/Security/76141/
Post #1199810
Posted Monday, November 7, 2011 4:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 104, Visits: 316
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse