Encrypting SQL Server Backups

If a database contains sensitive information, it is possible to encrypt any Full, Differential and Transaction Log backups that are taken of it.  This functionality is included with SQL Server Standard Edition and is not just an Enterprise feature.

There are no requirements to enable any features in SQL Server, just simply create a Master Key and a Certificate within SQL Server and then specify that the certificate is used when a database backup is taken.

‍

Create Master Key

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPassword!!2345’

‍

Create the Certificate

USE MASTER
GO
CREATE CERTIFICATE Backup_Cert_2020 WITH SUBJECT = ‘Certificate for encrypting backups’

‍

Backup the Certificate

The main issue with encrypting backup is that without the certificate that was used in the database backup, it is impossible to restore the backup. Therefore, the certificate needs to be backed up and stored safely off the server.  Using a password manager like Keepass is a good option as this allows you to add attachments. So it’s possible to store the passwords and the keys in the same place.

USE MASTER
GO
BACKUP CERTIFICATE Backup_Cert_2020
TO FILE = ‘C:\Temp\Backup_Cert_2020.cer’
WITH PRIVATE KEY (
FILE=‘C:\Temp\Backup_Cert_2020.ppk’,
ENCRYPTION BY PASSWORD =‘Another strong password!!-23’)

‍

The above command creates two files in the C:\Temp directory of the database server and it also password protects the private key.

To restore the certificate on another server you need both the files and the password that was used to encrypt the private key.

Optionally, once you have securely saved the certificate files and password, remove the files from the database server.

Strictly speaking the password for the service master key is not required in normal operations, but it is recommended that you securely store the password that you encrypted it with. If you restore the master database on another server, you need this password to regenerate the master key.

‍

Restore the Certificate

To restore the encrypted backup to another server, you first need to restore the Certificate that was used to encrypt the backup on to the new server.

As before, the first step in this process is to create a master key on the new server. This doesn’t have to use the same password as the original master key, it just needs to exist.

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPassword!6789’

‍

Then create the certificate using the two backup files and the password that was used to protect the private key.

USE MASTER
GO
CREATE CERTIFICATE Backup_Cert_2020 FROM FILE =‘C:\Temp\Backup_Cert_2020.cer’
WITH PRIVATE KEY(FILE=‘C:\Temp\Backup_Cert_2020.ppk’,
DECRYPTION BY PASSWORD=‘Another strong password!!-23’)

‍

Normally add this information into Keepass along with the certificates.

‍

Use the Certificate to Backup the Database with Encryption

BACKUP DATABASE ProdDB01 TO DISK = ‘C:\Backups\ProdDB01.bak’
WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = Backup_Cert_2020)

‍

It is also possible to encrypt any backups in the same way when the backup process is carried out within SQL Server Management Studio Backup GUI.

‍

Restore the Database

To restore the database there is no need to specify any arguments about using the certificate, just use the standard restore command.

RESTORE DATABASE ProdDB01 FROM DISK = ‘C:\Restores\ProdDB01.bak’

‍

When the backup is restored, SQL Server will look at the header of the backup file and this has the ThumbPrint of the certificate that was used to encrypt the backup.

The above ThumbPrint is then checked against the sys.certificates table to see if a Certificate exists that can decrypt the backup file. If not matched, then the restore cannot be carried out.

‍

Restoring a database without having the encryption certificate on the server results in the error Cannot find server certificate with thumbprint

‍

Certificate Expiry

When a certificate is created it has a default lifetime of 1 year.

SELECT	Name,
		Start_Date,
        Expiry_Date
FROM	sys.certificates
WHERE	Name = 'Backup_Cert_2020'

‍

It’s important to note that once the expiry date has been reached, any backups that are taken using the certificate will fail. This is not fail to encrypt, it will actually fail to carry out the database backup.

However, the expiry date doesn’t apply to restores. Backups can be restored after the expiry date of the certificate has been reached.

To specify an expiry date that is longer than one year, simply use the EXPIRY_DATE option with a date format in the following format.

USE MASTER
GO
CREATE CERTIFICATE Backup_Cert_2020 WITH SUBJECT = ‘Certificate for encrypting backups’, EXPIRY_DATE = ‘20340101’

‍

There is also a START_DATE option, and this just defaults to the current time if it is not specified.

‍

‍