How to configure SQL Server mirroring on a TDE encrypted database

Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.

SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.

The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.

SQL Server Transparent Data Encryption

SQL Server Transparent Data Encryption (TDE), is an encryption mechanism, available in the Enterprise edition of SQL Server 2008 and later. It is used to protect the database physical files, rather than protecting the data itself. This includes the database data, log, backup and snapshot files as mentioned previously. Using the SQL Server Transparent Data Encryption, unauthorized users will not be able to access the database’s data, by preventing them from attaching or restoring the database files to another SQL instance. Also it will be protected inside the backup media if it is stolen.

The SQL TDE encryption, as the name indicates, transparent to the applications that connects to the database, as the encryption is at the page level. The data is encrypted while it is at rest on the disk and decrypted during the read process on its way to the memory. An authorized user will be able to access the encrypted database content, without being aware that the database is encrypted or any action or extra code required from his side.

Encrypting any user database using TDE, the TempDB system database will be encrypted. As this database contains temp data from the TDE-Encrypted database

Encrypting a SQL Server database using TDE is a straightforward operation. First a master key should be created. Then you should create a certificate which is protected by that master key. After that a database encryption key (DEK) secured by the certificate is created in order to protect the user database. Finally you need to enable the encryption in your database.

The dependency of the database encryption key that is secured by the certificate which is protected by the master key prevents the database files from being restored or shown outside the current instance without these keys. Also, this encryption protect the backup files from being opened by the text editors to view its content.

If you decide, for any valid reason, to restore the TDE-Encrypted user database to another SQL Server Instance, you need to have a copy of the same master key on that new instance, in addition to the certificate backup files and certificate private key.

SQL Server TDE is a light encryption method that will not affect the queries performance, as the encryption is at the database files level. On the other hand, using the TDE, the database backup files will not take benefit from the backup compression feature completely.

The scenario

After the brief introduction about the SQL Server Transparent Data Encryption (TDE), and in order to simulate creating SQL Server Mirroring site on a database with TDE enabled on it, we will assume the below scenario:

We have two SQL Server 2014 Enterprise instances.
The first SQL Server is TSTIN1 hosts the SQLShackDemo database and will act as the Principal server in the mirroring site.
The second SQL Server is TSTIN2 and will act as the Mirrored server.
SQLShackDemo recovery model is FULL.
SQL Server Transparent Data Encryption will be enabled on the SQLShackDemo database.
A SQL Server disaster recovery site will be created using SQL Server mirroring between the TSTIN1 and TSTIN2 SQL Servers.

The implementation:

We will start by enabling TDE on the SQLShackDemo database hosted in the TSTIN1 SQL Server.

The first step is creating the Master Key. The Master Key will be created on the master system database using the CREATE MASTER KEY statement, and should be encrypted using a complex password as the T-SQL script below:

USE MASTER

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kEn@n$951';

The created Master Key information can be viewed by querying the sys.symmetric_keys system table as follows:

USE master

SELECT name, key_length,key_algorithm ,algorithm_desc ,create_date

from sys.symmetric_keys

This Master Key will be used on the mirrored server in order to restore our user database. So we need to back up the key in order to use it there:

USE master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'kEn@n$951';

BACKUP MASTER KEY TO FILE = 'F:\MSSQL\Backup\MasterKeyBack'

ENCRYPTION BY PASSWORD = 'kEn@n$951';

Once the Master Key is created and backed up successfully, we will create the server certificate, which is encrypted by the previously created Master Key. The certificate will be created using the CREATE CERTIFICATE statement, specifying the start and expiration date.

CREATE CERTIFICATE SQLShackDemoCert WITH SUBJECT = 'SQLShackDemoCert'

,START_DATE = '2/17/2016',EXPIRY_DATE='2/16/2021';

As we will copy this certificate to the mirrored server, we will take backup for this certificate, and this backup will be encrypted with a private key as in the following script:

BACKUP CERTIFICATE SQLShackDemoCert

TO FILE = 'F:\MSSQL\Backup\SQLShackDemoCert'

WITH PRIVATE KEY

(

FILE = 'F:\MSSQL\Backup\SQLShackDemoCertPrivate',

ENCRYPTION BY PASSWORD = 'Kind@$753'

);

Once the certificate and the backup completed successfully, you can view the certificate information by querying the sys.certificates system table as below:

USE master

SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date FROM sys.certificates where name = 'SQLShackDemoCert'

Until this stage, the Master Key and the Certificate are ready on the master database level. We can now simply create the database encryption key in order to encrypt the SQLShackDemo user database using the CREATE DATABASE ENCRYPTION KEY statement, and this DEK will be encrypted by the previously created certificate.

USE SQLShackDemo

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE SQLShackDemoCert

