Get Free Tips
MSSQLTips.com
Home » Configuring TDE for SQL Server Always On Availability Groups
Availability Groups

Configuring TDE for SQL Server Always On Availability Groups

written by Edwin Sarmiento 0 comments

Problem

In a previous tip on Implementing Transparent Data Encryption in SQL Server 2008, we’ve seen how we can configure Transparent Data Encryption (TDE) with SQL Server 2008 databases. I wanted to implement TDE on my SQL Server 2012 databases and join them in an Availability Group. How do I do it?

Solution

Transparent Data Encryption (TDE) performs real-time I/O encryption and decryption of the data and log files, thereby, protecting data at rest. You can use TDE with Availability Groups to provide security and high availability to your databases. However, when working with TDE-enabled databases, you cannot use the New Availability Group Wizard or Add Database to Availability Group Wizard to add them to an Availability Group as shown in the screenshot below.

Transparent Data Encryption (TDE) performs real-time I/O encryption and decryption of the data and log files, thereby, protecting data at rest.

This tip will walk you thru the process of enabling TDE on your databases and joining them to an Availability Group. The list below outlines the high-level process to perform this task.

  1. Configure SQL Server TDE on the primary and secondary replica
  2. Initialize the encrypted SQL Server database on the secondary replica
  3. Configure the SQL Server Availability Group

And, since you have very limited options to use the wizards to perform all of these tasks, we will be using T-SQL for all of them.

Configure SQL Server TDE on the primary and secondary replica

We will start by enabling TDE on the primary replica. This process is very similar to the one outlined in this tip. In a new Query Editor window,

  1. Run the following command to create the service master key in the master database. Remember to document the password used for encrypting the service master key and keep it in a secure place.
    <span style="color: blue;">USE MASTER  <br></span><span style="color: black;">GO  <br></span><span style="color: blue;">CREATE MASTER KEY ENCRYPTION BY </span><span style="color: black;">PASSWORD </span><span style="color: blue;">= </span><span style="color: red;">'mYC0mpl3XP@$$w0rd' <br></span><span style="color: black;">GO <br></span>
  2. Run the following command to create a certificate to encrypt the database encryption keys on the TDE-enabled databases. This certificate will be protected by the service master key.
    <span style="color: blue;">CREATE </span><span style="color: black;">CERTIFICATE TDECert <br></span><span style="color: blue;">WITH </span><span style="color: black;">SUBJECT </span><span style="color: blue;">= </span><span style="color: red;">'My TDE Certificate for all user database'</span>
  3. Run the following command to create a database encryption key on the user database that you will join to the Availability Group, protecting it using the certificate. You need to repeat this process for all of the databases that you will join to the Availability Group.
    <span style="color: blue;">USE </span><span style="color: black;">[SampleDB]  <br>GO  <br></span><span style="color: blue;">CREATE DATABASE ENCRYPTION KEY  <br>WITH </span><span style="color: black;">ALGORITHM </span><span style="color: blue;">= </span><span style="color: black;">AES_128  <br></span><span style="color: blue;">ENCRYPTION BY </span><span style="color: black;">SERVER CERTIFICATE TDECert <br>GO </span>
  4. Run the following command to turn on TDE on the database.
    <span style="color: blue;">ALTER DATABASE </span><span style="color: black;">[SampleDB] <br></span><span style="color: blue;">SET ENCRYPTION ON</span>
  5. Run the following command to backup the certificate to a file. We will use this to enable TDE on the secondary replica. Remember to document the password used for encrypting the certificate file and keep it in a secure place.
    <span style="color: blue;">USE MASTER  <br></span><span style="color: black;">GO  <br></span><span style="color: blue;">BACKUP </span><span style="color: black;">CERTIFICATE TDECert   <br></span><span style="color: blue;">TO FILE = </span><span style="color: red;">'C:\TDECert_File.cer'  <br></span><span style="color: blue;">WITH </span><span style="color: black;">PRIVATE </span><span style="color: blue;">KEY </span><span style="color: gray;">(</span><span style="color: blue;">FILE = </span><span style="color: red;">'C:\TDECert_Key.pvk' </span><span style="color: gray;">,  <br></span><span style="color: blue;">ENCRYPTION BY </span><span style="color: black;">PASSWORD </span><span style="color: blue;">= </span><span style="color: red;">'mYC0mpl3XP@$$w0rd' </span><span style="color: gray;">)  <br></span><span style="color: black;">GO</span>
  6. Copy the certificate file to the secondary replica.

Once TDE has been enabled on the databases in the primary replica, we can now proceed to enable it on the secondary replica. The reason why we are configuring TDE first on the secondary replica prior to joining the databases to an Availability Group is to make sure that we can perform data initialization. This is done by restoring the backups of the TDE-enabled databases to the secondary replica as highlighted in this previous tip.

