Configuring a Database with Transparent Database Encryption & Availability Groups

From what I’ve seen, Transparent Data Encryption (TDE) for SQL Server isn’t as heavily used a feature as many of the HA/DR options, but if your company has a requirement for encryption at rest, it’s a great feature to be able to access out of the box. On the MySQL side, I had done extensive testing with Gazzang, this was managed at the OS level, versus TDE at the database level.

Some things to note with TDE in SQL Server:

  • Requires SQL Server Enterprise Edition
  • There is some overhead, approx 3-5% (I need to run performance tests to validate this, but this is what I’ve read)
  • Any server where the database will be restored (either as a replica, or a standalone copy), will require the certificate restore & the Enterprise version of SQL Server
  • If the certificate for the database is lost, the database is not recoverable
  • Backup compression is not supported with TDE databases
  • Your tempdb database will also automatically be encrypted if any databases on an instance are encrypted.
  • You cannot add a TDE database to an availability group via the GUI, it must be added via T-SQL

I’m running my tests on a SQL 2012 Enterprise installation.

Initial Database Encryption

 
Step
Details
1 Backup database to be Encrypted

The database to be encrypted must be backed up prior to the initial encryptions

-- Create database TESTTDE
CREATE DATABASE [TESTTDE]
 ON  PRIMARY
( NAME = N'TESTTDE', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TESTTDE.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TESTTDE_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TESTTDE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TESTTDE] SET  RECOVERY FULL
GO
 
-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-NoEncrypt.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
2 Create the Database Master Key

A certificate is required to encrypt the instance

  • Different certificates can be created for each database on an instance
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'INSTANCE1_Use1Strong2Password3Here!';
go
 
-- Check for the Database Master Key
SELECT   
    name,
    key_length,
    algorithm_desc,
    create_date,
    modify_date
FROM sys.symmetric_keys
name    key_length    algorithm_desc    create_date    modify_date
##MS_DatabaseMasterKey##    256    AES_256    2014-07-02 16:33:12.370    2014-07-02 16:33:12.370
##MS_ServiceMasterKey##    256    AES_256    2014-06-20 14:22:37.053    2014-06-20 14:22:37.053
3 Create & Backup the certificate
CREATE CERTIFICATE TDETestDBCert
WITH SUBJECT = 'TDETest DB Certficate'
go
 
-- Check for Cert
select
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'
name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    NULL
4 Create database Encryption Key
USE TESTTDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDETestDBCert
GO
5 Backup the Certificate
USE MASTER
GO
BACKUP CERTIFICATE TDETestDBCert
TO FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.cer'
WITH PRIVATE KEY (FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.pvk',
    ENCRYPTION BY PASSWORD='My1Secure2Password!')   
 
-- Check Backup
select
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'
name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    2014-07-02 20:46:55.740
6 Encrypt Database
Use TestTDE
ALTER DATABASE TESTTDE
SET ENCRYPTION ON
GO
 
-- Check Status
USE master;
GO
 
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;

 

Encryption State Key:

  • 0 = No database encryption key present, no encryption
  • 1 = Unencrypted
  • 2 = Encryption in progress
  • 3 = Encrypted
  • 4 = Key change in progress
  • 5 = Decryption in progress
  • 6 = Protection change in progress 

     

name    is_encrypted    encryption_state    percent_complete    key_algorithm    key_length
tempdb    0    3    0    AES    256
TESTTDE    1    3    0    AES    128
model    0    NULL    NULL    NULL    NULL
WingTipToys2012    0    NULL    NULL    NULL    NULL
AdventureWorks2012    0    NULL    NULL    NULL    NULL
master    0    NULL    NULL    NULL    NULL
msdb    0    NULL    NULL    NULL    NULL
Jupiter    0    NULL    NULL    NULL    NULL
Neptune    0    NULL    NULL    NULL    NULL
7 Backup Encrypted Database

Use standard backup process

-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypted.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 


 

Restoring Encrypted Database

A TDE database to be restored requires that the server have the certificate restored.

You will need to know the backed up certificate password, created in step 5 in the Initial Database Encryption section

Part 1: Restore Certificates

 
Step
Details
1

Copy database & certificate backups to new host

  • Encrypted database backup
  • Certificate backup file & key
2

Create the Database Master Key on the new instance

Icon

This is only required if the instance has not yet been configured with a Master Key

 

A certificate is required to encrypt the instance

  • Different certificates can be created for each database on an instance
USE master;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'INSTANCE2_Use1Strong2Password3Here';
go
 
-- Check for the Database Master Key
SELECT   
    name,
    key_length,
    algorithm_desc,
    create_date,
    modify_date
FROM sys.symmetric_keys
name    key_length    algorithm_desc    create_date    modify_date
##MS_DatabaseMasterKey##    256    AES_256    2014-07-02 17:12:01.127    2014-07-02 17:12:01.127
##MS_ServiceMasterKey##    256    AES_256    2014-06-13 16:13:05.383    2014-06-13 16:13:05.383
3 Restore Certificate
Icon

