Mirroring with TDE

Recently I’ve had to set up a lot of database mirroring, and about half of those databases were encrypted. Setting up mirroring on unencrypted databases is normally a quick process, but encrypted mirroring requires a bit more work. While there are many examples of mirroring out there, it can be hard to find an example of encrypted mirroring that is not missing a step or two. You cannot use Object Explorer to do everything, so below are the necessary scripts to not only set up encrypted mirroring, but also how to setup Transparent Data Encryption.

1. Create and Backup Master Key and Certificate TDE requires a master key and a certificate, so those need to be created first and then immediately backed up. Backing these up is best practice, but the files will also need to be restored to the secondary server. If you already have TDE on the primary server, you can skip this step and go straight to Step 3.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

/********** On Principal **********/ --Create Master Key USE MASTER GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StongP@ssword' GO --Backup Master Key BACKUP MASTER KEY TO FILE = 'FilePath\MasterKey.KEY' ENCRYPTION BY PASSWORD = 'StrongP@ssword' GO --Create Certificate CREATE CERTIFICATE CertName WITH SUBJECT = 'CertDesc', EXPIRY_DATE = '1/1/2900' GO --Backup Cert; using a different key name than the original to avoid errors BACKUP CERTIFICATE CertName TO FILE = 'FilePath\CertName.CER' WITH PRIVATE KEY (FILE='FilePath\CertKey.KEY', ENCRYPTION BY PASSWORD = 'StrongP@ssword') GO CLOSE MASTER KEY GO

2. Enable Transparent Data Encryption The next step is to enable TDE using the new certificate, and then turn TDE on. If TDE has been enabled previously, you should have already skipped to Step 3.

1 2 3 4 5 6 7 8 9 10 11

--Enable TDE on database USE DbName GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertName GO --Turn TDE On ALTER DATABASE DbName SET ENCRYPTION ON GO

3. Restore Key and Certificate to Secondary This is your first step if you already have TDE turned on. Just make sure you copy the backup files to the secondary server so you can restore them.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

/********** ON MIRROR **********/ --Restore Master key to Secondary server USE MASTER GO RESTORE MASTER KEY FROM FILE = 'FilePath\MasterKey.KEY' DECRYPTION BY PASSWORD = 'StrongP@ssword' ENCRYPTION BY PASSWORD = 'StrongP@ssword' GO --Restore the Certificate to Secondary server OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword' GO CREATE CERTIFICATE CertName FROM FILE = 'FilePath\CertName.CER' WITH PRIVATE KEY ( FILE = 'FilePath\CertKey.key', DECRYPTION BY PASSWORD = 'StrongP@ssword') GO CLOSE MASTER KEY GO

4. Create Endpoints on Principal and Secondary You can do this at any time that makes sense to you, but it makes sense to me to ensure all the settings are up to date before moving onto the mirroring setup. Encrypted mirroring requires an encrypted endpoint (seems logical enough) so notice the specified encryption algorithm in the script below. Many other mirroring examples leave this critical piece out.

1 2 3 4 5 6 7 8

/********** ON MIRROR & Principal **********/ --Create Mirroring Endpoint on Principal & Secondary CREATE ENDPOINT Name STATE = STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=SUPPORTED ALGORITHM AES) GO

5. Backup Database on Principal You will need to perform a full backup along with a Log backup on the database you plan to mirror. There is nothing special about the backup, just make sure that you have the latest log backups and that you finish before the next scheduled backup starts, otherwise you will have to restore another log. I hate having to do that.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

/********** On Principal **********/ --Backup Primary Database - Full BACKUP DATABASE [DbName] TO DISK = 'FilePath\DbNameFULL.BAK' WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10, NAME = 'DbFullBackup' GO --Backup Primary Database - Log BACKUP LOG [DbName] TO DISK = 'FilePath\DbNameLOG.TRN' WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10, NAME = 'DbLogBackup' GO

6. Restore Backup to Secondary Restoring the backups for an encrypted database is only slightly more involved than normal. You will have to Open the Master Key before the restore, otherwise it will fail.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

/********** ON MIRROR **********/ --Restore the Full Backup on the Secondary USE MASTER GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword' --Don't forget this! RESTORE DATABASE [DbName] FROM DISK = 'FilePath\DbNameFULL.BAK' WITH NORECOVERY,NOUNLOAD,STATS=10 GO --Restore the Log Backup on the Secondary RESTORE LOG [DbName] FROM DISK = 'FileName\DbNameLOG.TRN' WITH NORECOVERY,NOUNLOAD,STATS=10 GO CLOSE MASTER KEY --Remember to close the master key once complete GO

7. Enable Mirroring on Secondary Once the database is in place, you may want to verify that the secondary database is in a Restoring state. This is required to mirror successfully. The scripts I’ve provided will restore correctly, but restoring the database improperly is one of the most common and embarrassing issues you’ll encounter while trying to turn on mirroring. Set the partner by using the fully qualified server name or by the IP address. Theoretically either will work, but it depends on your network.

The most important thing in the script below is to include the line to Add Encryption by Service. This can be done earlier and in a separate script, but I find it easiest to include while turning on mirroring. If you leave this step out, you’ll receive a misleading error after you run the script in Step 8 that will leave you depressed and frustrated because it won’t tell you anything useful. Something like:

Msg 1416, Level 16, State 31
Database "DbName" is not configured for database mirroring

So make sure you run the scripts with all the necessary lines!

1 2 3 4 5 6 7 8 9 10 11 12

/********** ON MIRROR **********/ --Enable Encrypted Mirroring on Secondary USE MASTER GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY --Most Important step!! ALTER DATABASE DbName SET PARTNER = 'TCP://PrincipalServer:5022' GO CLOSE MASTER KEY GO

8. Enable Mirroring on Principal This is the moment of truth. Did you follow all the steps correctly? If so, you should be fine. If you receive a database mirroring is not configured error, refer back to Step 7 and verify that encryption was added when you enabled mirroring. There is also a chance you will need to do another log restore to bring the log chain up to date. That’s purely based on how often you do log backups and how big the database is though.

1 2 3 4 5 6 7 8

/********** On Principal **********/ --Enable Encrypted Mirroring on Principal USE MASTER GO ALTER DATABASE DbName SET PARTNER = 'TCP://SecondaryServer:5022' GO

Summary Setting up Encrypted Mirroring is not overly difficult, but there are some quirks to it, and you can easily forget an integral step. I always refer to saved scripts to keep track of where I am in the process, and to make the overall setup faster.

Advertisements