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 a SQL Server database administration leader 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 a SQL Server database administration leader 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,994 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!

  • Lavanya Saripalli

    HI Ahmad, Your article is very good and helped me set up mirroring on TDE enabled databases_big thankyou. But I have a weird error message. On a couple databases , The full database backup restores ,but when it comes to transaction log to restore it throws me error saying server thumbprint not found. I cant understand why a full db restores while log doesn’t. Please let me know if you may have any pointers to this.

    • Ahmad Zuhair Yaseen

      Thank you Lavanya for your comment here.

      Please arrange to check these small point:
      – The Full backup and TRN backup both taken from the database with TDE already enabled on it.
      – Take both backups and copy it to the destination.
      – Try to restore using T-SQL commands rather than the GUI.
      – Make sure to locate the certificate files in the data path or the path specified in the script that is used to create the certificate at the destination.
      – Try to put the backup files in the same path as the certificate files.

      Please check the SQL error log if you still face the issue and provide me with that error.

      Best Regards,
      Ahmad

      • Lavanya Saripalli

        HI Ahmad, Thank you for replying back to my message! I was already doing all the steps but hte last one. Now made fresh backups on primary with TDE enabled, copied over to the files and certificates to the same location on mirror and tried restoring the databases. Still the full backup restores but the .trn throws the error message.
        Here is the message.

        Msg 33111, Level 16, State 3, Line 17
        Cannot find server certificate with thumbprint ‘0x6FE3584FDCFCC3EEADA380BD3BCA361C7FD4B1AD’.
        Msg 3013, Level 16, State 1, Line 17
        RESTORE LOG is terminating abnormally.

        The error log also shows the same cannot find server certificate message.

      • Lavanya Saripalli

        Hi Ahmad, Thank you for responding back to me so quickly. I am already doing all the steps 1 through 4.
        SO now I tried putting the backup files in the same path as the certificate files and still get the same error message. The full backup restores successfully only the log throws the error. the error message is the same in the error log also.Here is the message.
        Msg 33111, Level 16, State 3, Line 27
        Cannot find server certificate with thumbprint ‘0x6FE3584FDCFCC3EEADA380BD3BCA361C7FD4B1AD’.
        Msg 3013, Level 16, State 1, Line 27
        RESTORE LOG is terminating abnormally.

        • Ahmad Zuhair Yaseen

          Please arrange to do the below:
          – Check the TDE information on both the Primary and Secondary servers using the DMVs listed in the article, and make sure that all us correct.
          – What is the SQL version used on both servers?
          – Make sure that you opened the master key on the secondary for use as below:
          OPEN MASTER KEY DECRYPTION BY PASSWORD =’……’
          ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
          GO

          • Lavanya Saripalli

            HI Ahmad,
            Thank you for getting back to me.Just replying so in future may help someone. I was doing this on a 2016 SQL server SP1 . Turns out the only issue was that there were bugs unrelated to TDE but Mirroring yes. So I had to patch my servers to the latest CU and then it started working perfectly. and Yes I had to use the above OPEN and ALTER command too. Thank you again.

          • Ahmad Zuhair Yaseen

            Many thanks for your valuable input.