How to configure SQL Server mirroring on a TDE encrypted database February 19, 2016 by Ahmad Yaseen 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: 123456 USE MASTERGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kEn@n$951';GO The created Master Key information can be viewed by querying the sys.symmetric_keys system table as follows: 123456 USE masterGOSELECT 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: 1234567 USE masterOPEN MASTER KEY DECRYPTION BY PASSWORD = 'kEn@n$951';BACKUP MASTER KEY TO FILE = 'F:\MSSQL\Backup\MasterKeyBack' ENCRYPTION BY PASSWORD = 'kEn@n$951';GO 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. 12345 CREATE CERTIFICATE SQLShackDemoCert WITH SUBJECT = 'SQLShackDemoCert',START_DATE = '2/17/2016',EXPIRY_DATE='2/16/2021';GO 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: 12345678910 BACKUP CERTIFICATE SQLShackDemoCert TO FILE = 'F:\MSSQL\Backup\SQLShackDemoCert'WITH PRIVATE KEY ( FILE = 'F:\MSSQL\Backup\SQLShackDemoCertPrivate', ENCRYPTION BY PASSWORD = 'Kind@$753');GO Once the certificate and the backup completed successfully, you can view the certificate information by querying the sys.certificates system table as below: 12345 USE masterGOSELECT 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. 12345678 USE SQLShackDemoGOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE SQLShackDemoCertGO We reach now to the last step in configuring the SQL TDE in SQLShackDemo database, which is enabling the encryption on it as follows: 12345 ALTER DATABASE SQLShackDemoSET ENCRYPTION ONGO 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: 12345678 SELECT db_name(database_id), EncryptionState =CASE encryption_stateWHEN 1 THEN 'Unencrypted'WHEN 3 THEN 'Encrypted'ENDFROM 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: 1234567 RESTORE MASTER KEY FROM FILE = 'J:\MSSQL\DATA\MasterKeyBack' DECRYPTION BY PASSWORD = 'kEn@n$951' ENCRYPTION BY PASSWORD = 'kEn@n$951';GO Now we will start creating the server certificate, but first we should open the Master Key: 123 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: 1234567 CREATE CERTIFICATE SQLShackDemoCertFROM FILE = 'J:\MSSQL\DATA\SQLShackDemoCert' WITH PRIVATE KEY (FILE = 'J:\MSSQL\DATA\SQLShackDemoCertPrivate', DECRYPTION BY PASSWORD = 'Kind@$753');GO 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: 1234567891011 BACKUP DATABASE [SQLShackDemo] TO DISK = N'F:\MSSQL\Backup\SQLShackDemo.bak' WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO BACKUP LOG [SQLShackDemo] TO DISK = N'F:\MSSQL\Backup\SQLShackDemo.trn' WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO 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): 1234567891011 USE [master]RESTORE DATABASE [SQLShackDemo] FROM DISK = N'F:\MSSQL\Backup\SQLShackDemo.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 GO RESTORE LOG [SQLShackDemo] FROM DISK = N'F:\MSSQL\Backup\SQLShackDemo.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10GO 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: 1234567 CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 60002 ) FOR DATABASE_MIRRORING (ROLE=PARTNER);GO On the TSTIN2 server: 1234567 CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT = 60004 ) FOR DATABASE_MIRRORING (ROLE=PARTNER);GO 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: 123 GRANT CONNECT ON ENDPOINT::Mirroring TO [domain\TSTIN2$]; On the TSTIN2 server: 123 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: 123 ALTER DATABASE [SQLShackDemo] SET PARTNER = 'TCP://TSTIN2.aramex.com:60004' On the TSTIN2 server: 123 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. About Latest Posts Ahmad YaseenAhmad 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 blogsView all posts by Ahmad Yaseen Latest posts by Ahmad Yaseen (see all) SQL Server Auditing Best Practices - May 23, 2019 How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File - May 15, 2019 Perform a SQL Server Audit using ApexSQL Audit - May 15, 2019 Related posts: How to configure Transparent Data Encryption (TDE) in SQL Server Is SQL Server Always Encrypted, for sensitive data encryption, right for your environment How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL How to add a TDE encrypted user database to an Always On Availability Group What is SQL Server database mirroring?