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


 

Comments (0)

Skip to main content