As part of Encryption Key management in SQL Server 2008 Transparent Data Encryption (TDE) implementation, SQL server allows re-encryption of a mirrored database without breaking the Mirroring setup. In the following post I would be describing the script I used to perform this task.

 

Prerequisite :

·         Mirroring setup with TDE

·         Principal server : Server1

·         Mirror Server: Server2

·         Mirrored database: M1

·         Existing certification used to encrypt the databases : Cert1

 

Steps to re-encrypt the Mirrored database M1:

 

Step 1. Create a new certificate on the principal Server1:

Use Master

CREATE CERTIFICATE [Cert2]

  WITH SUBJECT = 'NEW_DEK protection certificate for M1'

go

 

Step 2. Backup certificate with Private key.

USE MASTER

BACKUP CERTIFICATE [Cert2]

  TO FILE = '\\File_path\Cert2.cer'

  WITH PRIVATE KEY

    (FILE = '\\File_path\Cert2_pvtkey.pvk',

     ENCRYPTION BY PASSWORD = 'N3W C3rt p4sswoRd')

go

 

Step 3. Restore new certificate on Mirror Server (Server2)

USE MASTER

CREATE CERTIFICATE [Cert2]

FROM FILE = '\\File_path\Cert2.cer'

      WITH PRIVATE KEY (FILE = '\\File_path\Cert2_pvtkey.pvk', 

            DECRYPTION BY PASSWORD = 'N3W C3rt p4sswoRd')

go

         

 

Step 4. Now we are ready to regenerate the DEK using the new certificate on the Principal (Server1):

USE M1

ALTER DATABASE ENCRYPTION KEY

REGENERATE

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE [Cert2]

go

 

·         Now the database (Principal and mirror) should be encrypted with the new certificate Cert2 instead of the Cert1.

·         The presence of Cert2 on the Server2 before running the alter database command will ensure that mirror database is also re-encrypted i.e. Mirroring will not break.

 

 

 

Ashutosh Tripathi
SE, Microsoft SQL Server

Reviewed by
Shamik Ghosh, & Rakesh Singh
CSS , Microsoft SQL Server