locked
Unfamiliar with usage of TDE RRS feed

  • Question

  • I have a couple of basic questions concerning using TDE.  I have 2 SQL Server instances that are configured for TDE, both running 2014 ENT edition.

    1.  If it is common for databases to be restored back and forth between instances of SQL Server on different nodes, do the instances use the same master key?

    2.  If this cross instance restore isn't typically done, but I have a need to restore a database from one instance to another for some reason, I would need to drop the master key first before adding the master key from the source instance.  Do I need to put the original master key back in place once I am done with the restore?  I also need to create a new database cert using the files from the source instance.  I guess I'm confused over the impact to the existing encrypted databases on the destination instance.  Would they all start using the same master key and database cert?

    Monday, November 6, 2017 7:57 PM

Answers

  • Hi pellet88,

     

    For the first question, there is no need to use the same Database Master Key (DMK) when backing up and restoring between two instances which enabled TDE, but we need to use the same certificate (.cer) and private key (.pvk).

     

    The database master key is used to encrypt other keys and certificates inside a database. And we use the certificate to encrypt a database, DMK will not be used to encrypt a database directly, in your scenario, please use the following steps:

     

    1. We will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. If you did not have a backup before, please use the following code to backup it:

     

                 USE master;

                 GO

                 BACKUP CERTIFICATE ***

                 TO FILE = '***.cer'

                 WITH PRIVATE KEY

                 (FILE = '***.pvk',

                 ENCRYPTION BY PASSWORD = '***')

     

    1. Because you have a DMK on your destination server, you did not to create a new one, please use the following code to create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

     

                 CREATE CERTIFICATE ***

                 FROM FILE = '***.cer'    

                 WITH PRIVATE KEY (FILE = '***.pvk',

                 DECRYPTION BY PASSWORD = '***')

     

    1. We will now be able to restore the encrypted database backup successfully.

     

    You can refer to this article: https://dataginger.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Teige Gao Tuesday, November 7, 2017 6:09 AM
    • Proposed as answer by Shanky_621MVP Tuesday, November 7, 2017 7:23 AM
    • Marked as answer by pellet88 Tuesday, November 7, 2017 10:37 AM
    Tuesday, November 7, 2017 6:06 AM

All replies

  • This is for an emergency type situation.  I cannot set encryption off on the database before hand.  I may need to restore to a few days ago.

    Monday, November 6, 2017 8:02 PM
  • Hi pellet88,

     

    For the first question, there is no need to use the same Database Master Key (DMK) when backing up and restoring between two instances which enabled TDE, but we need to use the same certificate (.cer) and private key (.pvk).

     

    The database master key is used to encrypt other keys and certificates inside a database. And we use the certificate to encrypt a database, DMK will not be used to encrypt a database directly, in your scenario, please use the following steps:

     

    1. We will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. If you did not have a backup before, please use the following code to backup it:

     

                 USE master;

                 GO

                 BACKUP CERTIFICATE ***

                 TO FILE = '***.cer'

                 WITH PRIVATE KEY

                 (FILE = '***.pvk',

                 ENCRYPTION BY PASSWORD = '***')

     

    1. Because you have a DMK on your destination server, you did not to create a new one, please use the following code to create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

     

                 CREATE CERTIFICATE ***

                 FROM FILE = '***.cer'    

                 WITH PRIVATE KEY (FILE = '***.pvk',

                 DECRYPTION BY PASSWORD = '***')

     

    1. We will now be able to restore the encrypted database backup successfully.

     

    You can refer to this article: https://dataginger.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Edited by Teige Gao Tuesday, November 7, 2017 6:09 AM
    • Proposed as answer by Shanky_621MVP Tuesday, November 7, 2017 7:23 AM
    • Marked as answer by pellet88 Tuesday, November 7, 2017 10:37 AM
    Tuesday, November 7, 2017 6:06 AM
  • Much appreciated.  This was exactly what I didn't understand.  Thank you.
    Tuesday, November 7, 2017 10:38 AM