InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 22:15:37 GMT20http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxMany thanks for this article and scripts - would like to add this though, make sure you have no long running transactions on your database that you want to encrypt before encrypting. DBCC OPENTRAN; on the relevant database will let you know the longest one. Didn't find this out until we run it on our Production database after tests etc - 18 hours later and no movement - once i killed off that transaction that had be open (for days!) completed in 30 mins. Fri, 17 Aug 2012 02:56:11 GMTmark.braceyhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxgood article but unfortunately it makes the same mistake regarding master keys that others make.To restore a TDE protected database to another server you [b][u]do not[/u][/b] need to backup and restore the database master key held in the master database. if a database master key doesn't already exist on the target server create one before restoring the certificate backup from the source server, but it is in no way tied to the certificate or the restore.Mon, 13 Aug 2012 14:23:04 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxThanks for a great article. I tried it out and it works OK.Now I was just wondering about backups. We use a third party tool for backups which does its own encryption while taking the backup (AES128). How do you think using TDE will affect restores of databases backed up using this tool?Sat, 11 Aug 2012 09:11:01 GMTkennethigirihttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxThank you for the article, it was interesting. If I might add a couple of things, TDE is only available on enterprise (or developer) feature. Also, you mentioned "no peeking" at the HR password. by encasing your scripts in a GUI you shield the password from other eyes. Of course, people would have to trust you to not make your GUI log the password somewhere, but that at least removes the temptation to "peek".Fri, 10 Aug 2012 09:58:02 GMTtimothyawisemanhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxOr this obvious bug should be fixed! :-PMon, 07 Nov 2011 16:53:39 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxI read it a few hours ago. It is a really helpful article. The BOL should state those recommendations.Thanks for posting it!Mon, 07 Nov 2011 16:04:31 GMTKarlierhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxIf you need to remove TDE you need to also do what it says here:http://www.sqlservercentral.com/articles/Security/76141/Thu, 03 Nov 2011 04:56:43 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxI 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.Wed, 06 Apr 2011 15:54:22 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxYes, 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:Wed, 06 Apr 2011 10:44:03 GMTKarlierhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxKarlier,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.)Wed, 06 Apr 2011 10:13:53 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxHi 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 [url=http://www.simple-talk.com/community/blogs/johnm/archive/2008/11/21/70565.aspx]here[/url]. Explain very well why I get suspect DB when I restart the instance.Greetings!Wed, 06 Apr 2011 09:53:08 GMTKarlierhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxThis might help you:http://msdn.microsoft.com/en-us/library/ff773063.aspxIt 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.Mon, 04 Apr 2011 16:56:28 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxSean, 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:Tue, 22 Mar 2011 18:32:15 GMTKarlierhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxWhy 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:[b]Inst1[/b]Run SetupTDE.sqlBackup database to db.bakCopy db.bak, TDEServerMasterKey.key, TDEServerCertificate.cer and TDEServerCertificate.key to Inst2[b]Inst2[/b]Run RestoreTDE.sqlRun StatusOfTDE.sql to check all OKRestore the databaseI have done this many times and it works fine.To then remove TDE from Inst2:Run RemoveTDE.sqlPossibly necessary now to restart SQL service so that tempdb is rebuilt definitly without TDEYou will not now be able to restore db.bak until you run RestoreTDE.sql againFri, 18 Mar 2011 17:45:28 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxFirst 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:[code="other"]MessageCannot find server certificate with thumbprint '0x9C49052631B0487200617071C628E764135685B4'.MessageAn 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.MessageThe database 'TDEDatabase' is marked RESTORING and is in a state that does not allow recovery to be run.MessageCannot find server certificate with thumbprint '0x9C49052631B0487200617071C628E764135685B4'.[/code]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!Thu, 17 Mar 2011 15:43:29 GMTKarlierhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxComments like this make it worthwhile so thanks! :cool:Tue, 15 Mar 2011 16:02:06 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxGreat article and scripts, thanks!Tue, 15 Mar 2011 15:58:15 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxI guess my scripts as they are only work for one database [i]comprehensively[/i] :-DQuick 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:[code="sql"]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'[/code]Tue, 08 Mar 2011 16:28:43 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxI 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. :-PI 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.Tue, 08 Mar 2011 16:20:15 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxliebesiech You have the best comment here!!!!!What kind of a specialist does not clarify abbreviations at the top of such an interesting article!!!!!Tue, 08 Mar 2011 11:47:36 GMTJohn Doe-361624http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxNice article, thanks. I'm about to implement TDE on a couple of servers so very timely.As you noted:[quote][b]Sean Elliott (UK) (3/8/2011)[/b][hr]...my site only has one database that uses TDE with no other application databases on the same instance.[/quote]Question: How do you enable TDE for a second database using your scripts?Thanks.Tue, 08 Mar 2011 09:36:18 GMTTerryShttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxBy 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.pdfTue, 08 Mar 2011 07:52:04 GMTSean Elliott (UK)http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxJust 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.Tue, 08 Mar 2011 06:47:43 GMTDBA in Unit 7http://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxJust for the records so that not everybody has to google it who isn't familiar with this feature: TDE = Transparent Data EncryptionNice article though.;-)Tue, 08 Mar 2011 00:46:15 GMTliebesiechhttp://www.sqlservercentral.com/Forums/Topic1074613-2763-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/TDE/72562/">TDE Setup and Administration Scripts</A>[/B]Tue, 08 Mar 2011 00:04:50 GMTSean Elliott (UK)