Database Mirroring with Transparent Data Encryption and You

I was presented with a challenge at my place of work. We are in need of using TDE to cover ourselves and our data, we also needed to setup database mirroring to keep inline with our DR/BC model. So I was tasked with making these two things work with one another.

Now I have successfully gotten both technologies to work before, so the challenge was how to get them to work together. Below is what I have figured out.

Steps taken and scripts written were extrapolated from http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/31/setting-up-database-mirroring-in-sql-server-2008-using-t-sql-when-the-database-is-encrypted-using-transparent-data-encryption.aspx

This website served as the foundation of the TDE+Mirroring, but the author left out key steps that I had to scrounge to fill in.

Overview:

Setup TDE on Principle DB

Restore TDE keys/certs from Principle DB to Mirror DB

Setup Mirroring on Mirrored DB

Setup Mirroring on Principle DB

Below are the individual steps I have taken to setup TDE and Mirroring.

The steps are setup so that the t-SQL (in italics) is on top with an explanation underneath (in bold).

On Principle:

USE MASTER

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomePassword';

go

Creating the “master key” for the SQL server that houses the principle database, make sure that the password is a strong one

-------------------------------------------------------------------------------------------------------------------

On Principle:

USE master;

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘SomePassword’;

BACKUP MASTER KEY TO FILE = ‘Location\MasterKeyName.key' ENCRYPTION BY PASSWORD = 'SomePassword';

GO

Right after creating the “master key” we issue the command to back that up to a safe location. Most commands that you issue on the principle database or master key, will have to be prefaced with opening the master key by the password.

-------------------------------------------------------------------------------------------------------------------

On Principle:

USE Master

CREATE CERTIFICATE CertName WITH SUBJECT = ‘CertFriendlyName’, EXPIRY_DATE = '3500-Jan-01';

Go

Here we are creating a certificate that will sign the keys, with no expiration date.

-------------------------------------------------------------------------------------------------------------------

On Principle:

BACKUP CERTIFICATE CertName TO FILE = ‘Location\CertName.cer'

          WITH PRIVATE KEY ( FILE = 'Location\CertKeyName.key', ENCRYPTION BY PASSWORD = 'SomePassword');

GO

Again, right after creating the cert we are backing it up. We also create yet again another key this time to encrypt the cert. Make sure that the key for the cert is NOT the same key as the “master key”, the code will fail and not with a helpful error.

-------------------------------------------------------------------------------------------------------------------

On Principle:

USE DatabaseName

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertName;

GO

This step creates the database encryption key using the cert we created above. You can change the type of encryption; just check Books Online for options.

-------------------------------------------------------------------------------------------------------------------

On Principle:

USE master;

GO

ALTER DATABASE DatabaseName SET ENCRYPTION ON

GO

So finally we turn TDE on for the principle database.

We now turn our efforts to the server that will host the mirrored database.

On Mirror:

USE master

RESTORE MASTER KEY

    FROM FILE = 'Location\MasterKeyName.key'

    DECRYPTION BY PASSWORD = 'SomePassword'

    ENCRYPTION BY PASSWORD = 'SomePassword';

GO

We are restoring the backed-up master key file that we created on the principle server to the mirror server

-------------------------------------------------------------------------------------------------------------------

On Mirror:

USE Master;

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'

CREATE CERTIFICATE CertName   

FROM FILE = 'Location \CertName.cer' WITH PRIVATE KEY ( FILE = 'Location \CertKeyName.key', DECRYPTION BY PASSWORD = 'SomePassword');

GO

We are restoring the cert to the mirror, using the cert name and passwords that were created on the principle.

-------------------------------------------------------------------------------------------------------------------

On Mirror:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

GO

This is my favorite part. This little three line snippet took me 3 days to figure out. I found it in the comments of the website linked above. Here we are altering the master key with the service master key of the mirrored server.

-------------------------------------------------------------------------------------------------------------------

Now that we have all of the TDE stuff in place on both the principle and the mirror, we need to backup the principle db, and restore it, and then enable mirroring!

On Principle:

USE DatabaseName

GO

BACKUP DATABASE DatabaseName

TO DISK = 'Location\DatabaseName_FULL.bak'

          WITH COMPRESSION,

                    NAME = 'Full Backup of DatabaseName';

