This topic provides an overview of the encryption options for SQL Server backups. It includes details of the usage, benefits, and recommended practices for encrypting during backup.
Starting in SQL Server 2014 (12.x), SQL Server has the ability to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a Certificate or Asymmetric Key) when creating a backup, you can create an encrypted backup file. All storage destinations: on-premises and Window Azure storage are supported. In addition, encryption options can be configured for SQL Server Managed Backup to Microsoft Azure operations, a new feature introduced in SQL Server 2014 (12.x).
To encrypt during backup, you must specify an encryption algorithm, and an encryptor to secure the encryption key. The following are the supported encryption options:
Encryption Algorithm: The supported encryption algorithms are: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key
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.
Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance.
The sequence to restore an encrypted database to a new location is to:
- BACKUP CERTIFICATE (Transact-SQL) in the old database
- CREATE MASTER KEY (Transact-SQL) in the new location master database
- CREATE CERTIFICATE (Transact-SQL) from the backup certificate of the old database imported to a location on the new server
- Restore a Database to a New Location (SQL Server)
If you are restoring a backup from a TDE encrypted database, the TDE certificate should be available on the instance you are restoring to. For more information, see Move a TDE Protected Database to Another SQL Server.
Encrypting the database backups helps secure the data: SQL Server provides the option to encrypt the backup data while creating a backup.
Encryption can also be used for databases that are encrypted using TDE.
Encryption is supported for backups done by SQL Server Managed Backup to Microsoft Azure, which provides additional security for off-site backups.
This feature supports multiple encryption algorithms up to AES 256 bit. This gives you the option to select an algorithm that aligns with your requirements.
You can integrate encryption keys with Extended Key Management (EKM) providers.
The following are prerequisites for encrypting a backup:
Create a Database Master Key for the master database: The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database. For more information, see SQL Server and Database Encryption Keys (Database Engine).
Create a certificate or asymmetric Key to use for backup encryption. For more information on creating a certificate, see CREATE CERTIFICATE (Transact-SQL). For more information on creating an asymmetric key, see CREATE ASYMMETRIC KEY (Transact-SQL).
Only asymmetric keys residing in an Extended Key Management (EKM) are supported.
The following are restrictions that apply to the encryption options:
If you are using asymmetric key to encrypt the backup data, only asymmetric keys residing in the EKM provider are supported.
SQL Server Express and SQL Server Web do not support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported.
Previous versions of SQL Server cannot read encrypted backups.
Appending to an existing backup set option is not supported for encrypted backups.
The account that does backup operations on an encrypted database requires specific permissions.
db_backupoperator database level role on the database being backed up. This is required regardless of encryption.
VIEW DEFINITION permission on the certificate in
The following example grants the appropriate permissions for the certificate.
USE [master] GO GRANT VIEW DEFINITION ON CERTIFICATE::[<SERVER_CERT>] TO [<db_account>] GO
Access to the TDE certificate is not required to back up or restore a TDE protected database.
Backup Encryption Methods
The sections below provide a brief introduction to the steps to encrypting the data during backup. For a complete walkthrough of the different steps of encrypting your backup using Transact-SQL, see Create an Encrypted Backup.
Using SQL Server Management Studio
You can encrypt a backup when creating the backup of a database in any of the following dialog boxes:
Back Up Database (Backup Options Page) On the Backup Options page, you can select Encryption, and specify the encryption algorithm and the certificate or asymmetric key to use for the encryption.
Using Maintenance Plan Wizard When you select a backup task, on the Options tab of the Define Backup ()Task page, you can select Backup Encryption, and specify the encryption algorithm and the certificate or key to use for the encryption.
Following is a sample TSQL statement to encrypt the backup file:
BACKUP DATABASE [MYTestDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak' WITH COMPRESSION, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert ), STATS = 10 GO
For the full Transact-SQL statement syntax, see BACKUP (Transact-SQL).
This example creates the encryption options and uses it as a parameter value in Backup-SqlDatabase cmdlet to create an encrypted backup.
$encryptionOption = New-SqlBackupEncryptionOption -Algorithm Aes256 -EncryptorType ServerCertificate -EncryptorName "BackupCert" Backup-SqlDatabase -ServerInstance . -Database "<myDatabase>" -BackupFile "<myDatabase>.bak" -CompressionOption On -EncryptionOption $encryptionOption
Create a backup of the encryption certificate and keys to a location other than your local machine where the instance is installed. To account for disaster recovery scenarios, consider storing a backup of the certificate or key to an off-site location. You cannot restore an encrypted backup without the certificate used to encrypt the backup.
To restore an encrypted backup, the original certificate used when the backup was taken with the matching thumbprint should be available on the instance you are restoring to. Therefore, the certificate should not be renewed on expiry or changed in any way. Renewal can result in updating the certificate triggering the change of the thumbprint, therefore making the certificate invalid for the backup file. The account performing the restore should have VIEW DEFINITION permissions on the certificate or the asymmetric key used to encrypt during backup.
Availability Group database backups are typically performed on the preferred backup replica. If restoring a backup on a replica other than where the backup was taken from, ensure that the original certificate used for backup is available on the replica you are restoring to.
If the database is TDE enabled, choose different certificates or asymmetric keys for encrypting the database and the backup to increase security.
|Create an Encrypted Backup||Describes the basic steps required to create an encrypted backup|
|Extensible Key Management Using Azure Key Vault (SQL Server)||Provides an example of creating an encrypted backup protected by keys in the Azure Key Vault.|