Backup Encryption | Part-1
We’ve been asking for this feature from a long time and it was one of the main reasons why third party tools were being used, they were minting money like anything. Thankfully Microsoft introduced this feature in SQL Server 2014, now you can encrypt the data while creating a backup at runtime and creates an encrypted backup file.
The best part is all type of storage destinations be it On-Premise or Windows Azure Storage all of them are fully supported. In addition to that you can also use this feature if you are using SQL Server Managed Backup to Windows Azure. I know what you are thinking “you are still using old version and you’ve to rely on these third party tools” The good news is, if you want you can use Backup encryption in your old SQL versions also (like SQL Server 2005, 2008 and SQL 2008 R2) but only if you plan to take backups on Azure by using SQL Server Backup to Windows Azure Tool. Read more about this feature here…
Benefits
1. This helps in securing the data; SQL Server encrypts the data at the time of creating a backup – so no need to change anything on the application front.
2. You can also use Backup Encryption for TDE enabled databases.
3. Since this is also supported with SQL Server Managed backup to Windows Azure this gives an added security for off-site backups.
4. You’ve got multiple options to select the encryption algorithm to meet your requirements.
5. You can integrate encryption keys with Extended Key Management (EKM) providers.
Backup
To make use of Backup Encryption all you need to do is provide an encryptor (that could be a Certificate or Asymmetric Key) and choose an Encryption algorithm. SQL Server provides four encryption algorithm to choose from these are: AES 128, AES 192, AES 256 and Triple DES.
Restoring
During restore you don’t have to specify any encryption parameters. However the certificate or the asymmetric key used to encrypt the backup file must be available on the instance you are restoring to. The user account performing the restore must have View Definition permissions on the certificate or key. If the DB you are restoring is already TDE configured, the TDE certificate should also be available on the instance you are restoring to.
Encryptor
You must create a Database Master Key (DMK) before creating a Certificate or Asymmetric Key. A DMK is symmetric key that is used to protect the private keys of Certificates and Asymmetric keys that are present in the database.
Once a DMK is created you can create a certificate or asymmetric key to use for backup encryption.
Restrictions
1. While encrypting backups using asymmetric key always remember the keys residing in the EKM provider are supported.
2. Two SQL Editions (SQL Express and SQL Server Web) do not support encryption during backup. Having said that restoring from an encrypted backup to these editions is fully supported.
3. Also note that earlier versions of SQL Server cannot read encrypted backups.
4. Appending to an existing backup set option is also not supported if you are encrypting the backup.
It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable.
Permissions
To encrypt or restore from an encrypted backup View Definition permission on the certificate or asymmetric key that is used to encrypt the database backup. However Access to the TDE certificate is not required to backup or restore a TDE protected Database.
Thanks,
Sarabpreet Singh