Recently, I was working on a scenario where we were trying to mirror the TDE enabled(encrypted) database. The configuration seemed to be fine but still we were seeing the errors like:

2012-05-08 14:54:37.73 spid35s     Error: 15581, Severity: 16, State: 3.
2012-05-08 14:54:37.73 spid35s     Please create a master key in the database or open the master key in the session before performing this operation.
2012-05-08 14:54:37.78 spid34s     Error: 1443, Severity: 16, State: 2.
2012-05-08 14:54:37.78 spid34s     Database mirroring has been terminated for database ‘test’.
This is an informational message only. No user action is required.
2012-05-08 14:56:16.80 spid21s     Error: 1474, Severity: 16, State: 1.

we were trying to configure mirroring using scripts.  After checking the configuration in detail, we found out we were missing on a very small step.

In this blog I will discuss step by step configuration for database mirroring with TDE enabled database. I will also mention the most common mistake which DBAs do (marked in Red in the text below)

Here are the steps:

1) Use Master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Password’;
Go

2) Next we will create a certificate to encrypt the DEK.

CREATE CERTIFICATE Test  WITH SUBJECT = ‘TDE Certificate’
Go

3) Confirm if  “pvt_key_encryption_type_desc” column of the sys.certificates catalog view has a value of ENCRYPTED_BY_MASTER_KEY.

Select * from sys.certificates

4) Create database

Create database test

5) To enable TDE, we need DEK at the database level.

Use test

Create DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE test

–Now we have the DEK in place and we can enable TDE for database test

ALTER DATABASE test SET ENCRYPTION ON

6)  Now we have to take the backups which we will restore on the mirrored server:

Use master

Backup master key to file = ‘C:MasterKey’ encryption by password = ‘Password’

7) BACKUP CERTIFICATE test  TO FILE = ‘D:Test.cert’

WITH PRIVATE KEY ( FILE = ‘D:Test.cert’ ,

ENCRYPTION BY PASSWORD = ‘Password’

);

Now moving further to the mirrored server

7) Restore master key from file = ‘D:MasterKey’
Decryption by password = ‘Password’
Encryption by password = ‘Password’

8)  OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘Password’

9) Here is the step where we missed and is very important step:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

10)  CREATE CERTIFICATE Test

FROM FILE = ‘D:Test.cert’

WITH PRIVATE KEY (FILE = ‘D:Test.cert’,

DECRYPTION BY PASSWORD = ‘Password’);

11)  –Create a mirroring endpoint for server instance by using the certificate

On principal :

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP
( LISTENER_PORT=5024 , LISTENER_IP = ALL )
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE <MASTER_HostA_cert>
, ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL ); GO

On mirrored instance :

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP
( LISTENER_PORT=5024 , LISTENER_IP = ALL )
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE <HOST_HostB> ,
ENCRYPTION = REQUIRED ALGORITHM RC4 , ROLE = ALL ); GO

12 ) USE master;
–Ensure that Test database uses the full recovery model.

ALTER DATABASE Test SET RECOVERY FULL;
GO
USE Test
BACKUP DATABASE Test TO DISK = ‘<c:Test.bak>’ WITH FORMAT
GO
BACKUP Log Test TO DISK = ‘<c:Test_log.bak>’ WITH FORMAT
GO

13) Copy the backups to the mirrored server and run the command:

RESTORE DATABASE SharePoint_Config FROM DISK = ‘<c:Test.bak>’ WITH NORECOVERY
GO
RESTORE log Test FROM DISK = ‘<c:Test_log.bak>’ WITH NORECOVERY
GO

14)  on Principal server, please run (name of the mirror server with FQDN):

ALTER DATABASE Test SET PARTNER = ‘<TCP://mirror.Testit.com:5024>’;
GO

15)  on mirror server , please run (name of the Principal server with FQDN):

ALTER DATABASE Test SET PARTNER = ‘<TCP://principal.testit.com:5024>’;
GO

Here you, Mirroring is configured on the server.

HTH!