Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

(We are, fortunately, not currently in this situation, just planning ahead to see what our options would be if it ever occurred.)

For a database encrypted with Transparent Date Encryption (TDE), a copy of the database backup is unrecoverable unless you have a backup of the certificate used to encrypt it.

What if you don't have that? Are there any additional options?

In the event of a total server failure, would restoring a backup of the MASTER database on new hardware also restore the certificate?

share|improve this question

2 Answers 2

Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted.

This is the TDE encryption hierarchy:

  1. Service master key (protected by Windows; tied to the service account credentials, and a machine key)
  2. Database master key (in this case, the one for the master database)
  3. Certificate
  4. TDE encryption key

The first three items are stored in the master database, and can all be backed up. The fourth is stored (encrypted by the certificate from #3) in the header of the encrypted database.

So in a failure scenario, you would have to restore enough of the encryption hierarchy to allow you to read the TDE key. SQL Server creates the service master key at installation; thus while restoring the master database to a different instance will also restore items 2 and 3, the necessary key(s) to decrypt them will not be present. Result: unreadable data.

The two best options are to either restore the certificate (#3) from a backup (a good option if master cannot be restored for whatever reason), or restore your master database and its master key (#2) from a backup. Restoring the master key may be a better option if you have a lot of certificates/keys protected by this key, and need to make them all accessible at once. This comes with the same precautions normally associated with restoring the master database (collations, logins, database names and file paths, etc.)

Generally, I'd only recommend restoring master in a recovery scenario. For a migration/scale-out scenario (such as using Availability Groups/mirroring with a TDE-encrypted database), it's better to backup/restore the certificate (#3) so that it's encrypted using the master keys unique to each instance it's moving to. You will need to include the private key with the certificate backup.

In any case, you're going to have to make key/certificate backups, so guard them well, and store them in redundant, secure locations. Simply having a backup of master will not get you out of a TDE disaster; you're going to need a backup of at least one key or certificate.

share|improve this answer
    
Hmm, so how is it that we can restore the certificate on another server without also restoring the SMK (1) or master db DMK (2)? Does it "attach" itself to the SMK/DMK from that new server? –  BradC Feb 14 '13 at 21:44
    
Ah, I think I get it: when you export the certificate, you are explicitly providing a key for export, which replaces the dependence on the SMK/DMK from the original server. Upon importing on the new server, you are then transferring the dependence onto the new server's SMK/DMK. Does that sound right? –  BradC Feb 14 '13 at 21:51
    
Yup, that's pretty much exactly it. When you export or import a certificate, you must provide a password that's used to encrypt/decrypt the backup. When you restore the backup, the certificate is imported, and re-encrypted with the destination server's DMK. –  db2 Feb 15 '13 at 13:01

1.If you want to restore a encrypted backup to another server as usual you encounter the following error

 Cannot find server certificate with thumbprint …...

2.Find the cert name : in this example vestacert

   SELECT  * FROM   sys.certificates

3.backup the cert from source server (Source encryptedserver) :

BACKUP CERTIFICATE vestacert
TO FILE = 'c:\Backup\certificate_TDE_Test_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'c:\Backup\certificate_TDE_Test_Key.pvk',
ENCRYPTION BY PASSWORD = 'Password12#')

4.Create new Master Cert on UAT server if not already exist

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

5.Restore backup certs in UAT server (UATserver)

CREATE CERTIFICATE vestacert2
FROM FILE = 'C:\tmp\certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'C:\tmp\LCMS\certificate_TDE_Test_Key.pvk', 
DECRYPTION BY PASSWORD = 'Passsword12#')

6.After this step restoring backup does not have any error and all data was readable.

7.But the funny thing is that removing encryption simply and taking new backup and restoring it on final server (Final Server) does not work and gives the following error The file "mydb_log" failed to initialize correctly. Examine the error logs for more details.

8.The correct way of removing encryption from UAT is to remove all signs like below step by step and from bottom to top

    USE master
    ALTER DATABASE mydb SET ENCRYPTION OFF
    USE mydb
    DROP DATABASE ENCRYPTION KEY 
    USE master
    DROP CERTIFICATE vestacert2 
    DROP MASTER KEY

9.Now create a new backup from UAT server and restore it to final server

good article:http://sqlserverzest.com/2013/10/03/sql-server-restoring-a-tde-encrypted-database-to-a-different-server/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.