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?
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.
So I will need to manually backup they key and certificate with;
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' )
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
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.