Setting up SQL Server Transparent Data Encryption (TDE) can seem daunting at first, especially when AlwaysOn Availability Groups (AG) are added to the equation. Once broken into its component parts it’s quite straight forward.

TDE encrypts SQL Server data files, known as encrypting data at rest. It performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key, which is stored in the database boot record for availability during recovery. For more information refer to Microsoft documents online See TDE for details on SQL Server 2017

In summary each instance needs a master key and a certificate, for databases to work correctly within an AG and across instances the certificate needs to be consistent across all nodes.

The process below lists the steps and commands to set up TDE on a two node AlwaysOn AG cluster, to set up on more nodes follow the Secondary Instance steps on each secondary, to set up on a none clustered environment ignore the steps for the Secondary Instance.

On the Primary Instance.

Create a Master Key

If encryption is being set up for the first time there should be no Master Key, it’s best to double check first. The below SQL should produce no results.

USE MASTER

GO

SELECT * FROM

sys.symmetric_keys

WHERE name = ‘##MS_DatabaseMasterKey##’

Create the Master Key in the Primary Instance. As this key is used to safeguard database certificates it should be created using a strong password

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD =’<Strong password 1>

GO

Substitute <Strong password 1> for a suitably strong password

To confirm the Master Key has been created.

USE MASTER

GO

SELECT * FROM

sys.symmetric_keys

WHERE name = ‘##MS_DatabaseMasterKey##’

Backup Master Key

It is good practice to back up the Master Key to a secure location. The password for the backup can be different to the Master Key password

USE MASTER

GO

BACKUP MASTER KEY

TO FILE = ‘<primary master key backup file>

ENCRYPTION BY Password = ‘<strong password 2>‘;

Substitute <primary master key backup file> for a file name in suitable secure location.

Substitute <strong password 2> for a suitably strong password

Create a Certificate

Create a certificate to be used to secure the database encryption keys.

By default, the command does not need an expiry date to be specified. The default, if no date is specified, is 12 months hence.

USE MASTER

GO

CREATE Certificate <primary certificate name>

WITH Subject = ‘<primary certificate subject>

,EXPIRY_DATE =’<datetime>‘;

GO

Substitute <primary certificate name>for the certificate name.

Substitute <primary certificate subject> for certificate subject.

Substitute <datetime> for the expiry date, it can be in any format that can be converted to date and time.

Confirm that the Certificate now exists

USE MASTER

GO

select * from sys.certificates

where name = ‘<primary certificate name>

GO

Substitute <primary certificate name>for the certificate name.

Backup the Certificate

A backup of the Certificate is needed for a couple of reasons.

  • it is needed to create a matching certificate on the secondary server
  • backups of TDE enabled databases are also encrypted, should a database need to be restored the corresponding Certificate would also need to be restored to read the backup.

The backup command creates two files, a certificate backup and private key. An encryption password is also required this should be a different strong password

Use master

Go

BACKUP Certificate <primary certificate name>

TO FILE = ‘<primary certificate backup file>

WITH Private KEY (FILE = ‘<primary certificate private key file>‘,

ENCRYPTION BY Password = ‘<strong password 3>‘);

GO

Substitute <primary certificate name>for the certificate name.

Substitute <primary certificate backup file> for a location and file form the certificate backup, as this is to be used by the secondary it could be a shared location.

Substitute <primary certificate private key file> for a location and file form the certificate private key, as this is to be used by the secondary it could be a shared location.

Substitute <strong password 3> for the password used to create the backup

If your instances use different Active Directory accounts, then the permissions on these files need to be updated to allow your secondary instance to read them.

On the Secondary Instance.

Create a Master Key

Like in the Primary Instance, there should be no Master Key but it’s best to check first.

USE MASTER

GO

SELECT * FROM

sys.symmetric_keys

WHERE name = ‘##MS_DatabaseMasterKey##’

GO

Create the Master Key in the Secondary Instance. As this key is used to safeguard database certificates it should be created using a strong password which can be different to the password used to create the Primary Instance Master Key

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD =’<strong password 4>

GO

Substitute <strong password 4> for a suitably strong password

To confirm the Master Key has been created.

USE MASTER

GO

SELECT * FROM

sys.symmetric_keys

WHERE name = ‘##MS_DatabaseMasterKey##’

GO

Backup Master Key

As with the Primary Instance it is good practice to back up the Master Key to a secure location.

USE MASTER

GO

BACKUP MASTER KEY

TO FILE = ‘<secondary master key backup file>

ENCRYPTION BY Password = ‘<strong password 5>‘;

Substitute < secondary master key backup file> for a file name in suitable secure location.

Substitute <strong password 5> for a suitably strong password

Create Secondary Certificate

Create a certificate to be used to secure the database encryption keys from the backup taken on the Primary Instance.

Make sure that the Secondary Instance has access and permission to read both files.

USE MASTER

GO

CREATE CERTIFICATE <secondary certificate name>

FROM FILE = ‘<primary certificate backup file>

WITH Private KEY (FILE = ‘<certificate private key file>‘,

Decryption BY Password = ‘<strong password 3>‘);

GO

Substitute < secondary certificate name>for the certificate name.

Substitute <primary certificate backup file> primary certificate backup file and location.

Substitute <primary certificate private key file> primary certificate private key file and location.

Substitute <strong password 3> for the password used to create the primary certificate backup

Backup the Certificate

Even though the certificate is created from a backup of the Primary Instance certificate it is still good practice to create a backup, the password used to create this certificate can be different to the Primary Instance certificate backup.

