Encryption in your SQL Server backup strategy!

Security best practices must be implemented also in your SQL Server backup strategy!

Databases in a company are the place where information is stored to drive the company’s production processes. Tera of data, dozens of databases, millions of rows, the entire activity depends on this, and information security can no longer be an option, we have to think about security by design and security by default.

If the environments are all-on-premise, data protection appears easier than in hybrid or fully cloud environments, anyway, what if database backups are stored off-site or in the cloud? Scenarios like these require some extra protections, the data reside on someone else’s infrastructure! If someone gains unauthorized access to your site, they could simply restore a copy of your databases from a backup. What is the use of maniacally protecting the company’s local network if backups can be stored externally without advanced protections?

Security best practices are in place to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

In this article, we will talk about backup encryption and how this feature can be applied with the latest versions of SQL Server.

Since SQL Server 2014, SQL Server can encrypt data when creating a backup. You can create an encrypted backup by specifying the encryption algorithm and certificate to protect the encryption key. All storage destinations, on-premises and in Azure, are supported.

SQL Server 2022 supports the following encryption algorithms:

• AES 128
• AES 192
• AES 256
• Triple DES

It is very important to back up the certificate or asymmetric key, used for encryption, to a different location from the backup file. Without the certificate or asymmetric key, it will not be possible to restore the backup.

The steps to encrypt your data during backup will be described below.

A master key must be created if it has not already been created in the master database. The master key is a symmetric key used to protect certificate private keys and asymmetric keys in the database. For more details see SQL Server and Database Encryption Keys (Database Engine).

The following T-SQL piece of code creates the master key in the master database.

-- Create a database master key
-- 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
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLProtectionEncryption2023';
GO

After creating the master key, it is needed to create a certificate or asymmetric key to be used for backup encryption. To increase the security level, I recommend creating specific certificates or asymmetric keys, one for each database backup strategy.

The following T-SQL piece of code creates the AdventureWorksBackupCertificate exclusively for backing up the AdventureWorks database.

-- Create a database certificate for backup
CREATE CERTIFICATE [AdventureWorksBackupCertificate] WITH SUBJECT = 'Certificate for AdventureWorks backup encryption';
GO

After creating the master key and the certificate to be used for encryption, we have to back up the certificate by placing it in a reserved area. The purpose of backing up the certificate on a dedicated file system area is to protect the data from any server failure.

Let’s back up the certificate using the BACKUP CERTIFICATE T-SQL command, the name of the certificate for the AdventureWorks database is AdventureWorksBackupCertificate, the backup will be saved in a secure location on a remote drive. We will also export the private key file (.key) which will encrypt the certificate file, everything is protected with a password.

-- Export the backup certificate to a file
BACKUP CERTIFICATE [AdventureWorksBackupCertificate]
  TO FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorksBackupCertificate.cert'
  WITH PRIVATE KEY (
                     FILE = 'X:\SQL\DBs\Backup\Certificate\AdventureWorksBackupCertificate.key'
                     ,ENCRYPTION BY PASSWORD = 'royalbreeze489'
                   );
GO

We are ready to back up the AdventureWorks database with encryption protocols enabled. I recommend choosing the AES_256 encryption algorithm. The backup will then be encrypted with the AdventureWorksBackupCertificate we just created.

BACKUP DATABASE [AdventureWorks2022]
  TO DISK = N'C:\SQL\DBs\Backup\AdventureWorks2022-with-encryption.bak'
  WITH COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [AdventureWorksBackupCertificate]);
GO

Restore encrypted backup

To restore an encrypted backup, it is not required to specify any encryption parameters. The asymmetric key or certificate used to encrypt the backup file must be available in the SQL Server instance being restored.

In addition, the user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. The operations to be performed to restore an encrypted SQL Server backup to a new location are:

• Certificate backup (to file) in the previous database server
• Create the master key, if it isn’t already present, in the database server in which you want to restore the database backup
• Restore the certificate from the imported file backup to a location in the new SQL Server where you want to restore the database backup
• Restore the database to the new SQL Server instance

You just have to add encryption in your backup strategy; those using SQL Server Maintenance Plan can start from here.

Enjoy SQL Server backup encryption!