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 https://sqlblog.com/blogs/greg_low/ and https://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: