August 16, 2012 at 12:01 am
Comments posted to this topic are about the item TDE and SQL Server Databases
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2012 at 6:19 am
Could you elaborate on your bullet point about TDE not encrypting databases used in replication topologies? I'm thinking specifically of log-shipping.
August 16, 2012 at 6:23 am
nice article. key points covered very well.
we use TDE. we have about half the databases encrypted. the others are not.
i noticed less than 5% increase in CPU usage implemening TDE. I find it to be a great feature.
August 16, 2012 at 7:25 am
Geoff A (8/16/2012)
nice article. key points covered very well.we use TDE. we have about half the databases encrypted. the others are not.
i noticed less than 5% increase in CPU usage implemening TDE. I find it to be a great feature.
Hi
thanks for your feedback. yes it is a great feature but it doesn't work well in all scenarios and this is what users should be aware of before implementing.
As an example, If all you want TDE for is to protect the disk files then NTFS ACLs would probably be less hassle and more appropriate that coupled with securing the rest of the Windows server platform
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2012 at 7:29 am
alex.thompson (8/16/2012)
Could you elaborate on your bullet point about TDE not encrypting databases used in replication topologies? I'm thinking specifically of log-shipping.
Replication does not replicate the database encryption calls, they have to be made manually.
Log shipping is not replication. See the following extract from Books Online
SQL Server Books Online
If a database is being used in database mirroring or log shipping, both databases will be encrypted. The log transactions will be encrypted when sent between them.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2012 at 8:07 am
I've tested TDE with log shipping. It works fine with it. You just have to install the same certificate onto the secondary servers that protects the DEK of the database.
I think he's referring to transactional, snapshot, and merge replication and TDE doesn't protect replicated objects unless the subscriber also implements TDE of it's own.
August 16, 2012 at 8:17 am
Nice article.
There's two more things that TDE does not protect...
1) Data in memory. Anyone with access to RAM, or who can cause a core dump and grab a copy of it can read whatever was in memory at the time. If you're concerned about this - at the very least clean up core dump files regularly.
2) Data in transit. The article does say that TDE only protects data at rest. I just feel that this needs to be emphasized. If you want to protect data on the network, you need to enable network encrpytion as well.
I'm hoping that Microsoft will address the encryption+compression problem in the next release. It's really not that difficult. Decrypt the backup stream, compress it, and re-encrypt it. Sure it will add CPU cycles, but if your compressing backups and encrypting databases, you're probably not concerned about the few extra CPU cycles anyway.
I use compressed backups and my own testing has found that TDE increases the backup size by almost double. YMMV.
August 16, 2012 at 8:44 am
chuck.hamilton (8/16/2012)
2) Data in transit. The article does say that TDE only protects data at rest. I just feel that this needs to be emphasized. If you want to protect data on the network, you need to enable network encrpytion as well.
When used with log shipping and mirroring the log records are encryted during transit
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 16, 2012 at 5:30 pm
I'm surprised there isn't more mention generally about the fact that TDE is only available in SQL Enterprise edition.
This seems to be where it is least needed as the data and backup files in an Enterprise environment should be kept as secure as query access to the DBMS (which is unencrypted).
I would think SQL Standard edition is where this feature would be of most value as there is a greater likelihood of backups being handled or stored in an insecure manner.
August 16, 2012 at 6:35 pm
guy.stephens (8/16/2012)
I'm surprised there isn't more mention generally about the fact that TDE is only available in SQL Enterprise edition.This seems to be where it is least needed as the data and backup files in an Enterprise environment should be kept as secure as query access to the DBMS (which is unencrypted).
I would think SQL Standard edition is where this feature would be of most value as there is a greater likelihood of backups being handled or stored in an insecure manner.
Yes fair comment.
Otherwise what did you think of the article content
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2012 at 1:08 am
I thought it was a very good article :).
Just making the point, since I've read a number of articles about TDE, had tried it out (on my SQL Developer version, which has all features), and had recommended it to customers. It was somewhat embarrassing to learn that it wasn't available in the versions of SQL they use.
August 17, 2012 at 5:25 am
yes i can see that would be a little embarrassing, please don't forget to rate the article if you found it useful
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2012 at 11:43 am
This is a good write up. A couple of comments:
1) Something to consider if you have more than a handful of databases and want to avoid key management headaches is a network Hardware Security Module (HSM) to secure and manage the keys. The network HSM manages the asymmetric key which is used to protect the symmetric key that is created when TDE is enabled for both SQL Server (and Oracle if you are using Oracle TDE).
2) The key in the Master database is not secure - there are known ways to extract this key if you have access to the system. This is a security hole and without an external key manager or HSM on the system SQL Server with TDE is technically not PCI compliant if that is critical to your regulatory needs. A network HSM allows you to avoid having to purchase a hardware HSM for each server to protect the key. On a side note, Vormetric Key Management manages TDE keys for both SQL Server and Oracle.
3) You mentioned some third party products. There are also products like Vormetric Encryption which provides file-level encryption for data outside of your user and tempdb tables along with associated files outside of the database. This can encrypt the Master Tables, System Tables, Log files and any other external content such as trace files that may contain sensitive data.
Cheers!
Todd
August 17, 2012 at 4:32 pm
Hi Todd thanks for the comments, I totally agree, if you're serious about implementing TDE then an external key management service is a must.
The 3rd party products I referred to were backup products such as Litespeed or sqlbackup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 27, 2013 at 8:39 am
I understand TDE does not encrypt FileStream data. In SQL 2012, a new feature is FileTable, which is built upon FileStream technology. This implies that TDE does not support FileTable technology. I have not seen this mentioned specifically. Anyone have any info on this? Thanks.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply