locked
TDE key rotation in the database mirroring situation. RRS feed

  • Question

  • I have set up database mirroring for TDE enabled database and they worked fine as far as I got all the key infrastructure identical on both Principal and mirrored SQL servers.

    Now it is coming to our yearly key rotation practice for compliance.  After I regenerate the DEK on principal server and also encrypt it with a newer certificate,  the mirroring is suspended and I am not able to resume it any more.  (Although I have added this newer certificate to the mirrored server too.) It is understandable because now the DEK is out of sync. 

    However, what are the correct steps to do the key rotation in the mirroring scenario? The bottom line is:  I DO NOT want to set up mirroring again since our backups are huge and take very long time to copy the backups from principal server to mirrored server.

    thanks for any input in advance.

     

    Monday, August 16, 2010 7:48 PM

All replies

  • I have set up database mirroring for TDE enabled database and they worked fine as far as I got all the key infrastructure identical on both Principal and mirrored SQL servers.

    Now it is coming to our yearly key rotation practice for compliance.  After I regenerate the DEK on principal server and also encrypt it with a newer certificate,  the mirroring is suspended and I am not able to resume it any more.  (Although I have added this newer certificate to the mirrored server too.) It is understandable because now the DEK is out of sync. 

    However, what are the correct steps to do the key rotation in the mirroring scenario? The bottom line is:  I DO NOT want to set up mirroring again since our backups are huge and take very long time to copy the backups from principal server to mirrored server.

    thanks for any input in advance.  I post this question in bothdatabase mirroring and Security forums because it belongs to both.

    Monday, August 16, 2010 7:54 PM
  • How about taking backup of the existing key from principal server and restoring it in mirror server? Have you tried this? You can check the links below to backup and re-creating the key

    http://sql-articles.com/blogs/implementing-configuring-transparent-data-encryption-tde/

    http://sql-articles.com/blogs/how-to-copy-move-a-database-that-is-encrypted-with-tde/


    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, August 18, 2010 6:54 AM
  • Thanks for your reply.  However,  as I said,  I did not have issue the first time I set up mirroring to get the master key and certificate identical on both principal and mirrored server. (by backuping and restoring the keys and certificate).   Also,  after I created a new certificate,  I also back it up and restore it on mirrored server.

    The issue is,  the DEK to encrypt the database was regerenated by the following TSQL. 


    ALTER DATABASE ENCRYPTION KEY

    REGENERATE

    WITH ALGORITHM = AES_256

    go

    As soon as I run this on principal server,  the mirroring is suspended and can not be resumed.  I don't think the out of sync is caused by certificate but by DEK.

    thanks.

    Wednesday, August 18, 2010 1:52 PM
  • Have you tried only regenerating the DEK (without encrypting it with a new certificate), does that work?

    You can also try this:

    1. Only regenerate the DEK and wait for the encryption scan to complete on the principal.
    2. Once the encryption scan completes then create the new certficate on principal.
    3. Back it up and restore it on the mirrored servers (with the private key).
    4. Once the new certificate is set up on the mirrored servers then encrypt the DEK with the new certificate on the principal server.
    5. Do not drop the old certificate on the primary or the mirrored server.

    Let me know if this works for you or not.

    Regards,

    Zubair


    Zubair
    Wednesday, August 18, 2010 9:44 PM
  • Thank you for the tip.  I checked my errorlog again and find out the real cause of it. 

    I actually used HSM to generate asymmetric key to encrypt DEK.  I am not using the certificate to protect DEK.  (just want to make the explaination a little bit easier.)

    The reason mirroring was suspended because the mirrored server does not have a login and credential created for the new asymmetric key to automatically access the EKM provider.   After I created a new credential and login for the key,  it works.

    I do have another questions though:  when the DEK re-encrypt the database (progressive scan) again,  is the database online and accessible?  I know Oracle 11g has the online encrypting option.  Does SQL 2008 provide this option as well?

    Thanks.

    Thursday, August 19, 2010 1:58 PM
  • Yes, the database is online during the re-encryption scan, albeit performance maytake a hit.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 25, 2010 5:48 PM