none
Transparent Data Encryption (TDE) SQL 2008

    Question

  • I have 2 production clustered SQL 2008 servers with a few instances and I need to turn TDE on. The db's are stored on a SAN. Has anyone done this to a live system and can offer some best practice and suggestions? Thanks!
    • Edited by dou2ble Thursday, February 23, 2012 6:24 PM
    Thursday, February 23, 2012 6:23 PM

Answers

  • In addition to testing your process in a test environment first ... the issues I had to deal with were fairly common.

    1) Key management. If you loss the certificates and /or the passwords, you have loss your data. Make many backups and store them in secure places that more then one "trusted" admin can access, if needed. Also, be sure you do not store the private keys and passwords with the database back up's, if your back up files are stolen (lost, etc.) the encryption provides no protection, its like leaving the keys in the ignition of your car :)

    2) The encryption is "Transparent" based on the SQL server security configurations. First thing you want to do is make sure that you have the proper access controls in place.

    3) If you are running in a cluster, using log shipping or mirroring, you have additional steps to do to make sure fail over works after you turn TDE on.

    4) If you are compressing your back ups, your back up size will grow significantly, it could double in size, so be sure to test your D&R process BEFORE turning TDE on.

    5) Once you encrypt one database, be aware that TempDB usage will be encrypted for all users regardless or the db they are using. 

    Microsoft made it really easy to enable this, but without the right research, testing and a good process, it can actually be worst then useless, it can cause you to loss all your data.


    William F. Kinsley

    • Proposed as answer by Iric WenModerator Monday, February 27, 2012 7:02 AM
    • Marked as answer by dou2ble Monday, February 27, 2012 5:40 PM
    Saturday, February 25, 2012 6:20 PM

All replies

  • In addition to testing your process in a test environment first ... the issues I had to deal with were fairly common.

    1) Key management. If you loss the certificates and /or the passwords, you have loss your data. Make many backups and store them in secure places that more then one "trusted" admin can access, if needed. Also, be sure you do not store the private keys and passwords with the database back up's, if your back up files are stolen (lost, etc.) the encryption provides no protection, its like leaving the keys in the ignition of your car :)

    2) The encryption is "Transparent" based on the SQL server security configurations. First thing you want to do is make sure that you have the proper access controls in place.

    3) If you are running in a cluster, using log shipping or mirroring, you have additional steps to do to make sure fail over works after you turn TDE on.

    4) If you are compressing your back ups, your back up size will grow significantly, it could double in size, so be sure to test your D&R process BEFORE turning TDE on.

    5) Once you encrypt one database, be aware that TempDB usage will be encrypted for all users regardless or the db they are using. 

    Microsoft made it really easy to enable this, but without the right research, testing and a good process, it can actually be worst then useless, it can cause you to loss all your data.


    William F. Kinsley

    • Proposed as answer by Iric WenModerator Monday, February 27, 2012 7:02 AM
    • Marked as answer by dou2ble Monday, February 27, 2012 5:40 PM
    Saturday, February 25, 2012 6:20 PM
  • Thank you William for all the very helpful info! Sounds pretty straight forward. Only one question. Bullet 3, are there additional steps outside of the usual testing for cluster failover?

    Monday, February 27, 2012 5:40 PM
  • With Clustering the SQL instance is shared between the nodes, so there should be only one service master key. 

    With that said, I know that the The Service master key is tried to the service account when SQL is installed on each node. So you would need to verify that the other node can access the Database Master key and the symmetric encryption keys. This is a MS article that talks about these keys.(http://msdn.microsoft.com/en-us/library/bb964742.aspx

    So, I would think that as long as the SQL Service is running under the same domain account on all the nodes, you should be ok, but test the fail over process to verify, otherwise you may have to copy the service master key to the other nodes.


    William F. Kinsley

    Tuesday, February 28, 2012 3:14 AM