Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'.

I have setup TDE on both the principle and the mirror. The problem I have comes into play when I am setting up the mirroring of the two databases. Since I am using TDE I don't know of a way to setup mirroring via the gui, so I am forced to use t-sql to get the job done.

Below is the code I have used on the mirrored server

--Restore the full backup to the mirrored mdf and ldf
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE DATABASE TDE
   FROM disk = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
      WITH NORECOVERY,
       REPLACE,
       MOVE 'TDE' TO 'E:\TDE.mdf',
      REPLACE,
      MOVE 'TDE_log' TO 'G:\TDE.ldf'
CLOSE MASTER KEY 
GO

--Restore the log backup to the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE LOG TDE
    FROM DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
    WITH NORECOVERY;
CLOSE MASTER KEY
GO


--Drop/Create Mirroring endpoint on mirror
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7025 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER
        );
GO

--Check the endpoints for the mirror
USE MASTER
SELECT * FROM sys.database_mirroring_endpoints
GO

--Set the principle on the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://PRINCIPLE.DOMAIN.local:7022'
GO
CLOSE MASTER KEY
GO

Below is the code that I use on the principle server.

----------------------Mirroring Section----------------------------------

--Full Backup of Principle
USE TDE
GO
BACKUP DATABASE TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
    WITH COMPRESSION,
         NAME = 'Full Backup of TDE';
GO

---Log Backup of Principle
USE TDE
GO
BACKUP LOG TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
    WITH COMPRESSION,
         NAME = 'Log backup of TDE'
GO

--Drop/Create Mirroring endpoint on principle
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7022 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER
        );
GO

--Check the endpoints for the princple
USE master
select * from sys.database_mirroring_endpoints
GO

--Set the mirror db on the principle db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://MIRROR.DOMAIN.local:7025'
CLOSE MASTER KEY
GO

I setup the mirroring endpoint 1st, then the principle endpoint. I then issue the ALTER DATABASEon the mirror, then on the principle, where I get the resulting error:

 Msg 1416, Level 16, State 31, Line 2
Database "TDE" is not configured for database mirroring.

I am at a loss as to what to do about this. The mirror is in the "restoring" state, but I am certain that the error is talking about the principle db.

Thanks for any help you can give!

Update Code for the Principle TDE:

--Create Master Key in Master Database
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1Password';
PRINT 'created master key'
go

--Backing up the master key file
USE master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password';
BACKUP MASTER KEY TO FILE = '\\SERVERNAME\TDE_Master_Key.key' ENCRYPTION BY PASSWORD = '1Password';
GO

--Create Server Certificate in the Master Database encrypted with master key (created above) which would be used to create USER database encryption key.
USE Master
CREATE CERTIFICATE Cert_For_TDE WITH SUBJECT = 'Master_Cert_for_TDE', EXPIRY_DATE = '3500-Jan-01';
Go

--Backing up the server cert file
--USE master;
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SERVERNAME\TDE_Cert.cer' 
    WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', ENCRYPTION BY PASSWORD = '1Password');
GO

--Create user database key
USE TDE
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
GO

--Enabling Transparent Database Encryption for the USER Database
USE master;
GO
ALTER DATABASE TDE SET ENCRYPTION ON
GO

Code on mirror for TDE:

--restore the backed up key to the mirror
use master
RESTORE MASTER KEY
    FROM FILE = '\\SERVERNAME\TDE_Master_Key.key'
    DECRYPTION BY PASSWORD = '1Password'
    ENCRYPTION BY PASSWORD = '1Password';
GO

--restore the backed up cert to the mirror
USE Master;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
CREATE CERTIFICATE Cert_For_TDE    
FROM FILE = '\\SERVERNAME\TDE_Cert.cer' WITH PRIVATE KEY ( FILE = '\\SERVERNAME\TDE_Cert_Key.key', DECRYPTION BY PASSWORD = '1Password');
GO

Update2 sys.database_mirroring_endpoints joined with sys.tcp_endpoints on Principle show:

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65545   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7022    NULL

sys.database_mirroring_endpoints joined with sys.tcp_endpoints on Mirror show:

endpoint_id name    principal_id    state_desc  role_desc   connection_auth_desc    certificate_id  encryption_algorithm_desc   port    ip_address
65537   TDE 261 STARTED PARTNER NEGOTIATE   0   RC4 7025    NULL
share|improve this question
    
Is the database in full recovery model? Are there any log backup jobs running while you perform the set up? Did you backup the master key on the principal and restore it on the mirror for TDE? –  Robert L Davis Apr 6 '11 at 22:32
    
Yup, both dbs are in full recovery. No jobs running on it while I perform the backup( only 1 table with 4 rows) it takes 2 seconds. I have backed up the master key and restored it to the mirror. I'll post that code tomorrow when I get back to work. –  RateControl Apr 6 '11 at 23:18
    
I have added the TDE scripting, and some dmvs –  RateControl Apr 7 '11 at 14:02
add comment

1 Answer 1

up vote 6 down vote accepted

Found a website with a comment on it.

I added the code to just after where I restore the key and cert

--Mumbojumbo to get mirroring to work
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

It works like a charm, it makes a little sense that I had to encrypt the master key that I restored with the service master key of the new server. I guess.

shrug

share|improve this answer
1  
Glad you were able to self-answer :D –  jcolebrand Oct 7 '11 at 18:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.