Configure SQL Server Database Mirroring on TDE Enabled Databases

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!

Advertisements

6 thoughts on “Configure SQL Server Database Mirroring on TDE Enabled Databases

  1. I have a quick question: at point 13) I am a bit perplexed… about:
    RESTORE DATABASE SharePoint_Config FROM DISK … database should be Test…? could you please explain, I could have missed the point… for me, I would want to keep the mirrored database as the source database… I would be very grateful if you explain. Thank you in advance.

    Like

  2. Thanks for reading my blogs.
    The database name must be test(based on the example in the blog) as it’s database mirroring so both the source and destination names should be same.

    I didn’t get this question :- “for me, I would want to keep the mirrored database as the source database .” Could you please give me more details about your requirement. 🙂

    Like

  3. Hello and thank you very much for answering. What I meant was, for the following:

    RESTORE DATABASE SharePoint_Config FROM DISK = ‘’ WITH …

    could be:

    RESTORE DATABASE Test FROM DISK = ‘’ WITH … ?

    Thank you very much again.

    Like

  4. Hi,

    I was followed the above instructions and Configure Database Mirroring on TDE Enabled Databases .i got the below error message when i was starts mirroring .

    Error message :

    Alter failed for DatabaseEncryptionKey ‘Microsoft.SqlServer.Management.Smo.ObjectKeyBase’. (Microsoft.SqlServer.Smo)
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Database ‘Test’ cannot be opened. It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

    Can you help me with this ….

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s