Post-it notes: Chapter 5 – Data Encryption

February 17, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.

TDE

Transparent Data encryption (TDE)

  • Introduced in 2008
  • Encrypts data at rest, no application changes and min performance hit
  • Decrypted in memory
  • If TDE is enabled, backups are encrypted
  • TempDB is encrypted
  • Cannot encrypt other system db’s
  • Enterprise/Developer edition only

Enabling TDE

  • Create service master key on instance (SMK)
  • Create DB master key + Cert in master db (DMK)
  • Create DB encryption key in user db (DEK)
  • Then enable TDE in the DB.
  • TDE based on Data Protection API (DPAPI), introduced Win200, used since SQL 2005
  • TDE can only be enabled using T-SQL, no options in SSMS

TDE hierarchy

  • Create service master key on instance (SMK)
    • Created by SQL Server at setup
    • Encrypted using OS DPAPI and local key based on service account credentials
    • BACKUP SERVICE MASTER KEY TO FILE = …
    • Used to encrypt/decrypt all keys on server
    • Create DB master key + Cert in master db (DMK)
      • DMK is stored in master db
      • DMK is encrypted using Triple-DES algorithm
      • CREATE MASTER KEY ENCRYPTION BY…
      • CREATE CERTIFICATE <> WITH
      • BACKUP CERTIFICATE
      • Create DB encryption key in user db (DEK)
        • Stored in boot record of encrypted db
        • Created using DMK cert
        • CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE <>
        • TDE encrypts data and log using DEK

Encryption Algorithms

  • 3 Key Triple DES
  • AES (128, 192 and 256bit)
  • T-SQL – WITH ALGORITHM

How?

  • Encrypted at page level
  • Performed in memory before/after writing/reading to disk
  • Encrypted/decrypted using DEK
  • Is_crypted column set to 1 in sys.databases
  • Uses shared locks while en/de-crypting

Encryption Keys

  • Symmetric
    • User same pswd to encrypt/decrypt
    • faster
    • Asymmetric
      • Use public pswd to encrypt and private to decrypt
      • More secure
      • Uses certs (container for asymmetric key)

Certificates

  • Encrypt data between SQL and apps over networks
  • Support db mirroring
  • Support server broker conversation
  • Sign SSIS packages
  • Certs are securable – can assign permissions
  • SQL Server can use internal generated certs or externally generated ones.

Key management

  • Create, backup, restore, change keys
  • Default – stores all with SQL
  • Can use 3rd party, external key management (EKM) & hardware based security model (HSM)
  • Plug-in, handles en/decrypt and management
  • HSM – dedicated HW handles en/decrypt
  • NOTE: SQL 2008, EKm does not support certs

Cell/Column encryption

  • Introduced SQL 2005
  • Performance and space costs
  • Must be varbinary
  • EncryptByAsymKey
  • DecryptByAsymKey
  • Encrypted column cannot be used as Index or in WHERE clause

OS Encryption

  • W2K introduced encrypting file system (EFS)
  • Protects data at rest
  • Generally used for single computer/user
  • EFS will result in performance hit in SQL.
  • TDE cannot encrypt system db’s, but EFS could be used.

Backups

  • Once TDE is enabled, backups are encrypted
  • Cannot restore on another server
  • Need to create server cert on new server from backup of source server
  • Once key is restored, you can then restore the DB

Thanks for reading.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: