SQL Server 2008 Transparent Data Encryption (TDE)


Here are some notes on “SQL Server 2008 Transparent Data Encryption (TDE)” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.


Transparent Data Encryption (TDE)

Impact

  • Indexing still works.
  • Transaction log is also encrypted.
  • Tempdb is encrypted when your first user database is encrypted.
  • Compression and encryption don’t go well together. Can’t compress encrypted data much.
  • Instant File Initialization is disabled.
  • Filestream data is not encrypted.
  • Consider impact on 3rd party tools.

Backup Encryption

  • Encrypted databases backups are encrypted
  • Encryption key must be available to restore backups (certificate + private key)
  • No option to encrypt only at backup time.

Demo

  • CREATE DATABASE…
  • CREATE TABLE… with some char column
  • INSERT…  some recognizable string
  • sp_detach…
  • Look at the database at rest using Notepad (?!), look for string
  • CREATE MASTER KEY…
  • CREATE CERTIFICATE…
  • CREATE DATABASE ENCRYPTION KEY…
  • ALTER DATABASE…SET ENCRYPTION ON
  • Select * from sys.dm_database_encryption
  • Look at encryption_state = 3, encrypted
  • sp_detach…
  • Look at the database at rest using Notepad (?!), look for string
  • Database size does not change… Same number of pages…

Backup/Restore of encrypted database

  • On source (re-using from previous demo):
    • BACKUP CERTIFICATE … TO FILE=’..’
      WITH PRIVATE KEY (FILE=’…’, ENCRYPTION BY PASSWORD=’…’)
    • BACKUP DATABASE…
  • On target
    • CREATE MASTER KEY…
    • CREATE CERTIFICATE… FROM FILE
      WITH PRIVATE KEY (FILE=’…’,DECRYPTION BY PASSWORD=’…’)
    • RESTORE DATABASE… 

Related blog posts:

Comments (2)

  1. Fred Mackie says:

    Good post, Jose. You might want to add http://technet.microsoft.com/en-us/sqlserver/dd353197.aspx to your list. There’s a  TDE How-to video there by Scott Golightly that is pretty good.

  2. Anonymous says:

    Pingback from Exam 70-458 – MCSA: SQL Server 2012 – Study Resources | Tracy Boggiano's SQL Server Blog