Ahmad Yaseen

How to configure SQL Server mirroring on a TDE encrypted database

February 19, 2016 by

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:

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

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:

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.

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:

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

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.

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

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:

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:

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

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

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:

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):

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:

On the TSTIN2 server:

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:

On the TSTIN2 server:

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:

On the TSTIN2 server:

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.

Useful links


Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is Senior SQL Server DBA at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
SQL Server security

About Ahmad Yaseen

Ahmad Yaseen is Senior SQL Server DBA at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

1,641 Views
  • George Johnson

    Worked perfectly except that I had to put the master key top auto open on the target server as the mirroring would not start properly until I did this. SQL command : ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    Cheers!