How to enable TDE Encryption on a database in an Availability Group

By default, the Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support databases that are already encrypted:  see Encrypted Databases with AlwaysOn Availability Groups (SQL Server).

If you have a database that is already encrypted, it can be added to an existing Availability Group – just not through the wizard.   You’ll need to follow the procedures outlined in Manually Prepare a Secondary Database for an Availability Group.

This article discusses how TDE encryption can be enabled for a database that already belongs to an Availability Group.   After a database is already a member of an Availability Group, the database can be configured for TDE encryption but there are some key steps to do in order to avoid errors.

To follow the procedures outlined in this article you need:

1. An AlwaysOn Availability Group with at least one Primary and one Secondary replica defined.
2. At least one database in the Availability Group.
3. A Database Master Key on all replica servers (primary and secondary servers)
4. A Server Certificate installed on all replica instances (primary and all secondary replicas).

 

For this configuration, there are two servers:  

SQL1 – the primary replica instance,  and

SQL2 – the secondary replica instance.

Step One:  Verify each replica instance has a Database Master Key (DMK) in Master – if not, create one.

To determine if an instance has a DMK, issue the following query:

 USE MASTER
GO
SELECT * FROM sys.symmetric_keys 
      WHERE name = '##MS_DatabaseMasterKey##'

If a record is returned, then a DMK exists and you do not need to create one, but if not, then one will need to be created. To create a DMK, issue the following TSQL on each replica instance that does not have a DMK already:

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Mhl(9Iy^4jn8hYx#e9%ThXWo*9k6o@';

Notes:

• If you query the sys.symmetric_keys without a filter, you will notice there may also exist a “Service Master Key” named:   ##MS_ServiceMasterKey## .   The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.  For more information regarding the Service Master Key (SMK), please refer to the following article:  Service Master Key.  We will not need to concern ourselves with the SMK in this article.
• If the DMK already exists and you do not know the password, that is okay as long as the service account that runs SQL Server has SA permissions and can open the key when it needs it (default behavior).   For more information refer to the reference articles at the end of this post.
• You do not need to have the exact same database master key on each SQL instance.   In other words, you do not need to back up the DMK from the primary and restore it onto the secondary.   As long as each secondary has a DMK then that instance is prepared for the server certificate(s).
• If your instances do not have DMKs and you are creating them, you do not need to have the same password on each instance.   The TSQL command, CREATE MASTER KEY, can be used on each instance independently with a separate password.   The same password can be used, but the key itself will still be different due to how our key generation is done.
• The DMK itself is not used to encrypt databases – it is used simply to encrypt certificates and other keys in order to keep them protected.  Having different DMKs on each instance will not cause any encryption / decryption problems as a result of being different keys.

 

Step Two:  Create a Server Certificate on the primary replica instance.

To have a Database Encryption Key (DEK) that will be used to enable TDE on a given database, it must be protected by a Server Certificate.  To create a Server Certificate issue the following TSQL command on the primary replica instance (SQL1):

 USE MASTER
GO
CREATE CERTIFICATE TDE_DB_EncryptionCert
WITH SUBJECT = 'TDE Certificate for the TDE_DB database'
  

To validate that the certificate was created, you can issue the following query:

 SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates

which should return a result set similar to:

The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key.   A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed. 

 

Step Three:  Back up the Server Certificate on the primary replica instance.

Once the server certificate has been created, it should be backed up using the BACKUP CERTIFICATE TSQL command (on SQL1):

 USE MASTER
BACKUP CERTIFICATE TDE_DB_EncryptionCert
TO FILE = 'TDE_DB_EncryptionCert'
WITH PRIVATE KEY (FILE = 'TDE_DB_PrivateFile',
ENCRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')

The BACKUP CERTIFICATE command will create two files.   The first file is the server certificate itself.   The second file is a “private key” file, protected by a password.  Both files and the password will be used to restore the certificate onto other instances.

When specifying the filenames for both the server certificate and the private key file, a path can be specified along with the filename.  If a path is not specified with the files, the file location where Microsoft SQL Server will save the two files is the default “data” location for databases defined for the instance.   For example, on the instance used in this example, the default data path for databases is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA”.

 

Note:

If the server certificate has been previously backed up and the password for the private key file is not known, there is no need to panic.   Simply create a new backup by issuing the BACKUP CERTIFICATE command and specify a new password.   The new password will work with the newly created files (the server certificate file and the private key file).

 

Step Four:  Create the Server Certificate on each secondary replica instance using the files created in Step 3.

The previous TSQL command created two files – the server certificate (in this example: “TDE_DB_EncryptionCert”) and the private key file (in this example: “TDE_DB_PrivateFile”)  The second file being protected by a password.

These two files along with the password should then be used to create the same server certificate on the other secondary replica instances.

After copying the files to SQL2, connect to a query window on SQL2 and issue the following TSQL command:

 CREATE CERTIFICATE TDE_DB_EncryptionCert
FROM FILE = '<path_where_copied>\TDE_DB_EncryptionCert'
WITH PRIVATE KEY 
(   FILE = '<path_where_copied>\TDE_DB_PrivateFile',
    DECRYPTION BY PASSWORD = 't2OU4M01&iO0748q*m$4qpZi184WV487')
  

This installs the server certificate on SQL2.  Once the server certificate is installed on all secondary replica instances, then we are ready to proceed with encrypting the database on the primary replica instance (SQL1).

 

Step Five:  Create the Database Encryption Key on the Primary Replica Instance.

On the primary replica instance (SQL1) issue the following TSQL command to create the Database Encryption Key.

 USE TDE_DB2
go
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_DB_EncryptionCert

The DEK is the actual key that does the encryption and decryption of the database.  When this key is not in use, it is protected by the server certificate (above).  That is why the server certificate must be installed on each of the instances.    Because this is done inside the database itself, it will be replicated to all of the secondary replicas and the TSQL does not need to be executed again on each of the secondary replicas.

At this point the database is NOT YET encrypted – but the thumbprint identifying the server certificate used to create the DEK has been associated with this database.  If you run the following query on the primary or any of the secondary replicas, you will see a similar result as shown below:

 SELECT db_name(database_id), encryption_state, 
    encryptor_thumbprint, encryptor_type 
    FROM sys.dm_database_encryption_keys

 

Notice that TempDB is encrypted and that the same thumbprint (i.e. Server Certificate) was used to protect the DEK for two different databases.   The encryption state of TDE_DB2 is 1, meaning that it is not encrypted yet.

 

Step Six:  Turn on Database Encryption on the Primary Replica Instance (SQL1)

We are now ready to turn on encryption.   The database itself as a database encryption key (DEK) that is protected by the Server Certificate.   The server certificate has been installed on all replica instances.   The server certificate itself is protected by the Database Master Key (DMK) which has been created on all of the replica instances.   At this point each of the secondary instances is capable of decrypting (or encrypting) the database, so as soon as we turn on encryption on the primary, the secondary replica copies will begin encrypting too.

To turn on TDE database encryption, issue the following TSQL command on the primary replica instance (SQL1):

 ALTER DATABASE TDE_DB2 SET ENCRYPTION ON

To determine the status of the encryption process, again query sys.dm_database_encryption_keys :

 SELECT db_name(database_id), encryption_state, 
    encryptor_thumbprint, encryptor_type, percent_complete
    FROM sys.dm_database_encryption_keys

When the encryption_state = 3, then the database is encrypted. It will show a status of 2 while the encryption is still taking place, and the percent_complete will show the progress while it is still encrypting. If the encryption is already completed, the percent_complete will be 0.

 

At this point, you should be able to fail over the Availability Group to any secondary replica and be able to access the database without issue.

 

What happens if I turn on encryption on the primary replica but the server certificate is not on the secondary replica instance?

The database will quit synchronizing and possibly report “suspect” on the secondary.   This is because when the SQL engine opens the files and begins to read the file, the pages inside the file are still encrypted.   It does not have the decryption key to decrypt the pages.  The SQL engine will think the pages are corrupted and report the database as suspect.  You can confirm this is the case by looking in the error log on the secondary.   You will see error messages similar to the following:

 

2014-01-28 16:09:51.42 spid39s Error: 33111, Severity: 16, State: 3.

2014-01-28 16:09:51.42 spid39s Cannot find server certificate with thumbprint '0x48CE37CDA7C99E7A13A9B0ED86BB12AED0448209'.

2014-01-28 16:09:51.45 spid39s AlwaysOn Availability Groups data movement for database 'TDE_DB2' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.

2014-01-28 16:09:51.56 spid39s Error: 3313, Severity: 21, State: 2.

2014-01-28 16:09:51.56 spid39s During redoing of a logged operation in database 'TDE_DB2', an error occurred at log record ID (31:291:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

 

The error messages are quite clear that the SQL engine is missing a certificate – and it’s looking for a specific certificate – as identified by the thumbprint.  If there is more than one server certificate on the primary, then the one that needs to be installed on the secondary is the one whose thumbprint matches the thumbprint in the error message.

The way to resolve this situation is to go back to step three above and the back up the certificate from SQL1 (whose thumbprint matches) and then create the server certificate on SQL2 as outlined in step four.   Once the server certificate exists on the secondary replica instance (SQL2), then you can issue the following TSQL command on the secondary (SQL2) to resume synchronization:

 

 ALTER DATABASE TDE_DB2 SET HADR RESUME

References

• Encrypted Databases with AlwaysOn Availability Groups (SQL Server)

•  Manually Prepare a Secondary Database for an Availability Group
  

•  Transparent Data Encryption (TDE)
  

•  Move a TDE Protected Database to Another SQL Server
  

•  SQL Server and Database Encryption Keys (Database Engine)
  

•  DMV - sys.dm_database_encryption_keys (Transact-SQL)
  

•  DMV - sys.symmetric_keys (Transact-SQL)
  

•  DMV – sys.certificates (Transact-SQL)
  

•  Service Master Key
  

•  SQL Server Certificates and Asymmetric Keys
  

Comments

• Anonymous
  November 28, 2015
  Thanks for the Article and good one.

• Anonymous
  December 23, 2016
  Thanks for the detailed steps. Very helpful.

• Anonymous
  May 04, 2017
  Great Article and nicely detailed.

• Anonymous
  September 26, 2017
  I am about to enable TDE on an Iaas Always On and thank you for sharing . Good Job!!

• Anonymous
  October 23, 2017
  Detailed explanation with all the steps. A descriptive article. Thanks for sharing.