Setting up 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.
