Database Backup Encryption in MSSQL

Procedure

Create a Test Database

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

References

Original Source

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

So What Do Sea Shells Have To Do With Computer Security?

One Little Method Secures You On-line, Your CryptoWallet, And Your Wi-fi: Meet The Tortoise

{UPDATE} Mommy’s New Ice Princess Baby Doctor Hack Free Resources Generator

{UPDATE} WordsMatrix Hack Free Resources Generator

{UPDATE} House Flipper Hack Free Resources Generator

{UPDATE} New Quizduel Hack Free Resources Generator

Exposing FilxCoin / FilCoinX / FilecoinX (FILX) — A Case Study

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

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

More from Medium

Understanding Kubernetes + Docker

Build and run microservices with Kubernetes, Helm and Skaffold locally

MYSQL Database Instance on Red Hat Openshift

How to request data from different pods and services by using hostname and subdomain in Kubernetes