TDE and Always On
To configure the TDE on the TDE_Test database, we should first create a master key in the master database. Only one master key can be created per each SQL Server instance. All user databases that are hosted in the same SQL instance, and have the TDE enabled on it, will share the dependency upon the same master key. The below CREATE MASTER KEY T-SQL statement is used to create the master key under the master database, and this master key will be encrypted by a complex password. It is better to keep a backup of that password in a secure place:
| USE master GO CREATE MASTER KEY ENCRYPTIONBY PASSWORD=’Az_Y@89|87′; GO |
After creating the master key successfully, the next step now is to create the Certificate that will be used to encrypt the Database Encryption Key. The Certificate will be protected by the previously created master key. The Certificate can be created using the CREATE CERTIFICATE T-SQL statement below:
| CREATE CERTIFICATE TDE_AVGWITH SUBJECT=’TDE_on_AVG_test’; GO |
To make sure that the certificate is created successfully, you can query the sys.certificate system object for all certificates that are created under the current SQL Server instance as below:
| SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_dateFROM sys.certificates; |
The result will show us that the TDE_AVG certificate is created successfully, encrypted by the master key and valid for one year only:
The Certificate is ready now, so we will start working at the database side. We will use the CREATE DATABASE ENCRYPTION KEY T-SQL command to create the TDE_Test database encryption key that is encrypted by the TDE_AVG certificate created previously, specifying the encryption algorithm, which is the Advanced Encryption Standard (AES) in our case with a 128 bit key length as follows:
| USE TDE_Test GO CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE TDE_AVG; GO |
The last step in configuring the TDE is to turn on the TDE encryption on the TDE_Test database using ALTER DATABASE … SET ENCRYPTION ON T-SQL command below:
| ALTER DATABASE TDE_TestSET ENCRYPTION ON;GO |
That is it! The Transparent Data Encryption is configured completely on the TDE_Test database. The sys.dm_database_encryption_keys DMV can be used to list all databases with TDE enabled on the databases:
| SELECT DB_NAME(database_id) AS DatabaseName ,Encryption_State AS EncryptionState ,key_algorithm AS Algorithm ,key_length AS KeyLengthFROM sys.dm_database_encryption_keys GO |
The result shows that the TDE is enabled on the TDE_Test database, and the value 3 for the database encryption state means that the database is completely Encrypted. But what about the tempdb? We have not configured the TDE on that database!
If you encrypt any user database using the Transparent Data Encryption, the physical files of the tempdb will be encrypted automatically, as it may contain data from the TDE enabled user database temporarily, while using temporary tables or cursors. In this way, we will maintain full protection for our data using the TDE feature. The operative point is that, the TDE encryption on the tempdb is transparent from that database users and will have a minimal performance impact on the current SQL instance.
As a best practice, it is better to back up the TDE certificate and the private key associated with that certificate after enabling the TDE on a database. In this way, you will be able to restore the database backup files or attach the database data files on another SQL Server instance. The backup can be performed by running the below BACKUP CERTIFICATE T-SQL command, which will backup both the certificate itself and its private key, providing a complex password to encrypt that private key as below:
| USE master GO BACKUP CERTIFICATE TDE_AVGTO FILE = ‘C:\TDE_Cert\TDE_AVG’WITH PRIVATE KEY (file=’C:\TDE_Cert\TDE_AVG_Pirvate’,ENCRYPTION BY PASSWORD=’Lm_s$$15_12′) |
The generated backup files can be viewed by browsing the path provided in the script or the SQL instance DATA path by default if you do not provide the path as follows:
At this point, the TDE_Test database is encrypted at the DB41VS SQL Server that acts as the primary replica in the AG40VS Always On Availability Group.
Now we need to add it to the Availability Group in order to replicate it to the DB42VS SQL Server. The Add Database Wizard and New Availability Group Wizard for AlwaysOn Availability Groups do not support adding databases that are already encrypted using the TDE encryption to an Availability Group. If you try to add the TDE_Test database, which is encrypted using TDE, by the Add Database Wizard, the wizard will show you that this is not applicable as the database contains an encryption key as below:
This means that we need to add the database to the Availability group manually. We need to perform full backup and transaction log backup for the TDE_Test database, copy it and restore it with NORECOVERY at the DB42VS secondary replica. But recall again that the TDE_Test database is encrypted using Transparent Data Encryption. If we try to restore the TDE_Test database backup to the DB42VS SQL Server, the restore process will fail getting the below error:
The error indicates that the TDE_Test database is encrypted using a database encryption key that is protected by a certificate, and this certificate is not available on that SQL Server instance. So that, in order to restore this encrypted database backup on a SQL Server instance, the certificate that encrypts the database encryption key should be created on that server. This is why we create a backup for the certificate and its private key when we enable the TDE on our database.
To do that, we should first create a master key on the secondary server that is encrypted by a complex password, using the CREATE MASTER KEY T-SQL command below:
| USE master GO CREATE MASTER KEY ENCRYPTIONBY PASSWORD = ‘Az_Y@89|87’ |
You have the choice to use the same password for the previous master key or use a new password, as the master key is used to encrypt the certificate not the database itself in order to keep it protected. In all cases, the key itself will be different due to how the key generation process is performed.
The next step is to copy the certificate and the private key backup files generated previously from the DB41VS primary replica to the DB42VS secondary server. You can keep it in the default data folder on the secondary serve, or in a custom folder where you should provide that bath in the CREATE CERTIFICATE T-SQL statement. The private key of the certificate should be decrypted by the same password that was used to encrypt it when the backup was created:
| CREATE CERTIFICATE TDE_AVG FROM FILE=’F:\TDE_Cert\TDE_AVG’ WITH PRIVATE KEY (FILE = ‘F:\TDE_Cert\TDE_AVG_Pirvate’, DECRYPTION BY PASSWORD=’Lm_s$$15_12′) |
Now the certificate that encrypts the TDE_Test database is available in the secondary replica. We will create a full back and transaction log backup from the TDE_Test database in the primary replica and copy it to the secondary replica:
| BACKUP DATABASE [TDE_Test] TO DISK = N’F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak’ WITH NOFORMAT, NOINIT, NAME = N’TDE_Test-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [TDE_Test] TO DISK = N’F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn’ WITH NOFORMAT, NOINIT, NAME = N’TDE_Test-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
On the primary replica we will add the database to the Availability Group using the ALTER AVAILABILITY GROUP… ADD DATABASE T-SQL command as follows:
| USE master GO ALTER AVAILABILITY GROUP AG40VS ADD DATABASE [TDE_Test] |
You can make sure that the database is added successfully to the Availability Group by expanding the AG40VS Availability Groups node in the DB41VS primary server and you will find the database under the Availability Databases node:
Once the backup files are copied to the secondary server, we will restore the full backup and the transaction log backup on that server using WITH NORECOVERY option:
| USE [master] RESTORE DATABASE [TDE_Test] FROM DISK = N’F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 GO RESTORE LOG [TDE_Test] FROM DISK = N’F:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\TDE_Test.trn’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO |
The restore process will complete successfully this time as the same certificate that encrypt the database encryption key is created on the secondary server. The database is available now on the secondary replica and waiting in RESTORING state.
At the secondary replica side, we need to join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL command below:
| USE master GO ALTER DATABASE TDE_Test SET HADR AVAILABILITY GROUP = AG40VS; |
To make sure that the TDE_Test database is replicated to the DB42VS SQL Server, expand the Databases node at the DB42VS SQL Server and you will see the TDE_Test database in synchronized state:
Comments