Database Backup Encryption in MSSQL

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;

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

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

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

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'

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

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;

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

Original Source

--

--

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
Alibaba Cloud

Alibaba Cloud

4.97K Followers

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com