10

I am working in a dev environment to better understand TDE encryption. I have it working along with backups and restores on another server. Had a few questions, I know I need to backup the certificate with the corresponding private key.

USE master; 
GO 
BACKUP CERTIFICATE Test
TO FILE = 'C:\Test.cer'
WITH PRIVATE KEY
(FILE = 'C:\Test.pvk',
ENCRYPTION BY PASSWORD = 'Example12#')

These need to be moved/restored on the new server in the event of a failure. Is there anything else that I need to backup from the source server that would be needed in the event of needing to restore to another server?

Also any suggestions on private key storage ? My thought at the moment is to backup the certificate, private key, and password to a KeePass database that is backed up separately and replicated off-site.

That leaves the question though of where to backup the KeePass private key to ?

Improve this question

2 Answers 2

Reset to default
5

If you want to restore on a different server, you should be able to do so with the certificate, private key and database backup file(s).

When a certificate is created in any database in SQL Server, it is part of an encryption hierarchy. The certificate in the master database itself only contains a public key, which needs no protection, however, the master database will also contain a separate but mathematically related private key which does need to be protected. The method of protecting the private key is to encrypt it using the database master key you created in the master database prior to creating the certificate. The next layer in the encryption hierarchy is that the DMK is encrypted by the service master key. There is only one SMK on the system and it is in the master database.

Even though you don't need the DMK and SMK to restore an encrypted backup to another server, I would back up these two keys anyway as it makes recovery much more flexible.

When you restore the backup encryption certificate to the master database, what happens behind the scenes is that the private key is read from the file, decrypted using the password you provide in the restore command, then encrypted using the database master key and saved. As you know, the private key from the certificate can then be used to decrypt the Database Encryption Key in the backup file and successfully restore the database.

I don't have a specific recommendation for storing the certificate and key backup files, but they do need to be available to you and whoever does the disaster recovery in your organization.

Improve this answer
1

In Denny Cherry's book Securing SQL Server I found a security best practice for backing up the certificates:

  • Burn the certificate backups on two different CDs
  • Place each CD in a sealed and signed envelope
  • Mark the envelope with which system these certificates are for
  • Place one CD in the office safe of manager of the company
  • Store the second CD offsite in another secure location
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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