Database Backup Encryption in MSSQL

By Feng Yi.

When it comes to database security, preventing security risks due to database backup files being leaked is a very important security and prevention topic. This topic aims to ensure the security of user data in case the backup file of the user database leaks. Prior to SQL Server 2014, the industry commonly used TDE technology to implement and prevent files from being leaked out of the database. However, TDE works by encrypting all the user data before storing it to the disk, and decrypting it when it is read. This behavior of encrypting while writing and decrypting while reading will inevitably result in reduced query performance and increased CPU usage (for more information about the impact on the performance and CPU, see this article SQL Server Transparent Data Encryption (TDE) Performance Comparison). Then, is a technology available that can not only ensure the security of backup files, but also take into account the user query performance and CPU resource consumption? This is the database backup encryption technology that we are going to introduce today. This technology was first introduced in SQL Server 2014. The SQL Server 2014 Enterprise edition and Standard edition support backup encryption, while the SQL Server 2014 Web edition and Express edition support restoring encrypted backup files.

Procedure

Create a Test Database

To make the test simple, let’s create a test database called .

Create a Test Table

In the test database, create a test table , and insert a piece of random data.

The data content is as follows:

Image for post
Image for post

Create the Master Key and Certificate

Create the master key and certificate for encrypting database backup files.

Backup the Certificate

First, backup the certificate and certificate key files to the local machine. It is recommended that they should be saved offline to a third party host in case the host goes down unexpectedly and the certificate files are lost, resulting in an unrestorable encrypted backup file.

Encrypt the Full Backup

After the master key and certificate files are created, we can perform full backup encryption for the database.

Encrypt the Differential Backup

For the database differential backup encryption, we insert a data entry for subsequent test data verification before the backup.

Before the differential backup operation, verify the two pieces of data in the table, as shown in the following figure:

Image for post
Image for post

Encrypt the Log Backup

For the database transaction log backup encryption, we still insert a data entry for subsequent test data verification before the backup.

Before the log backup, verify the three pieces of data in the table, as shown in the following figure:

Image for post
Image for post

View Backup History

After the full backup, differential backup and log backup for the data are completed, view the backup history.

The backup history information is shown as follows:

Image for post
Image for post

From the data in the figure, we can see that all three backups use certificates for backup encryption.

View Backup File Information

After the backup history is viewed, check the metadata information of the backup file before cleaning up the test environment. And, it can be checked successfully without any errors.

The following is a figure of some results:

Image for post
Image for post

Clean up the Environment

The purpose of cleaning up the environment is to simulate restoring the database backup file on a new instance.

View Backup File Information Again

After the certificate and master key are cleaned up, check the backup file information again. An error is reported at this time, because the database backup file is encrypted. This error is what we expected. That is, even if the database backup file is leaked, the data can be absolutely secure and subject to unauthorized access.

The error message is similar to the following:

The following is a figure of some error messages:

Image for post
Image for post

Restore Certificate Files

Database backup encryption can effectively prevent the security risks of leaked database files. Valid users need to restore the encrypted backup file successfully on the new instance. First, create the master key. Then, re-create the certificate from the certificate backup file.

Check Backup File Information

Verify the backup file information. And it can already be read correctly.

Restore Encrypted Full Backup Files

First, try to restore full database backup files.

Restore Encrypted Differential Backup Files

Then, try to restore differential database backup files.

Restore Encrypted Log Backup Files

Next, try to restore database log backup files.

Check the Data in the Test Table

Finally, check the three pieces of test data in the test table.

The three pieces of data are verified to be consistent.

Image for post
Image for post

Clean up the Test Environment

Clean up the test environment.

Summary

In this tutorial, you have learned how to use certificates to implement database backup encryption in SQL Server 2014 and later. This encryption technology can not only prevent the security risk of leakage, but also can better ensure the query performance of users, and will not cause the consumption of additional CPU resources.

References

Original Source

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store