Are there any specific problems that need to be taken into consideration when enabling Transparent Data Encryption (TDE) on a database that is published with a type of replication?
The answer to the question above is no, other than ensuring certificates match on all databases that are involved in the replication landscape to retain the security needs for why TDE is being used.
TDE is file level encryption. This meaning, TDE encrypts and decrypts at the disk level where the data and log files are retained. Replication is managed in a combination of the system databases and the user database level and in reality, has no awareness of the file storage. This is the same when we would discuss any type of replication such as, Peer-to-peer, Transactional or Merge. This can even go further into topics such as mirroring, availability groups and backup and restore.
To further go over implementing TDE for a database that is being replicated with Transactional Replication in SQL Server, take the following situation.
A database, DBA, exists on a master server. This database is a repository for secondary ETL processing that pushes data into several tables within the DBA database from source systems. The DBA database is under a great deal of pressure from the ETL processing so there is a limited standard set for what can be placed upon it for additional reporting. A table exists in the DBA database, ShippingDelays that you have a requirement to report on for delivery failures in the business. In order to report on this table, you have to move the data from the DBA.ShippingDelays table into a secondary reporting database engine. After careful consideration, Transactional Replication was implemented to handle this data movement and has been working extremely well for a long period of time. It has later been found that order numbers fall into a category of data that must be secured further with encryption. TDE was chosen but the question has been asked if it will break replication upon implementing TDE.
The problem is identified and relates directly to the question we asked in the first sentence of this article. The solution has also been found but a proof of concept is required. Let’s show the steps that are needed to implement TDE in a database while other features are present, such as replication.
The complete solution is illustrated below.
Transactional Replication has been setup between the DBA Operational Data Store and the Reporting Server.
Note – the instance connections in SSMS are using CNAME entries for better understanding as it relates to the diagram shown earlier.
There are only a few steps that need to be taken to enable TDE.
- Stop all replication agents – this is not completely required but no transactions should be distributed when you turn TDE on.
- Create a master key on the publisher database server
- Create a certificate on the publisher server
- Backup the certificate
- Create a database encryption key based on the certificate in step 3
- Copy the certificate backup files to the subscriber server
- Create a master key on the subscriber server
- Create a certificate based on the backup file of the publisher certificate
- Create a database encryption key
- Start replication objects
To show these steps, we will take the replication setup from earlier. As stated, stop replication agents prior to moving on. This can be done by connecting to the publisher instance and stopping them as they are listed in the SQL Agent jobs tree
Connect to the publisher instance and create a master key and certificate. Make a backup of the certificate immediately after the certificate creation.
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password$1'
GO
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'Password$1'
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'TDECert'
WITH PRIVATE KEY
(
FILE = 'TDECertFile',
ENCRYPTION BY PASSWORD = 'Password$1'
);
GO
The above will place two files in the DATA directory for the SQL Server instance. In the case of the setup in this article, that location is
C:Program FilesMicrosoft SQL ServerMSSQL11.SHAREPOINT2013MSSQLDATA
Copy the two files from the publisher server to the subscriber server. Place them in the DATA directory for ease of the T-SQL command that needs to be written. If you do want to place them in another directory, you can. Simply use the full path in the statement so the command knows where to find them. Also, ensure the SQL Server service account has access to that directory.
Once the backup files are copied, turn on database encryption for the DBA database. This is performed by using the CREATE DATABASE ENCRYPTION KEY and ALTER DATABASE commands.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO
ALTER DATABASE DBA
SET ENCRYPTION ON;
GO
At this point, the DBA database on the publisher has TDE enabled and is being encrypted on the file level. Before starting replication, we need to ensure the subscriber is performing the same tasks, with the same certificate.
Connect to the subscriber and create a master key and a certificate. Create the certificate based on the file copied earlier from the publisher’s certificate.
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'Password$1'
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'TDECert'
WITH PRIVATE KEY
(
FILE = 'TDECertFile',
DECRYPTION BY PASSWORD = 'Password$1'
);
GO
Next, create a database encryption key and enable the database for encryption.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO
ALTER DATABASE DBA
SET ENCRYPTION ON;
GO
At this point, replication can be started again. Perform a basic test to ensure data is moving through replication as expected. In the ShippingDelays table, there is a composite key made of the four columns in the table – SalesOrderID, OrderDate, DueDate, and ShipDate.
Given the table structure, the below statement can be used to put a small load on the table and replication.
DECLARE @ID INT = 1013
WHILE @ID <= 10000
BEGIN
INSERT INTO ShippingDelays
SELECT @ID, GETDATE(), GETDATE(), GETDATE()
SET @ID += 1
END
Monitor the distribution of the commands and transactions with replication monitor
Connect to the subscriber and query the table to ensure transactions are being committed.
As shown, data is moving as it should from the DBA Operational Data Store to the Reporting Server for the article (table) ShippingDelays.
Summary
As shown in this proof of concept, enabling TDE on a database that was previously setup with replication, posed no major problems in the replication process from functioning. Taking the steps to ensure the keys match ensures security is maintained on both publisher and subscriber. Although this article could have simply been answered with a few short paragraphs, showing and proving a process like this is a critical step to verification as well as, learning how to manage the entire structure that has been setup. With different table structures on the subscriber, TDE may not be needed but always remember, if TDE was utilized, ensure the movement of that data maintains the level of security when it moves through the data architecture. Also, take care in the data that is stored in the distribution database as it may be a security risk as well.