To enable TDE on the secondary replica,

  1. Run the following command to create the service master key in the master database. Remember to document the password used for encrypting the service master key and keep it in a secure place.
    <span style="color: blue;">USE MASTER  <br></span><span style="color: black;">GO  <br></span><span style="color: blue;">CREATE MASTER KEY ENCRYPTION BY </span><span style="color: black;">PASSWORD </span><span style="color: blue;">= </span><span style="color: red;">'mYC0mpl3XP@$$w0rd' <br></span><span style="color: black;">GO </span>
  2. Run the following command to create a certificate to encrypt the database encryption keys on the TDE-enabled databases. This certificate will be based on the file we exported from the primary replica and copied to the secondary replica. Thus, we need to provide the password we used to encrypt it initially. This certificate will be protected by the service master key.
    <span style="color: blue;">USE MASTER  <br></span><span style="color: black;">GO  <br></span><span style="color: blue;">CREATE </span><span style="color: black;">CERTIFICATE TDECert  <br></span><span style="color: blue;">FROM FILE = </span><span style="color: red;">'C:\TDECert_File.cer'   <br></span><span style="color: blue;">WITH </span><span style="color: black;">PRIVATE </span><span style="color: blue;">KEY </span><span style="color: gray;">(</span><span style="color: blue;">FILE = </span><span style="color: red;">'C:\TDECert_Key.pvk'</span><span style="color: gray;">,   <br></span><span style="color: black;">DECRYPTION </span><span style="color: blue;">BY </span><span style="color: black;">PASSWORD </span><span style="color: blue;">=  </span><span style="color: red;">'mYC0mpl3XP@$$w0rd'</span><span style="color: gray;">);</span>

Once the service master key and the database master key has been configured on the secondary replica, we can now test the functionality by initializing the data in preparation for joining the databases to the Availability Group.

Initialize the encrypted SQL Server database on the secondary replica

The following steps outline the process of initializing the databases in preparation for the Availability Group configuration. The process involves taking a FULL and LOG backup of the databases from the primary replica and restoring it in NORECOVERY mode on the secondary replica.

  1. On the primary replica, run the following command to take FULL and LOG backups.
    <span style="color: blue;">BACKUP DATABASE </span><span style="color: black;">SampleDB <br></span><span style="color: blue;">TO DISK = </span><span style="color: red;">'E:\DBBackups\SampleDB.Bak' <br>   </span><span style="color: blue;">WITH </span><span style="color: black;">INIT</span><span style="color: gray;">, </span><span style="color: black;">STATS</span><span style="color: gray;">, ; <br></span><span style="color: black;">GO <br></span><span style="color: blue;">BACKUP </span><span style="color: magenta;">LOG </span><span style="color: black;">SampleDB <br></span><span style="color: blue;">TO DISK = </span><span style="color: red;">'E:\DBBackups\SampleDB.trn' <br>   </span><span style="color: blue;">WITH </span><span style="color: black;">INIT</span><span style="color: gray;">, </span><span style="color: black;">STATS</span><span style="color: gray;">, ; <br></span><span style="color: black;">GO </span>

    Do this for all the databases that will be migrated to the new environment.

  2. Copy the generated FULL and LOG backups to the secondary replica.
  3. On the secondary replica, run the following command to restore the FULL and LOG backup. It is recommended to store the database files in the same location as that of the primary replica.
    <span style="color: blue;">RESTORE DATABASE </span><span style="color: black;">[SampleDB]  <br></span><span style="color: blue;">FROM DISK = </span><span style="color: red;">N'E:\DBBackups\SampleDB.Bak'  <br></span><span style="color: blue;">WITH </span><span style="color: black;">NORECOVERY</span><span style="color: gray;">, </span><span style="color: black;">STATS </span><span style="color: blue;">= </span><span style="color: black;">5 <br>GO   <br></span><span style="color: blue;">RESTORE </span><span style="color: magenta;">LOG </span><span style="color: black;">[SampleDB]  <br></span><span style="color: blue;">FROM DISK = </span><span style="color: red;">N'E:\DBBackups\SampleDB.trn'  <br></span><span style="color: blue;">WITH </span><span style="color: black;">NORECOVERY</span><span style="color: gray;">, </span><span style="color: black;">STATS </span><span style="color: blue;">= </span><span style="color: black;">5 <br>GO  </span>
  4. Do this for all the databases that will be migrated to the new environment.

Note that if this process fails, verify that the certificate exported from the primary replica has been imported on the secondary replica prior to restoring the backups.

Configure the SQL Server Availability Group

