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 BackupEncrypted.

-- create test database
IF DB_ID('BackupEncrypted') IS NOT NULL
    DROP DATABASE BackupEncrypted
GO
CREATE DATABASE BackupEncrypted
ON PRIMARY
(NAME = BackupEncrypted_data,
    FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
    SIZE = 100MB, FILEGROWTH = 10MB),
FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA
  ( NAME = BackupEncrypted_MemoryOptimized,
    FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized')
LOG ON
  ( NAME = BackupEncrypted_log,
    FILENAME = N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
    SIZE = 100MB, FILEGROWTH = 10MB)
GO

Create a Test Table

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

USE [BackupEncrypted]
GO
-- create test table and insert one record
IF OBJECT_ID('dbo.testTable', 'U') IS NOT NULL
    DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
(
 id UNIQUEIDENTIFIER default NEWID(),
 parent_id UNIQUEIDENTIFIER default NEWSEQUENTIALID()
);
GO

SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO

SELECT * FROM dbo.testTable ORDER BY id;

The data content is as follows:

Create the Master Key and Certificate

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

USE master
GO
-- If the master key is not available, create it. 
IF NOT EXISTS (SELECT * 
                FROM sys.symmetric_keys
                WHERE name LIKE '%MS_DatabaseMasterKey%') 
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; 
END 
GO

USE master
GO
-- create certificate
CREATE CERTIFICATE MasterCert_BackupEncrypted
AUTHORIZATION dbo
WITH SUBJECT = 'Backup encryption master certificate',
START_DATE = '02/10/2017',
EXPIRY_DATE = '12/30/9999'
GO

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.

USE master
GO
EXEC sys.xp_create_subdir 'C:\Tmp'

-- then backup it up to local path
BACKUP CERTIFICATE MasterCert_BackupEncrypted 
TO FILE = 'C:\Tmp\MasterCert_BackupEncrypted.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Tmp\MasterCert_BackupEncrypted.key',
    ENCRYPTION BY PASSWORD = 'aa11@@AA')
;

Encrypt the Full Backup

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

USE master;
GO
-- do full backup database with encryption
BACKUP DATABASE [BackupEncrypted]  
TO DISK = N'C:\Tmp\BackupEncrypted_FULL.bak'  
WITH COMPRESSION, ENCRYPTION (
    ALGORITHM = AES_256, 
    SERVER CERTIFICATE = MasterCert_BackupEncrypted),
    STATS = 10;
GO

Encrypt the Differential Backup

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

USE [BackupEncrypted]
GO
-- insert another record
SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO

SELECT * FROM dbo.testTable ORDER BY id;

USE master;
GO
--Differential backup with encryption
BACKUP DATABASE [BackupEncrypted]
TO DISK = N'C:\Tmp\BackupEncrypted_DIFF.bak'
WITH CONTINUE_AFTER_ERROR,ENCRYPTION (
    ALGORITHM = AES_256, 
    SERVER CERTIFICATE = MasterCert_BackupEncrypted),
    STATS = 10,
    DIFFERENTIAL;
GO

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

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.

USE BackupEncrypted
GO
-- insert another record
SET NOCOUNT ON;
INSERT INTO dbo.testTable DEFAULT VALUES;
GO

SELECT * FROM dbo.testTable ORDER BY id;

USE master;
GO
-- backup transaction log with encryption
BACKUP LOG [BackupEncrypted]
TO DISK = N'C:\Tmp\BackupEncrypted_log.trn'
WITH CONTINUE_AFTER_ERROR,ENCRYPTION (
    ALGORITHM = AES_256, 
    SERVER CERTIFICATE = MasterCert_BackupEncrypted),
    STATS = 10;
GO

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

View Backup History

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

use msdb
GO
-- check backups
SELECT 
    b.database_name,
    b.key_algorithm,
    b.encryptor_thumbprint,
    b.encryptor_type,
    b.media_set_id,
    m.is_encrypted, 
    b.type,
    m.is_compressed,
    bf.physical_device_name
FROM dbo.backupset b
INNER JOIN dbo.backupmediaset m 
    ON b.media_set_id = m.media_set_id
INNER JOIN dbo.backupmediafamily bf 
    on bf.media_set_id=b.media_set_id
WHERE database_name = 'BackupEncrypted'
ORDER BY b.backup_start_date  DESC

The backup history information is shown as follows:

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.

USE master
GO
-- before clean environment, try to get backup files meta info, will be success
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

The following is a figure of some results:

Clean up the Environment

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

use master
GO
-- let's try to simulate a database crash, here we just drop this database.
DROP DATABASE [BackupEncrypted];
GO
-- and clean certificate and master key to simulate restore to a new instance.

DROP CERTIFICATE MasterCert_BackupEncrypted;
GO

DROP MASTER KEY;
GO

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.

USE master
GO
-- try to get backup files meta info again after clean environment, will be not success now.
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

