TDE Setup and Administration Scripts

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    Comments posted to this topic are about the item TDE Setup and Administration Scripts

  • liebesiech

    SSCrazy

    Points: 2468

    Just for the records so that not everybody has to google it who isn't familiar with this feature: TDE = Transparent Data Encryption

    Nice article though.

    😉

  • DBA in Unit 7

    SSCarpal Tunnel

    Points: 4059

    Just a warning to those who would like to turn on TDE on servers that host some other databases along with the db you want to encrypt: if some of other DB's heavily utilize TEMPDB ( for example, using a lot #temp tables, doing a lot sorts, etc...), turning on TDE may kill the performance, yes, KILL, literally, the server would not responce due to the chance that the tempdb would go mad and refuse to work.

    we had this last week in our DEV server and i couldn't even run sp_who2 when it happened.

    RESTART Server ( tried to restart the SQL Server service and it hang; had to reboot the server) and turn off TDE...and then Restart Server cleaned up the chokeness.

    Make sure you test enough before putting it to PROD...or leaving those encrypted DB'S on one box may be a better idea.

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    By chance my site only has one database that uses TDE with no other application databases on the same instance. Definitly try any change in DEV or TEST before PROD. A silver bullet restart of SQL might help if you have some databases using TDE and some not using TDE. It is the case that you have to restart SQL if you run the RemoveTDE script so that tempdb get receated properly so perhaps this is true when you first set it up too.

    Not sure that performance would be hit massively re use of tempdb. Clearly there is no such thing as "something for nothing" so there is a performance hit but I believe it is not prohibitive unless your server is already operating near the bounds of its capacity. I did attend a Kevin Guinn presentation on TDE performance impact at SQL PASS 2009 which presented results from extensive performance comparisons of TDE configured databases against non TDE databases for sample operations. The results did not deter me from going ahead with my intended use of TDE. That said my particular scenario is not a heavily used database. This seems to be the basis for that presentation:

    http://www.dell.com/downloads/global/solutions/Dell_SQL2008_EE_Encryption_and_Compression.pdf

  • TerryS

    SSCommitted

    Points: 1642

    Nice article, thanks. I'm about to implement TDE on a couple of servers so very timely.

    As you noted:

    Sean Elliott (UK) (3/8/2011)


    ...my site only has one database that uses TDE with no other application databases on the same instance.

    Question: How do you enable TDE for a second database using your scripts?

    Thanks.

  • John Doe-361624

    SSC Veteran

    Points: 246

    liebesiech

    You have the best comment here!!!!!

    What kind of a specialist does not clarify abbreviations at the top of such an interesting article!!!!!

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    I did not want to write yet another article about TDE that tells you lots of complicated theory and background but then does not actually tell you how to use it for real. If you do not know what TDE is an acronym for then this is not the place to start. 😛

    I read several of these and still did not understand what you were supposed to do or really why there were so many levels of protection. It was only when I tried to do it for real that I gradually understood properly.

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    I guess my scripts as they are only work for one database comprehensively 😀

    Quick answer - everywhere where there are commands affecting XX_DATABASE_XX copy and paste them as many times as required to include all the databases you require to use TDE.

    If there are many such databases you could use master.dbo.sp_MSForEachDb to hit all relevant databases in a loop replacing XX_DATABASE_XX with [?] or '?'. You can see extensive use of sp_MSForEachDb in my other article about DDL auditing - gets slightly tricky if you need any quote characters in the command.

    Not all the scripts do actually refer to a particular datadase so not all need changing. An example of one that would need changing is RemoveTDE.sql. For example part of it could be enhanced to:

    exec master.dbo.sp_MSForEachDb

    '

    if ''?'' != ''tempdb''

    begin

    if exists

    (

    select 1 from sys.dm_database_encryption_keys

    where DB_NAME(database_id) = ''?''

    )

    begin

    while not exists

    (

    select encryption_state from sys.dm_database_encryption_keys

    where DB_NAME(database_id) = ''?'' and encryption_state = 1

    )

    begin

    set @DateStr = convert(varchar, getdate(), 120)

    raiserror(''%s Waiting 5 seconds for database encryption to complete'', 0, 0, @DateStr) with nowait

    waitfor delay ''00:00:05''

    end

    end

    end

    '

  • UMG Developer

    SSChampion

    Points: 13482

    Great article and scripts, thanks!

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    Comments like this make it worthwhile so thanks! 😎

  • Karlier

    Old Hand

    Points: 360

    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!

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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

  • Karlier

    Old Hand

    Points: 360

    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:

  • Sean Elliott (UK)

    SSCommitted

    Points: 1626

    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.

  • Karlier

    Old Hand

    Points: 360

    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!

Viewing 15 posts - 1 through 15 (of 25 total)

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