GO

--T-Log Backup of TDE

USE DatabaseName

GO

BACKUP LOG DatabaseName

TO DISK = 'Location\DatabaseName_LOG.trn'

          WITH COMPRESSION,

               NAME = 'Log backup of DatabaseName’

GO

The backing-up of the principle database is standard full backup and transaction log backup.

-------------------------------------------------------------------------------------------------------------------

On Mirror:

USE MASTER

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'

RESTORE DATABASE DatabaseName

   FROM disk = 'Location\DatabaseName_FULL.bak'

      WITH NORECOVERY,

       REPLACE,

       MOVE 'LogicalDatabaseName' TO 'Location\DatabaseName.mdf',

      REPLACE,

      MOVE 'LogicalDatabaseLogName' TO 'Location\DatabaseName.ldf'

CLOSE MASTER KEY

GO

So when performing the restore make sure that you open the master key, then close it when done. If you don’t know the “logical” names of the files, right-click on the database name, then select “files”, look at the first column for the “logical” names of the .mdf and .ldf files. Also make sure that you specify “WITH NO RECOVERY”, because we want to restore the transaction log after this.

-------------------------------------------------------------------------------------------------------------------

On Mirror:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'

RESTORE LOG TDE

          FROM DISK = 'Location\DatabaseName_LOG.trn'

          WITH NORECOVERY;

CLOSE MASTER KEY

GO

For database mirroring to work, you must specify to restore the log backup “WITH NORECOVERY” the database has to be in a restoring state to initialize mirroring.

-------------------------------------------------------------------------------------------------------------------

Now on to the setting up of the database mirroring

On Principle/Mirror:

USE MASTER

SELECT * FROM sys.database_mirroring_endpoints

GO

This command will display the endpoints that are already on the server.

-------------------------------------------------------------------------------------------------------------------

On Principle/Mirror:

DROP ENDPOINT EndPointName

Issuing this command will drop the end point if need be.

-------------------------------------------------------------------------------------------------------------------

As a rule, I look for any existing endpoints to make sure that I am not duplicating any names.

On Principle/Mirror:

CREATE ENDPOINT EndPointName

          STATE = STARTED

          AS TCP ( LISTENER_PORT = PortNumber )

    FOR DATABASE_MIRRORING (

                   ROLE = PARTNER

                   );

GO

For my endpoint port number I use 7025 on the principle, and 7022 for the mirror.

-------------------------------------------------------------------------------------------------------------------

 

On Principle/Mirror:

SELECT dme.endpoint_id

,dme.name

,dme.principal_id

,dme.state_desc

,dme.role_desc

,dme.connection_auth_desc

,dme.certificate_id

,dme.encryption_algorithm_desc

,te.port

,te.ip_address

FROM sys.database_mirroring_endpoints dme

INNER JOIN sys.tcp_endpoints te

 ON dme.endpoint_id = te.endpoint_id AND te.type = 4

GO

This will check to make sure that the endpoints are started. This command was pulled from the comments section of the link posted above

-------------------------------------------------------------------------------------------------------------------

After both endpoints are created and started, it is time to issue the commands to start the mirroring.

On Mirror:

USE MASTER

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'

ALTER DATABASE Databasename

SET PARTNER = 'TCP://PrincipleServerName.Domain:PortNumber'

GO

CLOSE MASTER KEY

GO

As a rule, I always issue the “start” code on the mirror to connect to the principle first. The connection has to be the FQDN, eg, server.domain.local etc… I am not sure if the open/close master key is needed, but it doesn’t error out.

-------------------------------------------------------------------------------------------------------------------

On Principle:

USE MASTER

ALTER DATABASE DatabaseName

SET PARTNER = 'TCP:// MirrorServerName.Domain:PortNumber'

GO

This command will start database mirroring on the principle.

-------------------------------------------------------------------------------------------------------------------

So, after issuing this code on the principle server, you should have TDE and Mirroring all setup. To check, refresh the database list in SSMS, the databases should read “Principle, Synchronized” and “Mirror, Synchronized/Restoring”

You can right click on the principle database, and choosing tasks-> mirroring monitor to check it out!