March 8, 2011 at 12:04 am
Comments posted to this topic are about the item TDE Setup and Administration Scripts
March 8, 2011 at 12:46 am
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.
😉
March 8, 2011 at 6:47 am
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.
March 8, 2011 at 7:52 am
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
March 8, 2011 at 9:36 am
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.
March 8, 2011 at 11:47 am
liebesiech
You have the best comment here!!!!!
What kind of a specialist does not clarify abbreviations at the top of such an interesting article!!!!!
March 8, 2011 at 4:20 pm
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.
March 8, 2011 at 4:28 pm
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
'
March 15, 2011 at 3:58 pm
Great article and scripts, thanks!
March 15, 2011 at 4:02 pm
Comments like this make it worthwhile so thanks! 😎
March 17, 2011 at 3:43 pm
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!
March 18, 2011 at 5:45 pm
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
March 22, 2011 at 6:32 pm
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:
April 4, 2011 at 4:56 pm
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.
April 6, 2011 at 9:53 am
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 24 total)
You must be logged in to reply to this topic. Login to reply