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.
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!