The error message is similar to the following:

Msg 33111, Level 16, State 3, Line 178
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 178
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 179
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 179
RESTORE HEADERONLY is terminating abnormally.
Msg 33111, Level 16, State 3, Line 181
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 181
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 182
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 182
RESTORE HEADERONLY is terminating abnormally.
Msg 33111, Level 16, State 3, Line 184
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 184
RESTORE FILELIST is terminating abnormally.
Msg 33111, Level 16, State 3, Line 185
Cannot find server certificate with thumbprint '0xA938CE32CC86DFA6EAD2AED9429814F1A4C683ED'.
Msg 3013, Level 16, State 1, Line 185
RESTORE HEADERONLY is terminating abnormally.

The following is a figure of some error messages:

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.

USE master
GO
-- so we have to re-create master key, the certificate and open the 
IF NOT EXISTS (SELECT * 
                FROM sys.symmetric_keys
                WHERE name LIKE '%MS_DatabaseMasterKey%') 
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey*'; 
END 
GO

use master
GO
-- re-create certificate
CREATE CERTIFICATE MasterCert_BackupEncrypted
FROM FILE = 'C:\Tmp\MasterCert_BackupEncrypted.cer'
WITH PRIVATE KEY (FILE = 'C:\Tmp\MasterCert_BackupEncrypted.key',
DECRYPTION BY PASSWORD = 'aa11@@AA');
GO

Check Backup File Information

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

USE master
GO
-- after re-create certificate, try to get backup files meta info again, will be success.
RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_FULL.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_DIFF.bak'

RESTORE FILELISTONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'
RESTORE HEADERONLY FROM DISK='C:\Tmp\BackupEncrypted_log.trn'

Restore Encrypted Full Backup Files

First, try to restore full database backup files.

USE [master]
-- restore encrypted full backup
RESTORE DATABASE [BackupEncrypted] 
FROM  DISK = N'C:\Tmp\BackupEncrypted_FULL.bak' 
WITH FILE = 1,  
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD,  STATS = 5, NORECOVERY
GO

Restore Encrypted Differential Backup Files

Then, try to restore differential database backup files.

-- Restore encrypted diff backup
RESTORE DATABASE [BackupEncrypted] 
FROM  DISK = N'C:\Tmp\BackupEncrypted_DIFF.bak' WITH  FILE = 1,  
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD,  STATS = 5, NORECOVERY
GO

Restore Encrypted Log Backup Files

Next, try to restore database log backup files.

-- restore encrypted transaction log backup
RESTORE LOG [BackupEncrypted] 
FROM  DISK = N'C:\Tmp\BackupEncrypted_log.trn' WITH  FILE = 1,  
MOVE 'BackupEncrypted_data' TO N'E:\SQLDATA\DATA\BackupEncrypted_data.mdf',
MOVE 'BackupEncrypted_MemoryOptimized' TO N'E:\SQLDATA\DATA\BackupEncrypted_MemoryOptimized',
MOVE 'BackupEncrypted_log' TO N'E:\SQLDATA\DATA\BackupEncrypted_log.ldf',
NOUNLOAD,  STATS = 10
GO

Check the Data in the Test Table

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

USE [BackupEncrypted]
GO
-- double check the three records
SELECT * FROM dbo.testTable ORDER BY id;

The three pieces of data are verified to be consistent.

Clean up the Test Environment

Clean up the test environment.

use master
GO
-- clean up the environment
DROP DATABASE BackupEncrypted;
GO
DROP CERTIFICATE MasterCert_BackupEncrypted;
GO
DROP MASTER KEY;
GO

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

: Dikky Ryan Pratama May 8, 2023 at 3:51 pm

I wanted to take a moment to express my gratitude for the wonderful article you recently published on Alibaba Cloud Blog. Your writing was engaging and insightful, and I found myself fully immersed in the content from start to finish.The way you presented the information was both informative and easy to understand, which made it an enjoyable read for me. Your hard work and dedication to providing high-quality content are truly appreciated.Thank you once again for sharing your knowledge and expertise on this subject. I look forward to reading more of your work in the future.

0 0

Community

Database Backup Encryption in MSSQL

Procedure

Create a Test Database

Create a Test Table

Create the Master Key and Certificate

Backup the Certificate

Encrypt the Full Backup

Encrypt the Differential Backup

Encrypt the Log Backup

View Backup History

View Backup File Information

Clean up the Environment

View Backup File Information Again

Restore Certificate Files

Check Backup File Information

Restore Encrypted Full Backup Files

Restore Encrypted Differential Backup Files

Restore Encrypted Log Backup Files

Check the Data in the Test Table

Clean up the Test Environment

Summary

References

Read previous post:

Read next post:

Cherish Wang

You may also like

Comments

Dikky Ryan Pratama May 8, 2023 at 3:51 pm

Cherish Wang

Related Products

Database Overview

Tair

Database Security Solutions

Data Encryption Service