We reach now to the last step in configuring the SQL TDE in SQLShackDemo database, which is enabling the encryption on it as follows:

ALTER DATABASE SQLShackDemo

SET ENCRYPTION ON

As mentioned early in the TDE introduction, once the user database encrypted, the TempDB system database will be encrypted too. To make sure that the databases are encrypted, we will query the sys.dm_database_encryption_keys system object as follows:

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 1 THEN 'Unencrypted'

WHEN 3 THEN 'Encrypted'

END

FROM sys.dm_database_encryption_keys

Here we go, our database SQLShackDemo is completely encrypted using the SQL Server Transparent Data Encryption.

Our requirement here, is to configure the SQL Server mirroring on the SQLShackDemo database between the TSTIN1 and TSTIN2 SQL Servers, taking into consideration that the database is TDE enabled.

In order to achieve this, we should restore the Master Key to the TSTIN2 server from the backup we took from the TSTIN1 server.

After coping the Master Key and Certificate backup files to the TSTIN2 server, connect to the TSTIN2 SQL Server instance and restore the Master Key using RESTORE MASTER KEY statement as below:

RESTORE MASTER KEY

FROM FILE = 'J:\MSSQL\DATA\MasterKeyBack'

DECRYPTION BY PASSWORD = 'kEn@n$951'

ENCRYPTION BY PASSWORD = 'kEn@n$951';

Now we will start creating the server certificate, but first we should open the Master Key:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'kEn@n$951';

Once the Master Key is opened, we will create the certificate from the certificate backup files that are copied from the first server:

CREATE CERTIFICATE SQLShackDemoCert

FROM FILE = 'J:\MSSQL\DATA\SQLShackDemoCert'

WITH PRIVATE KEY (FILE = 'J:\MSSQL\DATA\SQLShackDemoCertPrivate',

DECRYPTION BY PASSWORD = 'Kind@$753');

Till this stage, the same server encryption of the TSTIN1 server applied to the TSTIN2 server. So we can start the mirroring configuration normally by taking full backup of the SQLShackDemo database from the TSTIN1, and then taking LOG backup for this database as follows:

BACKUP DATABASE [SQLShackDemo] TO DISK = N'F:\MSSQL\Backup\SQLShackDemo.bak'

WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

BACKUP LOG [SQLShackDemo] TO DISK = N'F:\MSSQL\Backup\SQLShackDemo.trn'

WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

After copying the .bak and .trn backup files from the TSTIN1 server to the TSTIN2 one, we will restore these backup files with the option (NORECOVERY):

USE [master]

RESTORE DATABASE [SQLShackDemo] FROM DISK = N'F:\MSSQL\Backup\SQLShackDemo.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

RESTORE LOG [SQLShackDemo] FROM DISK = N'F:\MSSQL\Backup\SQLShackDemo.trn'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

If you try to restore the backup files on the second server without restoring the Master Key and Certificate, the following error will be shown:

After restoring the database backup files, we will create the SQL Server Mirroring Endpoints using the CREATE ENDPOINT statement.

On the TSTIN1 server:

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 60002 )

FOR DATABASE_MIRRORING (ROLE=PARTNER);

On the TSTIN2 server:

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 60004 )

FOR DATABASE_MIRRORING (ROLE=PARTNER);

Assuming that the SQL Server instances in our example are running with LOCAL SYSTEM account, we need to grant CONNECT permission for the SQL Service account on the other server’s Endpoints.

On the TSTIN1 server:

GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\TSTIN2$];

On the TSTIN2 server:

GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\TSTIN1$];

The Endpoints are created and the SQL Service accounts granted connect access to these endpoint, now we will define the mirroring principal and mirrored partners to complete the mirroring setup process:

On the TSTIN1 server:

ALTER DATABASE [SQLShackDemo] SET PARTNER = 'TCP://TSTIN2.aramex.com:60004'

On the TSTIN2 server:

ALTER DATABASE [SQLShackDemo] SET PARTNER = 'TCP://TSTIN1.aramex.com:60002'

Refresh the databases on the SQL Server Management Studio, and you will find that the SQL Server Mirroring is configured successfully on both the Principal and Mirrored SQL instances.

Conclusion:

SQL Server Transparent Data Encryption is a good SQL encryption feature on the database physical files level. It will not affect the server’s performance and no action required from the application side to handle the communication with the TDE encrypted database. Encrypting a SQL database with TDE is straightforward process with few accurate steps. Encrypting a database with TDE will not prevent you from configuring the SQL Server Mirroring on this database. Just you need to make sure to restore the Master Key and Certificate files from the principal server to the mirrored one, then you can proceed with the mirroring normally. SQL Server Mirroring traffic is encrypted too, so the data will be protected in its way.

Author
Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

SQL Server Transparent Data Encryption

The scenario

The implementation:

Conclusion:

Related posts: