Transparent Data Encryption is a method to encrypt your SQL Server data on disk. Using it can present challenges, though. How does it work? How does it interact with other SQL Server features? What does it not encrypt? Join Jes as she explains the ins and outs of TDE.

Want to try it yourself (in a test environment!)? Download the script.

Jes Schultz Borland
↑ Back to top
  1. Backups of databases using TDE are encrypted.

    The following MSDN article states ‘Backup files of databases that have TDE enabled are also encrypted by using the database encryption key.’

    • Correct! What I meant to say – and I realize it didn’t come out well – is that you can’t use TDE solely to encrypt backups. If you enable TDE, it’s going to encrypt the data and log files, then the backups will be encrypted as well. You can’t specify one or the other.

  2. Pingback: (SFTW) SQL Server Links 08/08/14 - John Sansom

  3. Thanks for the good overview.
    Do ALL of the databases on a server have to use the same certificate with the same password?
    IE, If I have separate databases for individual customers does each of the databases use the same encryption key?

    • Ray – no, you can use different certificates for each database.

      • I guess that could make things complicated. For example, if you had a procedure that collects data from multiple databases and uses tempdb to aggregate and transform the data to a report.
        I think I sense “Danger Will Robinson” on that path 🙂

  4. Can you enable encryption at the table or column levels using TDE? From what I read you can enable it at the database to protect your database as a whole from restores or re-attaching, and at the column level but not at the table level. I find that odd hence the reason I wanted to re-confirm this. thanks, and o great video simple, descriptive, etc.

    • Transparent data encryption is database level only.

      There’s other forms of encryption that work at the column level, they are totally different. You could do a whole table by encrypting every column.

  5. Thanks for the great session and the scripts. I am investigating on setting up TDE, but have stumbled upon the following questions. It will be very helpful if you could shed some light.

    1. Do I have to backup the Service Master Key? If yes, why?
    2. Do I have to backup the Database Master Key? If yes, why?
    3. Where the Database Master Key should be created – at the “user database level” or at “master” database level? Why? What are the differences between both approaches and which is advisable?
    4. While restoring backups of encrypted databases on other/new instance we can have following 2 situations:
    a. Restore the database master key and certificate from their backup files and then restore the database
    b. create the database master key on the new instance, restore the certificate and then restore the database.
    What is the preferred approach?

Leave a Reply

Your email address will not be published. Required fields are marked *