Menu
AlwaysOn 2016 – Encrypted Databases in an AG
By Ginger Daniel | Encryption
In the AlwaysOn 2016 release, there are several enhancements that have improved manageability, scalability, and availability. One of the enhancements is the ability to add encrypted databases to an AlwaysOn group.
Encrypted databases were allowed in earlier versions of AlwaysOn, however they could not be accessed if there was a failover to a secondary replica because the primary and secondary replicas had different service master keys. So when the secondary would try to decrypt the database master key using its own service master key, the master key would fail to open because of an invalid password.
In SQL 2016 there is now access to the database on the secondaries in the event of failover since it is no longer a requirement for each replica to have the same Database master Key (DMK) and password on each instance. There is no need to back up the DMK from the primary and restore it to the secondary(s), it is only necessary that a DMK exist on each replica. As long as each replica has a DMK created, even without the same password, there will not be any encryption/decryption issues because of different keys.
Transparent Data Encryption (TDE) is a method of protecting your data ‘at rest’. There are several methods of protecting your data in addition to TDE, but this article will only discuss TDE encrypted databases in an AlwaysOn Availability Group. If your physical media (drives or backup tapes) are stolen, TDE will encrypt the sensitive data in the data and log files, and will also protect the keys used to encrypt the data with a certificate. TDE provides for real-time I/O encryption and decryption, but it does not protect data ‘in-flight’ flowing from the server to the end user. Also it does not protect data from a user who has privileges in the SQL instance.
The steps below demonstrate how to add an encrypted database to an AlwaysOn Availability Group
If your database has not already been encrypted, follow these steps to create a master key (DMK), backup the certificate, create a database encryption key (DEK), and enable encryption on the database on your primary replica.
–create DMK database master key
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd’
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd’
GO
BACKUP MASTER KEY TO FILE = ‘C:\SQLRX\KeyBkup\SQLDMK.dmk’
ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’
GO
–check to make sure master key is set up
SELECT b.name, a.crypt_type_desc
FROM sys.key_encryptions a
INNER JOIN sys.symmetric_keys b
ON a.key_id = b.symmetric_key_id
WHERE b.name = ‘##MS_DatabaseMasterKey##’;
GO
–create and backup certificate
CREATE CERTIFICATE TDECert WITH SUBJECT = ‘Cert used for TDE’;
GO
BACKUP CERTIFICATE TDECert
TO FILE = ‘C:\SQLRX\KeyBkup\Cert4TDE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\SQLRX\KeyBkup\TDEPvtKey.pvk’,
ENCRYPTION BY PASSWORD = ‘*DBA$RC00l’
);
GO
–create DEK database encryption key with certificate
USE GKTestDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 — AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO
USE master
GO
—enable TDE for the database
ALTER DATABASE GKTestDB SET ENCRYPTION ON
GO
–monitor encryption progress
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
GO
On each secondary replica, verify that a Database Master Key (DMK) exists in the master database.
–check to make sure master key is set up
SELECT b.name, a.crypt_type_desc
FROM sys.key_encryptions a
INNER JOIN sys.symmetric_keys b
ON a.key_id = b.symmetric_key_id
WHERE b.name = ‘##MS_DatabaseMasterKey##’;
GO
If a record is returned, that means a DMK exists on the secondary instance. If no record is returned, then create a one as follows:
–create DMK database master key
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMKP@ssw0rd2’
GO
BACKUP MASTER KEY TO FILE = ‘C:\SQLRX\KeyBkup\SQLDMK.dmk’
ENCRYPTION BY PASSWORD = ‘G00dP@ssw0rd’
GO
On each secondary replica instance, create/restore the TDE Certificate from the server certificate and private key that you backed up on the primary. Copy the certificate as well as the private key from the primary over to a file path on the secondary(s). The following will install the certificate onto the secondary replica(s).
–create TDE Certificate from the certificate backed up on primary
CREATE CERTIFICATE TDECert
FROM FILE = ‘C:\SQLRX\KeyBkup\Cert4TDE.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\SQLRX\KeyBkup\TDEPvtKey.pvk’,
DECRYPTION BY PASSWORD = ‘*DBA$RC00l’
)
Create a full and tlog backup of the TDE encrypted database on the primary replica.
–create Full Backup of encrypted database on primary
BACKUP DATABASE [GKTestDB]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestDB.bak’
WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND,NOUNLOAD, COMPRESSION, STATS = 10
GO
–create Tlog backup of encrypted database on primary
BACKUP LOG [GKTestDB]
TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’
WITH NOFORMAT, NOINIT, NAME = N’GKTestDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
On the primary replica instance, add the TDE encrypted database to the Availability Group. As of the current release of SQL Server 2016, you cannot do this yet through the wizard
Instead add the TDE database to your AlwaysOn group using TSQL:
USE master
GO
ALTER AVAILABILITY GROUP AGroup_SQLRx
ADD DATABASE GKTestDB
GO
On each secondary replica instance, restore the full and tlog backups of the encrypted database with no recovery. Copy the backup files from the primary replica to a file location on your secondary and restore from that file path as follows:
–Restore Full backup of encrypted database on secondary with no recovery
USE [master]
RESTORE DATABASE [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestFullBackup.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
GO
–Restore the Tlog backup of encrypted database on secondary with no recovery
RESTORE LOG [GKTestDB] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\GKTestTlogBackup.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
GO
On each secondary replica instance, join the database to the availability group.
USE master
GO
ALTER DATABASE GKTestDB
SET HADR AVAILABILITY GROUP = AGroup_SQLRx
GO
After joining the encrypted database to the AlwaysOn group, you should now be able to fail over the group to the secondary replica and be able to access the database without any issues. It is common knowledge that encrypting a database causes degraded performance, so the next article will demonstrate the performance effects of having an encrypted database in your AlwaysOn Group.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!