Results 1 to 5 of 5

Thread: TDE and backups

  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: TDE and backups

    Hi,

    Am new to SQL so please bear with me....

    I'm looking to use TDE to encrypt a database on disk, and tape backups... my concern is backing up the certificate and key used to do the encryption.

    Am I right in thinking that if I backup the master database, this will contain everything needed to restore the encrypted database, and in the event of a failure, I can restore the master db, and then restore my encrypted db?

    Thanks for your help!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Nope. To be safe, you will need to ensure that you have copies of all of the keys and certificates available. You will not have to back them up often, as they do not change over time, though.

    The keys in the master database are encrypted with the master database key, which in turn is encrypted with the service master key. When you go to a new server, none of the keys in the master database will be usable, as they are encrypted with a key that does not match the new server's service master key.

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Thanks, I think I get it,

    So I will need to manually backup they key and certificate with;

    USE master
    GO
    BACKUP CERTIFICATE MyDEKCert
    TO FILE = 'C:\sql-backups\MyDEKCert-Backup.cer'
    WITH PRIVATE KEY (FILE = 'C:\sql-backups\MyDEKCert_KeyBackup.pvk' ,
    ENCRYPTION BY PASSWORD = 'str0ngp4ssw0rd' )
    GO

    In the event of a failure, I can then re-create the certificate with these files and then restore the database?

    One of my concerns was that in doing a full backup of all the databases, everything required to decrypt the database was backed up on the same tape, making this whole thing fairly pointless

    Another concern is that someone decides to change the key, making our backup out of date and useless

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Well, if they don't know what to use for the password to decrypt the backup, then it is OK. If you post the password in public forums, weeelllll... ;-)

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Aren't the certificates password protected themselves?
    btw: certificates usually expire, it might take a while though. I wonder what happens if you try to decrypt a backup with an expired certificate or a renewed certificate.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •