Implementing Transparent Data Encryption In Always On

Introduction

Transparent Data Encryption is an SQL DB encryption technique. In TDE, primary and secondary data files are encrypted using an encryption key with the help of Database Master Key and TDE Certificate. This is the simplest form of encryption and it has been provided by default in SQL Server 2016.

Always On Availability Group is an HADR (High Availability Disaster Recovery) technique which is introduced in SQL Server 2012. It provides an alternative to Failover Clustering (High Availability) and Mirroring (Disaster Recovery). In simple words, when the database is moved in to the Availability group, two or more physical copies of a database reside on two or more different Servers, which enables us to sync data automatically between the primary and secondary database.

In this article, I will explain the steps involved in implementation of TDE on database that is stored in the Availability Group.

Assumptions

Reader is familiar with the basics of Always On environment and has basic knowledge of different TDE terminologies.

Implementation Steps

Let us assume that the database TestTDEInAO which we are targeting is in Availability Group.

Remove Database from Availability Group

• Connect to Primary node from SSMS.
• Open Object Explorer.
• Disable _SQL_BackupAll and _SQL_BackupTranAll jobs from SQL Server agent on both, primary and secondary nodes if enabled.
• Expand the Always On High Availability node and the Availability Group's node.
• Select the availability group, and expand the Availability Databases node.
• Right click on TestTDEInAO. In menu, select Remove Database from Availability Group command.
• In the Remove Databases from Availability Group dialog box, make sure only TestTDEInAO database is listed.
• Click OK.

Encrypting Primary copy of the Database

• Generating, Encrypting, and Backing up the Master key
  
  
  • Open query window and execute the following command to check whether database Master key is present or not in Master DB
    1. USE Master;
    2. SELECT * FROM sys.symmetric_Keys WHERE name=
    3. '##MS_DatabaseMasterKey##'

    If no record is found execute the below command to create a master key.

    1. USE Master;
    2. CREATE MASTER KEY ENCRYPTION BY PASSWORD='<Password>';

    Provide a password for encrypting the master key and store it in a secure place.

• Back up the master key by executing the below command.
  1. USE master;
  2. OPEN MASTER KEY DECRYPTION BY PASSWORD ='<Password>';

  Where ’<Password>’ is the one which is used in first step for encryption of the master key.

  1. BACKUP MASTER KEY TO FILE = 'Location\MasterKeyName.key' ENCRYPTION BY PASSWORD = '<Password>';

  Where ’<Password>’ is the one which is used in first step for encryption of the master key.

• Creating and Backing up certificate
  
  
  • Create a certificate that's protected by the master key. Execute the following command in query window.
    1. USE master;
    2. OPEN MASTER KEY DECRYPTION BY PASSWORD ='<Password>';

    Where PASSWORD =’<Password>’ used for encryption of the master key.

    1. CREATE CERTIFICATE <CertificateName>
    2. WITH SUBJECT='<SubjectName>',
    3. EXPIRY_DATE ='01-01-3000'

    Ex- CREATE CERTIFICATE TDECertificate

    WITH SUBJECT = 'TDE Certificate for TestTDEInAO‘

• Backup the Certificate using the below command.
  1. USE Master
  2. GO
  4. BACKUP CERTIFICATE <Certificate Name>
  5. TO FILE = 'Location\CertName.cer'
  6. WITH PRIVATE KEY (file='Location\CertKeyName.key',
  7. ENCRYPTION BY PASSWORD='encryption_password')

  Where 'encryption_password' is the password that is used to encrypt the private key before writing the key to the backup file.

• Create a Database Encryption Key and Encrypting Database
  Follow below steps to create and secure Database Encryption Key with the help of certificate.
  
  
  • Open New Query window and use the target database.
    
    1. USE <TestTDEInAO>;
  • Create Database Encryption key Encrypted by server certificate as shown in below query.
    
    1. CREATE DATABASE ENCRYPTION KEY
    2. WITH ALGORITHM = AES_128
    3. ENCRYPTION BY SERVER CERTIFICATE <CertificateName>
  • Encrypt the database by executing following command in query window.
    
    1. ALTER DATABASE <TestTDEInAO>
    2. SET ENCRYPTION ON;
  • Check the status of the encryption by running the below query.
    
    1. USE master
    2. SELECT db_name(database_id) as 'database name' , encryption_state, percent_complete
    3. FROM sys.dm_database_encryption_keys;
    4. GO

  Where encryption state,

  2 =Encryption in progress

  3=Encrypted

  Once the database encryption state turn to Encrypted, we can say that our database is encrypted.

Backup and Restoration on Secondary Node

In this step, we will be backing up Master key, Certificate, and Database along with transactional logs as well and we will be restoring these elements on secondary node/s.

• Restoring Master key and Certificate
  • Copy the backed up key files and certificate from primary server to mirror server. Execute below command to back-up the master key in the mirrored server
    1. USE master
    2. RESTORE MASTER KEY
    3. FROM FILE = 'Location\MasterKeyName.key'
    4. DECRYPTION BY PASSWORD = 'password'
    5. ENCRYPTION BY PASSWORD = 'password';
    7. Where
    8. DECRYPTION BY PASSWORD ='password'

    Specifies the password that is required to decrypt the database master key that is being imported from a file.

    1. ENCRYPTION BY PASSWORD ='password'
    Specifies the password that is used to encrypt the database master key after it has been loaded into the database.
    

• Restore the backed-up certificate by executing below commands.
  1. USE Master;
  2. OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'
  4. CREATE CERTIFICATE <CertificateName>
  5. FROM FILE = 'Location\CertName.cer' WITH PRIVATE KEY ( FILE = 'Location\CertKeyName.key', DECRYPTION BY PASSWORD = 'encryption_password');
  6. GO
  7. Where PASSWORD = 'Password’ is the password used for encrypting the master key in the previous step.
  8. and PASSWORD = 'encryption_password' is the password that is used to decrypt the private key and is same that was used while encrypting during back-up of the certificate.

• Alter the Master key to add encryption by service master key.
  1. USE master
  2. OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'
  3. ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
  4. GO
  6. Where PASSWORD = 'Password' is the password used to encrypt the master key.

• Restoring Encrypted Database from Primary to Secondary node
  
  
  • Take a full backup and transactional backup of TestTDEInAO from Primary node. Recommended is to take backup in multi file mode.
  • On Secondary node same backup should be restored in NO RECOVERY mode

Bring Database in Availability Group

Now, we have encrypted database on primary as well as secondary servers. Now, we will bring these servers back in Availability Group by using TSQL.

• Add Database on Primary node to Availability Group
  • Refer below script to add the existing database in AG
    1. ALTER AVAILABILITY GROUP MyAvailabilityGroup ADD DATABASE TestTDEInAO;
    2. GO

• Join Secondary node Database to Availability Group
  • Refer below script to join the secondary server database to the AG
    1. ALTER DATABASE TestTDEInAO SET HADR AVAILABILITY GROUP = MyAvailabilityGroup;
    2. GO

Once the database syncing is completed, you will have achieved your goal.!!!!