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)

• Protecting data at rest, no changes to app, low performance cost
• Once TDE is enabled, pages are encrypted on write
• Trading CPU for security, performance impact is minor
• See https://msdn.microsoft.com/en-us/library/bb934049.aspx
• CREATE DATABASE ENCRYPTION KEY ALGORITHM=…
• See https://msdn.microsoft.com/en-us/library/bb677241.aspx
• ALTER DATABASE … SET ENCRYPTION ON
• Must secure key, which is required for backups
• See https://msdn.microsoft.com/en-us/library/ms178578.aspx

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:

• https://blogs.technet.com/josebda/archive/2009/04/01/sql-server-2008-encryption.aspx

Comments

• Anonymous
  January 01, 2003
  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.
• Anonymous
  January 06, 2014
  Pingback from Exam 70-458 - MCSA: SQL Server 2012 - Study Resources | Tracy Boggiano's SQL Server Blog