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!
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.
LikeLike
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. 🙂
LikeLike
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.
LikeLike
Yes, that’s feasible!
LikeLike
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 ….
LikeLike
Could you please tell me which step it failed on ?
LikeLike