USE MASTER

GO

BACKUP Certificate <secondary certificate name>

TO FILE = ‘<secondary certificate backup file>

WITH Private KEY (FILE = ‘<secondary certificate private key file>‘,

ENCRYPTION BY Password = ‘<strong password 6>’);

GO

Substitute <secondary certificate name>for the certificate name.

Substitute <secondary certificate backup file> for a location and file form the certificate backup.

Substitute <secondary certificate private key file> for a location and file form the certificate private key.

Substitute <strong password 6> for the password used to create the backup

Verification.

TDE should now be enabled and in sync at the instance level.

Run the following on both instances.

USE MASTER

GO

SELECT name, principal_id, pvt_key_encryption_type_desc, issuer_name, cert_serial_number, sid, subject, thumbprint, expiry_date

from sys.certificates

where name = ‘<certificate name>

GO

Substitute <certificate name>
for the certificate name.

Confirm that the cert_serial_number, sid & thumbprint are consistent across all instances.

Monitoring TDE

Monitoring the state of databases should be done on all instances and the Availability Group Dashboard can also be used t0 check the state of the AG and its databases

these queries should be run on both instances.

This query shows encrypted databases with the Certificate name and encryption information, the encryptor_thumbprint should be the same as the thumbprint listed for the certificates in both instances.

USE MASTER

GO

SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint , dek.database_id, dek.key_algorithm, dek.key_length

FROM sys.dm_database_encryption_keys dek

INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint

order by 1

The query below lists all databases and their encryption status.

is_encrypted         0 = not encrypted, 1 = encrypted

encryption_state     1 = unencrypted, 2 = encryption in progress 3 = encrypted

percent_complete     useful during set up shows the percent of database encrypted and when complete shows 0

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;

GO

Implementing TDE on a database

TDE can be implemented on a database that is already part of an AG or on database yet to be added. Adding TDE to a database that is already included in the AG is more straight forward because the AG Wizard does not support the addition of TDE enabled databases. For large databases consideration of when to encrypt should be taken in to account as adding TDE to a database already added to an AG may result in more data being transferred between the instances.

Implementing TDE on a database that is already part of an Availability Group

On the Primary Instance create a database encryption key using the certificate created earlier, this is needed before encryption at the database level can be set.

USE [<Database Name>]

GO

CREATE DATABASE ENCRYPTION KEY

WITH Algorithm = AES_256

ENCRYPTION BY Server Certificate <primary certificate name>;

GO

Substitute <Database Name> for the database name.

Substitute <primary certificate name>for the certificate name.

Enable Encryption for the Database

ALTER DATABASE [<Database Name>] SET ENCRYPTION ON

Substitute <Database Name> for the database name.

As the Certificates on both instances are in line the database will be encrypted and synchronised across all instances.

Adding an Encrypted database to an Availability Group

If as database that is already encrypted needs to be added to an AG this has to be done manually.

On the Primary Instance create a database encryption key using the Certificate created earlier, this is needed before encryption at the database level can be set.

USE [<Database Name>]

GO

CREATE DATABASE ENCRYPTION KEY

WITH Algorithm = AES_256

ENCRYPTION BY Server Certificate <primary certificate name>;

GO

Substitute <Database Name> for the database name.

Substitute <primary certificate name>for the certificate name.

Enable Encryption for the Database

ALTER DATABASE [<Database Name>] SET ENCRYPTION ON

Substitute <Database Name> for the database name.

Monitor as above.

As the GUI can’t be used to add Encrypted databases to an availability group T-SQL has to be used.

On the Primary Instance add the database to the AG

USE MASTER;

GO

ALTER AVAILABILITY GROUP [<Availability Group>]

ADD DATABASE [<Database Name>]

Substitute <Availability Group> for the Availability Group name.

Substitute <Database Name> for the database name.

On the Primary Instance take a backup of the Database to be used to restore on the secondary instance.

BACKUP DATABASE [<Database Name>]

TO DISK = N’<database backup file>‘ WITH NOFORMAT, INIT,

NAME = N’<Database Name>-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Substitute <database backup file> for the name of the database backup file, this file will need to be available on the Secondary instance.

Substitute <Database Name> for the database name.

On the Secondary Instance restore the backup just taken using the NORECOVERY option

RESTORE DATABASE [<Database Name>]

FROM DISK = N’<database backup file>‘ WITH NORECOVERY, NOUNLOAD, STATS = 5

Substitute <database backup file> for the name of the database backup file.

Substitute <Database Name> for the database name.

On the Primary Instance take a transaction log backup

BACKUP LOG [<Database Name>]

TO DISK = N’<log file backup file>‘ WITH NOFORMAT, NOINIT,

NOSKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

Substitute <log file backup file> for the name of the log file backup file, this file will need to be available on the Secondary instance.

Substitute <Database Name> for the database name.

On the Secondary Instance restore the transaction log backup, again with the NORECOVERY option

RESTORE LOG [<Database Name>]

FROM DISK = N’<log file backup file>‘ WITH NORECOVERY, NOUNLOAD, STATS = 5

Substitute <log file backup file> for the name of the log file backup file.

Substitute <Database Name> for the database name.

On the Secondary Instance with the database as up to date as possible alter the database to include it in the availability group.

USE MASTER;

GO

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];

GO

Substitute <Availability Group> for the Availability Group name.

Substitute <Database Name> for the database name.

Monitor as detailed above.