Once the databases have been initialized on the secondary replica, we can proceed to configure an Availability Group and join the TDE-enabled database to it. The following steps outline the process for creating and configuring the Availability Group on the primary replica. We will be using the following information to configure the Availability Group.

  • SQL Server service account: TESTDOMAIN\sqlservice (used by both primary and secondary replicas)
  • Availability Group Name: AGTest
  • Availability Group Listener Name: AGTestLN
  • Availability Group Listener IP Address: 172.16.0.116
  • Availability Group Listener Port Number: 1433
  • Availability Group Replicas: WS-ALWAYSON-AG1, WS-ALWAYSON-AG2

On the primary replica, run the following commands to perform the following steps:

  1. Create login that will be used to connect to the endpoint. For simplicity’s sake, we will use the SQL Server service account for this purpose
  2. Create the endpoint that the Availability Group will connect to
  3. Grant CONNECT permissions on the login to the endpoint
  4. Create the Availability Group, configuring the name, endpoint URL, replicas, databases, replication mode, and failover mode
  5. Create the Availability Group Listener Name
<span style="color: green;">--On the PRIMARY REPLICA <br>--a) Create login that will be used to connect to the endpoint <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">CREATE </span><span style="color: black;">LOGIN [TESTDOMAIN\sqlservice] </span><span style="color: blue;">FROM </span><span style="color: black;">WINDOWS <br>GO <br> <br></span><span style="color: green;">--b) Create endpoint  <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">CREATE ENDPOINT </span><span style="color: black;">HADR_ENDPOINT <br>    STATE</span><span style="color: blue;">=</span><span style="color: black;">STARTED  <br>    </span><span style="color: blue;">AS </span><span style="color: black;">TCP </span><span style="color: gray;">(</span><span style="color: black;">LISTENER_PORT</span><span style="color: blue;">=</span><span style="color: black;">5022</span><span style="color: gray;">)  <br>    </span><span style="color: blue;">FOR </span><span style="color: black;">DATA_MIRRORING </span><span style="color: gray;">(</span><span style="color: black;">ROLE</span><span style="color: blue;">=</span><span style="color: gray;">ALL, </span><span style="color: blue;">ENCRYPTION = </span><span style="color: black;">REQUIRED ALGORITHM AES</span><span style="color: gray;">) <br></span><span style="color: black;">GO <br> <br></span><span style="color: green;">--c) Grant CONNECT permissions on the login to the endpoint <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">GRANT </span><span style="color: black;">CONNECT </span><span style="color: blue;">ON </span><span style="color: black;">ENDPOINT::HADR_ENDPOINT </span><span style="color: blue;">TO </span><span style="color: black;">[TESTDOMAIN\sqlservice]</span><span style="color: gray;">;   <br></span><span style="color: black;">GO  <br> <br></span><span style="color: green;">--d) Create the availability group, [AGTest]  <br></span><span style="color: blue;">CREATE </span><span style="color: black;">AVAILABILITY </span><span style="color: blue;">GROUP </span><span style="color: black;">[AGTest]  <br>   </span><span style="color: blue;">FOR  <br>      DATABASE </span><span style="color: black;">[SampleDB]</span><span style="color: gray;"> </span><span style="color: black;"> <br>   REPLICA </span><span style="color: blue;">ON  <br>      </span><span style="color: red;">'WS-ALWAYSON-AG1' </span><span style="color: blue;">WITH  <br>         </span><span style="color: gray;">( <br>         </span><span style="color: black;">ENDPOINT_URL </span><span style="color: blue;">= </span><span style="color: red;">'TCP://WS-ALWAYSON-AG1.TESTDOMAIN.local:5022'</span><span style="color: gray;">,  <br>         </span><span style="color: black;">AVAILABILITY_MODE </span><span style="color: blue;">= </span><span style="color: black;">SYNCHRONOUS_COMMIT</span><span style="color: gray;">,</span><span style="color: black;">FAILOVER_MODE </span><span style="color: blue;">= </span><span style="color: black;">AUTOMATIC <br>         </span><span style="color: gray;">), <br>      </span><span style="color: red;">'WS-ALWAYSON-AG2' </span><span style="color: blue;">WITH  <br>         </span><span style="color: gray;">( <br>         </span><span style="color: black;">ENDPOINT_URL </span><span style="color: blue;">= </span><span style="color: red;">'TCP://WS-ALWAYSON-AG2.TESTDOMAIN.local:5022'</span><span style="color: gray;">, <br>         </span><span style="color: black;">AVAILABILITY_MODE </span><span style="color: blue;">= </span><span style="color: black;">SYNCHRONOUS_COMMIT</span><span style="color: gray;">, </span><span style="color: black;">FAILOVER_MODE </span><span style="color: blue;">= </span><span style="color: black;">AUTOMATIC <br>         </span><span style="color: gray;">);  <br></span><span style="color: black;">GO <br> <br></span><span style="color: green;">--e) Create the Availability Group Listener Name with virtual IP address <br></span><span style="color: gray;">AND </span><span style="color: black;">port number <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br> <br></span><span style="color: blue;">ALTER </span><span style="color: black;">AVAILABILITY </span><span style="color: blue;">GROUP </span><span style="color: black;">[AGTest] <br></span><span style="color: blue;">ADD </span><span style="color: black;">LISTENER </span><span style="color: red;">N'AGTestLN' </span><span style="color: gray;">( <br></span><span style="color: blue;">WITH </span><span style="color: black;">IP <br></span><span style="color: gray;">((</span><span style="color: red;">N'172.16.0.116'</span><span style="color: gray;">, </span><span style="color: red;">N'255.255.255.0'</span><span style="color: gray;">) <br>) <br>, </span><span style="color: black;">PORT</span><span style="color: blue;">=</span><span style="color: black;">1433</span><span style="color: gray;">); <br></span><span style="color: black;">GO  </span>

