How to “REGENERATE DEK” with a different certificate on Encrypted Mirrored database

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