Lessons learnt by implementing Backup Encryption
We’ve already discussed Backup Encryption feature in detail, incase you’ve not covered those please find the link:
There are a few things you must know before you implement backup encryption in your environment, here I’ll try to list them:
1. Original Certificate which was used as an encryptor should be available at the destination server\Instance to restore an encrypted backup. Encrypted backup keeps track of the thumbprint of the certificate, if the thumbprint doesn’t match it will never allow SQL Engine to read the backup, you won’t even be able to even get the file list details from the backup file (Restore FilelistOnly option). Refer the screenshot below which shows the error:
2. We should also refrain our self from renewing or changing the certificate in any way, renewal or changing the certificate results in changing the thumbprint of the certificate therefore making the certificate useless for the backup restore.
3. The login ID being used to restore the encrypted backup must have View Definition permissions on the encryptor (Certificate\Asymmetric Key) which was used to encrypt the backup.
4. You must restore the Database Masker Key on the destination server\SQL Instance since the certificate we used was encrypted using DMK only.
5. You need not to Restore the Service Master Key, if your DMK was encrypted using a Password- that is not a must.
6. You must take a backup of all your keys\certificates and passwords and keep it on a safe location other than the source server. The best practice would be to keep a copy of these things at your DR site or Offsite.
7. The Database master key (DMK) must be explicitly opened if it was encrypted using a password.
8. Restoring a Master Key can be a resource intensive task, if the current master key is being used to encrypt many things and you are restoring a new Master Key it has to decrypt and re-encrypt everything- we must try to do it in Off-Peak time.
Happy Learning
Thanks,
Sarabpreet Singh