Once the Availability Group has been created on the primary replica, we can proceed to add the secondary replica. On the secondary replica, run the following commands to perform the following steps:

  1. Create login that will be used to connect to the endpoint. For simplicity’s sake, we will use the SQL Server service account for this purpose
  2. Create the endpoint that the Availability Group will connect to
  3. Grant CONNECT permissions on the login to the endpoint
  4. Join the secondary replica to the Availability Group
  5. e. Join the databases to the Availability Group
<span style="color: green;">--On the SECONDARY REPLICA <br>--a) Create login that will be used to connect to the endpoint <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">CREATE </span><span style="color: black;">LOGIN [TESTDOMAIN\sqlservice] </span><span style="color: blue;">FROM </span><span style="color: black;">WINDOWS <br>GO <br> <br></span><span style="color: green;">--b) Create endpoint  <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">CREATE ENDPOINT </span><span style="color: black;">HADR_ENDPOINT <br>    STATE</span><span style="color: blue;">=</span><span style="color: black;">STARTED  <br>    </span><span style="color: blue;">AS </span><span style="color: black;">TCP </span><span style="color: gray;">(</span><span style="color: black;">LISTENER_PORT</span><span style="color: blue;">=</span><span style="color: black;">5022</span><span style="color: gray;">)  <br>    </span><span style="color: blue;">FOR </span><span style="color: black;">DATA_MIRRORING </span><span style="color: gray;">(</span><span style="color: black;">ROLE</span><span style="color: blue;">=</span><span style="color: gray;">ALL, </span><span style="color: blue;">ENCRYPTION = </span><span style="color: black;">REQUIRED ALGORITHM AES</span><span style="color: gray;">) <br></span><span style="color: black;">GO <br> <br></span><span style="color: green;">--c) Grant CONNECT permissions on the login to the endpoint <br></span><span style="color: blue;">USE </span><span style="color: black;">[master] <br>GO <br></span><span style="color: blue;">GRANT </span><span style="color: black;">CONNECT </span><span style="color: blue;">ON </span><span style="color: black;">ENDPOINT::HADR_ENDPOINT </span><span style="color: blue;">TO </span><span style="color: black;">[TESTDOMAIN\sqlservice]</span><span style="color: gray;">;   <br></span><span style="color: black;">GO  <br> <br></span><span style="color: green;">--d) Join the replica to the existing Availability Group, AGTest <br></span><span style="color: blue;">ALTER </span><span style="color: black;">AVAILABILITY </span><span style="color: blue;">GROUP </span><span style="color: black;">[AGTest] </span><span style="color: blue;">JOIN</span><span style="color: gray;">; <br></span><span style="color: black;">GO <br> <br></span><span style="color: green;">--e) Join the databases to the existing Availability Group, AGTest <br></span><span style="color: blue;">ALTER DATABASE </span><span style="color: black;">[SampleDB] </span><span style="color: blue;">SET </span><span style="color: black;">HADR AVAILABILITY </span><span style="color: blue;">GROUP = </span><span style="color: black;">[AGTest]</span><span style="color: gray;">;  <br></span><span style="color: black;">GO </span>

Testing the SQL Server Availability Group Failover

After the Availability Group has been created and configured, failover needs to be tested. This is the ultimate test to validate if, indeed, both TDE and the Availability Group work well in the given configuration. Failover of the Availability Group can be done via the Fail Over Availability Group Wizard from SQL Server Management Studio, similar to the screenshot below

After the Availability Group has been created and configured, failover needs to be tested

Or via the ALTER AVAILABILITY GROUP command.

Next Steps

Copyright (c) 2006-2025 Edgewood Solutions, LLC All rights reserved
sql server 2025 guide