As security becomes more and more important to data professionals, one of the more basic things a DBA or sysadmin must do is protect the backup files for their databases. SQL Server provides a few ways to encrypt backups, and there are numerous third party tools, such as SQL Backup, that perform encryption as well. In this article, we will look at the native backup encryption available in SQL Server 2014 and above. We will use a certificate supplied by a third party as opposed to a self generated certificate inside SQL Server.

Note: Certificate and key management is a complex task. It is easy to make a mistake in securing and rotating your certificates. Please be sure you protect your certificates and passwords carefully or your backup encryption may not be as valuable as you expect.

Getting a Certificate into SQL Server

I will not cover the myriad of ways that you can get a certificate for use with SQL Server. There are numerous third parties that you can contact to create certificates, as well as lots of software that will achieve this task. The specifications that you need to use for a certificate with SQL Server are:

• A DER formatted certificate file
• A private key file less than 2500 bytes in encrypted format

Not all certificates meet these requirements, so ensure whatever certificate you wish to use does so.

To get my external certificate, I will use the makecert.exe utility from the Windows SDK. This is an older utility, but it makes generating the two certificate files very easy. The command I used is:

makecert -sv "c:\EncryptionPrimer\MyBackupCert.pvk" -pe -a sha1 -b "01/01/2019" -e "12/31/2019" -len 2048 -r -n CN="SQL Backup Certificate" c:\EncryptionPrimer\MyBackupCert.cer

This creates a certificate, as you can see here:

And the files exist in the (older) VM I used to create a certificate.

The next step for me was to copy both these files to a place my SQL Server can access. While I use SSMS to run the code, the database server has to read in the files itself. I'll put these in the /data folder for me instance.

Once the files were there, I can connect to my SQL Server 2017 instance and load the certificate. Before I do that, I need a master key in the master database. This is similar to the process used when enabling TDE. We create the master key with a password, which you need to be sure is saved and protected.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeT4stP@ssword'

Once this is complete, we can load the certificate. To do this, we use the CREATE CERTIFICATE command, giving paths to the files. Here is the command I use, noting that I must include the password I entered when creating the certificate using makecert.

CREATE certificate MyBackupCert
 from file = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyBackupCert.cer'
 WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\MyBackupCert.pvk'
                 , DECRYPTION BY PASSWORD = 'MyStr0ngC#rtific@teP@ssword'
 )
GO

When this code runs, the certificate is loaded into SQL Server. I can see it in the Certificates folder inside the Security section of the master database. It also is visible in the sys.certificates DMV.

That's the setup, not let's create an encrypted backup.

Adding Encryption to Your Backup

By default SQL Server writes pages out to disk for a backup in an un-encrypted format. We can see this by examining a backup file using a hex editor. I will create a backup file and then open it in xvi32. First, I'll create the backup.

Next we'll open it in the editor. As you can see below, there is a header that notes this is a Microsoft SQL Server file in the ASCII decoding on the right.

If we scroll down a little, we will actually see the name of the database and instance on the right. In this case, the database was "sandbox" and the instance is [Plato\SQL2017].

To encrypt a backup, we add the WITH ENCRYPTION clause to the backup command. We must then specify the algorithm and the method of encryption for the key used in the encryption. As with other encryption methods, a symmetric key encrypts the data and an asymmetric key encrypts the symmetric key. In this case, I chose AES 256 bit and specified by certificate.

BACKUP DATABASE sandbox TO DISK = N'encryptedbackup.bak'
WITH ENCRYPTION (ALGORITHM = AES_256
               , SERVER CERTIFICATE = MyBackupCert
   )
GO

This runs, and we can then open up the backup file in our editor. We can see lots of random encryption in here that we can't really read. There is a few values that look like column names, but I can't find any data in my database here.

At this point, my backup is protected. The only way to get this data decoded is to load the certificate on a SQL Server instance and then restore this file. To get the certificate, the password used in creating the certificate is needed, so be sure you protect that.

Restoring an Encrypted Backup

The next test is to see what happens with this file on another system. I have a SQL Server 2019 instance handy and I'll use that to try a restore. I copy the backup file to my other instance and then connect in SSMS. In this case, it's a brand new test instance with no databases.

Using the restore dialog in SSMS, I'll select my backup file below.

When I do this, I get an error in the restore dialog.

The header cannot be decoded, so the backup set cannot be read. In order to read this, we must install the certificate on this instance. I will use the same code as before, after copying the two certificate files to the new path. In this case, the path changes slightly, but the effect is the same. I also need to ensure there is a master key in the master database.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Blu3Corn'
GO
-- load the certificate
CREATE certificate MyBackupCert
 from file = N'C:\Program Files\Microsoft SQL Server\MSSQL15.PACKT\MSSQL\DATA\MyBackupCert.cer'
 WITH PRIVATE KEY (FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.PACKT\MSSQL\DATA\MyBackupCert.pvk'
                 , DECRYPTION BY PASSWORD = 'Blu3Corn'
 )
GO

The certificate appears in the DMV:

Now when I return to the restore dialog and re-select the file, I see this:

The rest of the restore process is the same as it would be for any backup. The backup completes and the database is accessible.

Conclusion

This article showed the basics of using a third party certificate to protect an encrypted SQL Server database backup. We loaded a certificate created outside of SQL Server and used this for the backup and restore.

The hardest part of working with certificates is managing them across time and tracking the passwords that protect the private keys. Secondarily, getting a DER format, which was a challenge when I created a certificate with PowerShell and then tried to export it. Certificate handling is cumbersome on Windows, and only slightly easier on Linux, but once you get the proper formats, using a certificate is easy.

Let me point out there is no reason not to use a certificate generated inside SQL Server for this encryption purposes. This is the same format generated by third parties or software tools and in this case, there is no need for a chain of authenticity as there are in other cross system authentication mechanisms.

If you have any questions, please feel free to leave them in the discussion below.