You will need the certificate backup, private key file backup, and the password used to create the backup

USE master;
GO
CREATE CERTIFICATE TDETestDBCert
    FROM FILE = 'C:\Restore\TDETestDBCert.cer'
    WITH PRIVATE KEY (FILE = 'C:\Restore\TDETestDBCert.pvk',
    DECRYPTION BY PASSWORD = 'My1Secure2Password!');
GO
 
-- Verify Restore
select
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'
name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    NULL
4 Backup the Certificate on the restored instance
USE MASTER
GO
BACKUP CERTIFICATE TDETestDBCert
TO FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.cer'
WITH PRIVATE KEY (FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.pvk',
    ENCRYPTION BY PASSWORD='My2Secure2Password!')   
 
-- Check Backup
select
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'
name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    2014-07-02 21:16:54.673

Part 2 A – Restore Database (Standalone)

Icon

NOTE – This is only for a standalone database. Please refer to Restore database with Availability Group if configuring a replica set.

 
Step
Details
1

Restore Database

 

  1. Restore for Standard backup

Run Standard database restore

RESTORE FILELISTONLY FROM DISK='C:\Restore\TESTTDE-Encrypted.bak'
GO
 
RESTORE DATABASE TESTTDE
FROM DISK='C:\Restore\TESTTDE-Encrypted.bak'
WITH MOVE 'TESTTDE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE.mdf',
    MOVE 'TESTTDE_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE_log.ldf'
GO
LogicalName    PhysicalName    Type    FileGroupName    Size    MaxSize    FileId    CreateLSN    DropLSN    UniqueId    ReadOnlyLSN    ReadWriteLSN    BackupSizeInBytes    SourceBlockSize    FileGroupId    LogGroupGUID    DifferentialBaseLSN    DifferentialBaseGUID    IsReadOnly    IsPresent    TDEThumbprint
TESTTDE    C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE.mdf    D    PRIMARY    10485760    35184372080640    1    0    0    3DAD2405-E559-4E2E-B009-196F3DFCA37A    0    0    2424832    512    1    NULL    30000000033200197    3C8B27A5-69F9-4D00-AD03-B6BE6B43F5BD    0    1    0x527486CBFB44D8EF339195F76049DC95A436BEA8
TESTTDE_log    C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE_log.ldf    L    NULL    10485760    2199023255552    2    0    0    7265BDCF-E038-4EEF-A1A4-C8BFF652CCFE    0    0    0    512    0    NULL    0    00000000-0000-0000-0000-000000000000    0    1    NULL

Processed 296 pages for database 'TESTTDE', file 'TESTTDE' on file 1.
Processed 2 pages for database 'TESTTDE', file 'TESTTDE_log' on file 1.
RESTORE DATABASE successfully processed 298 pages in 0.079 seconds (29.377 MB/sec).

 

Part 2 B – Restore Database to Existing Availability Group

TDE databases cannot be managed by SSMS Availability Group Wizard, and must be configured by command line & manual restore.

Icon

This is only required for configuring a replication set

 
Step
Details
1 Add Master to Primary in Availability Group Assumptions:

  1. Availability group (Group-Alpha) exists
  2. Database is on the primary node of Group-Alpha.
    1. If it’s not the primary node, failover to create as the primary node
USE master
GO
ALTER AVAILABILITY GROUP [Group-Alpha]
ADD DATABASE [TestTDE];
GO
2 Backup Primary database  and log
-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypt-AGSetup.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
-- transaction log backup
BACKUP LOG [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypt-AGSetup-1.trn'
 WITH NOFORMAT, NOINIT, 
 SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
3

Restore Database no recovery

 

  1. Copy full & transactionlog backup to secondary
  2. Run full restore with NORECOVERY
  3. Run Transaction log restore with NORECOVERY
-- Test backup file
RESTORE FILELISTONLY FROM DISK='C:\Restore\TESTTDE-Encrypt-AGSetup.bak'
GO
 
-- Run full restore no recovery
RESTORE DATABASE [TESTTDE] FILE = N'TESTTDE'
FROM  DISK = N'C:\Restore\TESTTDE-Encrypt-AGSetup.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
 
-- Run transaction log restore - no recovery
RESTORE LOG [TESTTDE]
FROM  DISK = N'C:\Restore\TESTTDE-Encrypt-AGSetup-1.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
4 Add Secondary to Availability Group
USE master
GO
 
ALTER DATABASE [TestTDE] SET HADR AVAILABILITY GROUP = [Group-Alpha]
5 Repeat on any additional Replicas Repeat steps 3 (restore database with norecovery) and step 4 (add secondary to availability group) for any other replicas

Additional Reference

Adding a TDE database to an Availability Group:

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