locked
Restoring a TDE enabled Database without a copy of the DMK? RRS feed

  • Question

  • Hello all,

    My understanding of TDE and the key hierarchy is that when a certificate is created  through

    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';

    that this is protected by the DMK and once a DEK is created and encrypted using the certificate then there is a copy of this DMK in this database as well as the master DB

    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_128
    ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

    so if the DMK protects the certificate and there is a copy of this DMK in the user database as well as the Master DB then my question is how is it so that we do not need to restore the DMK when moving the TDE enabled DB to another server? 

    All I need do if there is no master key present on the instance is to create a new DMK and then create the certificate and private key for the certificate from a backup,  yet I have seen some articles that describe the backing up of the DMK and restoring of this on the new instance prior to recreating the certificate and even some articles describing the process of backing up and restoring the SMK as well!!

    Thanks in advance

    JTeQ

     

     

    Monday, February 6, 2012 1:42 PM

Answers

  • You need a copy of the original DMK to decrypt other keys and certificates inside the database. So it is always a good practice to back it up. Also, a copy of the DMK is kept inside the master database and silently updated whenever the DMK is changed, so it is automatically decrypted and you don't need to provide the DMK password every time you open it.

    So if you have e.g. a bunch of certificates and/or asymmetric keys you use to encrypt columns, you need to be sure you back up the DMK, because if you lose it you won't be able to decrypt your columns anymore.

    But that is for encrypting data inside your database using the database DMK. It will not apply to TDE.

    "Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM."

    With TDE enabled, that's a slightly different story. You don't need a DMK inside your database. What you need is the master database DMK, which will be used to protect the server certificate you are using to encrypt your database with. And that server certificate is also inside the master database. That's why you must back it up and store it safely. Without it, you won't be able to decrypt your database when you move it to another instance.

    When you move the TDE enabled database to another instance, you must create a DMK inside the master database of that instance if you don't already have one, and then create the server certificate from your backup.

    The DEK that is inside the moved database is already configured to use that certificate. So you can query your data right away.

    And that's what I've come so far. I hope it is clear for you, as it is for me now.


    SQL Server Database Administrator


    • Proposed as answer by Marcelo Farinelli Tuesday, February 7, 2012 2:17 PM
    • Edited by Marcelo Farinelli Tuesday, February 7, 2012 2:26 PM minor corrections and additions
    • Marked as answer by JTeQ Tuesday, February 7, 2012 6:20 PM
    Tuesday, February 7, 2012 2:14 PM

All replies

  • JT, the SMK is unique to that instance, so there is no way you can restore a SMK from another instance.

    When you restore or move a TDE database to another instance, on the destination instance you must create a DMK in the master database (if you don't already have one), recreate the certificate using a backup, and finally attach or restore the database.

    Please take a look at this article for more info.

    I also don't get it. Why would one need to restore the database DMK from another instance? The DMK is protected by the SMK. There is also a certification question about it, and the correct answer would be to create the DMK, create the certificate from a backup and then restore the database, but this answer does not appear, so you can't use it. That's confusing.

    Also take a look at the encryption hierarchy.


    SQL Server Database Administrator
    Monday, February 6, 2012 5:10 PM
  • Thanks for the reply Marcelo. 

    I have trouble understanding why we do not need a copy of the original DMK when we restore the TDE enabled database to another instance, considering that the TDE certificate is encrypted by the DMK and a copy of this DMK is in the TDE protected DB?

    Hopefully by finding this answer then I might be able to get a better understanding of TDE.

    Tuesday, February 7, 2012 9:39 AM
  • You need a copy of the original DMK to decrypt other keys and certificates inside the database. So it is always a good practice to back it up. Also, a copy of the DMK is kept inside the master database and silently updated whenever the DMK is changed, so it is automatically decrypted and you don't need to provide the DMK password every time you open it.

    So if you have e.g. a bunch of certificates and/or asymmetric keys you use to encrypt columns, you need to be sure you back up the DMK, because if you lose it you won't be able to decrypt your columns anymore.

    But that is for encrypting data inside your database using the database DMK. It will not apply to TDE.

    "Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM."

    With TDE enabled, that's a slightly different story. You don't need a DMK inside your database. What you need is the master database DMK, which will be used to protect the server certificate you are using to encrypt your database with. And that server certificate is also inside the master database. That's why you must back it up and store it safely. Without it, you won't be able to decrypt your database when you move it to another instance.

    When you move the TDE enabled database to another instance, you must create a DMK inside the master database of that instance if you don't already have one, and then create the server certificate from your backup.

    The DEK that is inside the moved database is already configured to use that certificate. So you can query your data right away.

    And that's what I've come so far. I hope it is clear for you, as it is for me now.


    SQL Server Database Administrator


    • Proposed as answer by Marcelo Farinelli Tuesday, February 7, 2012 2:17 PM
    • Edited by Marcelo Farinelli Tuesday, February 7, 2012 2:26 PM minor corrections and additions
    • Marked as answer by JTeQ Tuesday, February 7, 2012 6:20 PM
    Tuesday, February 7, 2012 2:14 PM
  • Thanks Marcelo, its a lot clearer for me now.

    My problem was that I was associating the creation of the DMK and password as being solely part of TDE.

    Many thanks for you help.

    Tuesday, February 7, 2012 6:41 PM