SQL TDE Question

Andrey Lau 1 Reputation point
2020-08-10T08:30:32.953+00:00

I went through the docs document to set up SQL Server TDE Extensible Key Management by using Azure Key Vault.

I have a question: how to rotate the key without destroying the SQL database? What's the script?

Note: I am using local SQL Server not Azure SQL Database.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-08-11T02:11:04.187+00:00

    Hi AndreyLau-2325,

    I have a question: how to rotate the key without destroying the SQL database? What's the script?

    Next is the process to rotate the keys and certificates used for TDE encryption:

    1. Create a new SQL TDE certificate;
    2. Backup the new SQL TDE certificate;
    3. Create the same SQL TDE certificate;
    4. Change encryption key for your databases;

    More detail steps and codes you can reference : rotate-tde-keys&certificates

    And this could be helpful: key-rotation-in-tde, SmartKey

    BR,
    Mia
    If the reply helped, do "Accept Answer" and upvote it.--Mia.

    1 person found this answer helpful.
    0 comments
  2. m 4,271 Reputation points
    2020-08-13T03:25:40.773+00:00

    Hi AndreyLau-2325，

    Please refence the reply from this case : tde-regenerate-key

    BR,
    Mia
    If the reply helped, please "Accept Answer" and upvote it.--Mia

    0 comments
  3. Stratos Matzouranis 36 Reputation points
    2020-08-26T13:03:02.977+00:00

    Btw even if TDE certificate expires it ll still works.

    0 comments
  4. Graziano Tartari 56 Reputation points
    2021-05-03T15:02:32.42+00:00

    The question is unanswered I see.

    Using AKV we don't need anymore certificate but the question to rotate is still alive.
    In Azure Key Vault you can create a new version of the key and re-encrypt the DEK.
    It works but then you cannot re-encrypt the old backup anymore and this is a problem because you cannot restore them.
    You have to create a new key, a new credential and a new login and preserve the oldest in order to restore the bk.

    Regards,
    Graziano.

    0 comments