This article will address the issue of backup encryption, why it is important, and the various approaches available.

• Why encrypt backups?
• Application-level encryption and “always encrypted”
  • Simple backup encryption (7zip)
• SQL Server backup encryption
  • Creating a certificate
  • IMPORTANT: certificate backup
  • Creating a backup with built-in encryption in SQL Server
  • Restoring an encrypted SQL Server backup
• TDE
• IMPORTANT: Do not lose the password and certificate

Why encrypt backups?

Data in a database is typically valuable and a target for hackers. However, this data can be accessed not only by connecting directly to the database but also from a backup file. Therefore, protecting backups is as crucial as protecting the database itself.

One way to protect data in backups is by encrypting it. If a hacker steals the backup, they won’t be able to extract the necessary data without the encryption key.

Application-level encryption and “always encrypted”

Before delving into backup encryption, it’s important to note that critical data should ideally be encrypted directly within the database. For example, credit card information or access tokens should be encrypted at the database level.

There are two primary methods to achieve this:

1. Application-level data encryption. In this approach, data is encrypted using an external encryption library before it is written to the database. The encryption and decryption processes occur within the application layer.
2. Always encrypted. This method, managed by SQL Server’s client library and the database administrator, automates the encryption process. It ensures that data is encrypted and decrypted transparently to the application, with the encryption keys managed by SQL Server.

Choosing between these methods depends on your requirements. The key difference is that in the second method, the encryption rules are managed by SQL Server itself, whereas in the first method, they are managed by the client-side code.

Encrypting sensitive data in the database is a best practice and should be implemented whenever possible. However, it’s not feasible to encrypt all data in the database because encryption can interfere with searching, sorting, grouping, etc. Therefore, encrypting the entire backups is also a crucial step in protecting your data.

Simple backup encryption (7zip)

The simplest way to encrypt a backup is to use an archiving tool that supports password protection. For example, 7zip offers a straightforward command-line interface where you can specify a password. Here’s how you can do it:

sqlcmd -U sa -P sa_password -Q "BACKUP DATABASE AdventureWorks TO DISK='C:\backup\AdventureWorks.bak'"

"c:\Program Files\7-Zip\7z" a C:\backup\AdventureWorks.zip C:\backup\AdventureWorks.bak -pMyPassword

In this example, the database will be encrypted with the password “MyPassword.” When setting a password, 7z applies strong encryption (AES-256), making it a straightforward encryption method that meets global standards.

Decryption and restoring the backup are also simple. The key is to remember the password used during the encryption of the backup file:

 "c:\Program Files\7-Zip\7z" e C:\backup\AdventureWorks.zip -oC:\backup -pMyPassword

sqlcmd -Q "RESTORE DATABASE AdventureWorks FROM DISK='C:\backup\AdventureWorks.bak' WITH REPLACE"

SQL Server backup encryption

SQL Server includes functionality for encrypting backups. You can create a certificate on your server and use this certificate in the backup command. The backup will be encrypted, and to restore it, the same certificate must be present on the server.

Creating a certificate

First, you need to create the database master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';

OR, if the master key already exists, you need to open the key within the same user session:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'

Now you can create the certificate:

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20250101';

SUBJECT — certificate name, which will be displayed in SSMS, EXPIRY_DATE — certificate expiration date

IMPORTANT: certificate backup

After creating the certificate, it needs to be exported as it will be required for restoration.

USE master;
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\Backups\MyServerCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\MyServerCertKey.pvk',
    ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe');

.cer this file is the actual certificate itself, .pvk this file is the private key file used to operate with the certificate that will be encrypted with PASSWORD

Creating a backup with built-in encryption in SQL Server

Next, when creating a backup through SSMS, you need to specify a new backup media set and check the Encrypt Backup checkbox:

or in the SQL backup command specify WITH ENCRYPTION

BACKUP DATABASE SalesDB 
TO DISK = 'C:\temp\SalesDB.bak'
WITH ENCRYPTION 
    (ALGORITHM = AES_256, SERVER CERTIFICATE = MyServerCert);

This command encrypts the backup using AES_256 and sertificate MyServerCert which was created above.

Restoring an encrypted SQL Server backup

To restore a backup that was encrypted by SQL Server itself, you need the certificate used for encryption to be present in the database. If you are restoring the backup to the same server where the necessary certificate already exists, you don’t need to specify anything additional apart from initiating the restore process itself.

If you are restoring to a new server, first create a new master key (with any password):

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword!';

USE master;
CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\Backups\MyServerCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\MyServerCertKey.pvk',
    DECRYPTION BY PASSWORD = 'AnotherComplexPassword!'
);

After that, you can restore the backup as usual, and the required certificate will be automatically picked up.

TDE

Transparent Data Encryption (TDE) is database-level encryption where data is encrypted at the page level. Data is written to disk in an encrypted format and decrypted when read.

Backups are also created in an encrypted format.

Enabling TDE is done at the database level, not for the entire server, and it is relatively straightforward:

1. Create the master key:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword!';

2. Create a certificate:

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Certificate';

3. Immediately back up the certificate:

USE master;
BACKUP CERTIFICATE MyServerCert 
TO FILE = 'C:\Cert\MyServerCert.cer' 
WITH PRIVATE KEY ( 
     FILE = 'C:\Cert\MyServerCertKey.pvk', 
     ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe');

4. Creating the database encryption key:

USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

4. Enabling TDE for this database

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Next, you can create a backup as usual. It will be encrypted immediately.

During restoration, ensure the same certificate used to encrypt the database is present in the database. If restoring to a new server, first export the certificate.

IMPORTANT: Do not lose the password and certificate.

As seen in the article, creating encrypted backups is not difficult, but managing backups becomes more complex. Besides the backups themselves, you need to securely store certificates and backup passwords.

Data for decryption should be stored in a separate storage (not where backups are kept), preferably in multiple locations. A good option is to store certificates in a corporate password manager and duplicate it on removable media (e.g., a flash drive).

Conclusion

SQL Server backup encryption is an important part of a backup strategy; configuring it is straightforward. However, to ensure encrypted backups remain accessible, it’s crucial not to lose decryption keys.

By the way, both SqlBak and SQLBackupAndFTP allow you to enable backup encryption.

Happy backups!