locked
TDE encryption backup RRS feed

  • Question

  • We have a database that is encrypted using TDE. We made a backup of this database and gave it to our clients who then need to restore the database to their server. How do I do this knowing TDE is enabled? I have the pvk and cer files from our server, but not sure what the process is. can anyone help?
    Monday, October 20, 2014 11:24 PM

Answers


  • Restoring a TDE Encrypted Database to a Different Server or Location

    Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

    USE [master]
    RESTORE DATABASE [TDE_restore] FROM  
    DISK = N'C:\Backup\TDE_Enabled.bak' 
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

    Output:

    Msg 33111, Level 16, State 3, Line 2
    Cannot find server certificate with thumbprint..
    Msg 3013, Level 16, State 3, Line 2
    RESTORE DATABASE is terminating abnormally
    

    To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

    USE master; 
    GO 
    BACKUP CERTIFICATE TDECert1
    TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
    WITH PRIVATE KEY
    (FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
    ENCRYPTION BY PASSWORD = 'Password12#')

    Create a Master Key in destination server.

    The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'
    

    After a master key has been created, 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 TDECert2
    FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
    WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
    DECRYPTION BY PASSWORD = 'Password12#')
    

    Restore Database in destination server

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

    USE [master]
    RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
    WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5


    Raju Rasagounder Sr MSSQL DBA

    Monday, October 20, 2014 11:54 PM
  • Yes  this password is from source and then you have to give the same indestination ( newly restoring server )

    Raju Rasagounder Sr MSSQL DBA


    Tuesday, October 21, 2014 2:43 AM

All replies


  • Restoring a TDE Encrypted Database to a Different Server or Location

    Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

    USE [master]
    RESTORE DATABASE [TDE_restore] FROM  
    DISK = N'C:\Backup\TDE_Enabled.bak' 
    WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

    Output:

    Msg 33111, Level 16, State 3, Line 2
    Cannot find server certificate with thumbprint..
    Msg 3013, Level 16, State 3, Line 2
    RESTORE DATABASE is terminating abnormally
    

    To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

    USE master; 
    GO 
    BACKUP CERTIFICATE TDECert1
    TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
    WITH PRIVATE KEY
    (FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',
    ENCRYPTION BY PASSWORD = 'Password12#')

    Create a Master Key in destination server.

    The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D1ffPa$$w0rd'
    

    After a master key has been created, 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 TDECert2
    FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
    WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 
    DECRYPTION BY PASSWORD = 'Password12#')
    

    Restore Database in destination server

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

    USE [master]
    RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 
    WITH  FILE = 1, NOUNLOAD,  REPLACE,  STATS = 5


    Raju Rasagounder Sr MSSQL DBA

    Monday, October 20, 2014 11:54 PM
  • I understand this except for create a master key in destination server. I created one as explained, however the script says

    DECRYPTION BY PASSWORD = 'Password12#')

    Where does this password come from? Is it from the source server? And if so I need to hand the password to my client?

    Monday, October 20, 2014 11:58 PM
  • Yes  this password is from source and then you have to give the same indestination ( newly restoring server )

    Raju Rasagounder Sr MSSQL DBA


    Tuesday, October 21, 2014 2:43 AM
  • We have a database that is encrypted using TDE. We made a backup of this database ...I have the pvk and cer files from our server, but not sure what the process is. can anyone help?

    Don't get me wrong, but please please work through all the links and scripts provided by the former respondents and make sure you comprehend the concept.

    When you start encrypting your database, it is even more vital than ever that you have tested and proven your recovery-strategy is working in a true disaster on a completely new server as well. Otherwise you are running the risk of losing all your data and not even any recovery tool would be able to save even single rows out of the encrypted data pages.(!)


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com


    Tuesday, October 21, 2014 8:25 PM
  • ...

    Don't get me wrong, but please please work through all the links and scripts provided by the former respondents and make sure you comprehend the concept.

    ...

    I just realize there were not so much links, just underlined titles. So here you go for some more literature on the topic:

    Transparent Data Encryption (TDE)

    Move a TDE Protected Database to Another SQL Server


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Tuesday, October 21, 2014 8:29 PM