TDE and SQL Server Databases

  • Perry Whittle

    SSC Guru

    Points: 233794

    Comments posted to this topic are about the item TDE and SQL Server Databases

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • alex.thompson

    SSC Journeyman

    Points: 75

    Could you elaborate on your bullet point about TDE not encrypting databases used in replication topologies? I'm thinking specifically of log-shipping.

  • Geoff A

    SSChampion

    Points: 11407

    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.

  • Perry Whittle

    SSC Guru

    Points: 233794

    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

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Perry Whittle

    SSC Guru

    Points: 233794

    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.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • chuck.hamilton

    Ten Centuries

    Points: 1380

    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.

  • chuck.hamilton

    Ten Centuries

    Points: 1380

    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.

  • Perry Whittle

    SSC Guru

    Points: 233794

    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

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Guy Stephens

    Old Hand

    Points: 306

    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.

  • Perry Whittle

    SSC Guru

    Points: 233794

    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

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Guy Stephens

    Old Hand

    Points: 306

    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.

  • Perry Whittle

    SSC Guru

    Points: 233794

    yes i can see that would be a little embarrassing, please don't forget to rate the article if you found it useful

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • tthiemann

    Valued Member

    Points: 62

    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

  • Perry Whittle

    SSC Guru

    Points: 233794

    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

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • deanroush

    SSC-Addicted

    Points: 474

    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