Post reply

TDE - alter database encryption key

  • November 24, 2011 at 1:54 am

    #252542

    I want to learn about altering db encryption key.

    Because it is possible that the security team want the key change periodically.

    I have 2 database servers - Primary and Secondary.

    Both are encrypted with TDE.

    1. I encrypted Primary database with TDE by creating master key, then certificate, encrypt, ...

    2. At Primary server, I create back up of certificate and private key files.

    3. At Primary, I create back up of database.

    4. At Secondary server, I create master key and create certificate from the files copied from Primary server.

    5. At Secondary server, I restore database successfully.

    Now both databases are TDE encrypted. Both work normally

    6. Back to Primary server, I alter the encryption key

    USE [DB_NAME];

    GO

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE WITH ALGORITHM = AES_256;

    GO

    7. At Primary server, I back up this database.

    8. At Secondary server, I try restore this database.

    I expect that it should fail because the encryption key is already changed at Primary but it is not yet applied to Secondary.

    9. But surprise that restore successful !!!

    Why ????

  • November 24, 2011 at 1:59 am

    #1412580

    EaseUS Todo Backup Advanced Server[/url] is an easy-to-use and cost-effective backup software for MS SQL Server 2000, 2005, 2008 or Exchange Server 2003/2007 and provides proven database backup solution for Server administrators.

    It can encrypt the database backups to ensure the data security.

  • November 24, 2011 at 2:21 am

    #1412591

    USE [DB_NAME];

    GO

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE WITH ALGORITHM = AES_256;

    GO

    You're not changing the certificate, you're simply regenerating the encryption. To change the certificate you would have to use:

    USE [DB_NAME];

    GO

    ALTER DATABASE ENCRYPTION KEY

    REGENERATE WITH ALGORITHM = AES_256

    ENCRYTPION BY CERTIFICATE 'NewCert';

    GO

    Without the new certificate details you're simply refreshing the encryption using the current certificate, which is why it still works on the second server.

  • November 24, 2011 at 2:58 am

    #1412606

    Before running your suggested command, I have to create a new certificate first also ?

  • November 24, 2011 at 3:04 am

    #1412608

    Yes, otherwise you're simply regenerating the encryption based on the existing certificate.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply