2 SQL Server 2014: Backup Encryption

From SQL Server 2014, SQL Server support the backup encryption . Which means we can encrypted our backup file to enhance the security for the backup media.

• Backup compression can also work with encryption.

prerequisites

Create the master key and certificate. Those will be used when we need to backup and restore the database.

---- Create Master key

USE [master];

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word'

GO

--- Crete Certificate

CREATE CERTIFICATE BackupCert

   WITH SUBJECT = 'BackupCert';

GO

select * from sys.certificates

GO

Use [master]

GO

--- Backup certificate

BACKUP CERTIFICATE BackupCert 

    TO FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

    WITH PRIVATE KEY ( 

        FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 

    ENCRYPTION BY PASSWORD = 'Pass@word');

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey'

GO

sys.certificates

Create test Database

Create the test database and insert some sample data then backup without encryption.

--- crete test database

create database BackupEncryptionDB;

go 

use [BackupEncryptionDB]

go

CREATE TABLE t1 (c1 int,c2 varchar(20))

GO

INSERT INTO t1 VALUES

        ('1', 'Summer')

        ,('2', 'Sunny')

        ,('3','Skye');

GO

Backup database BackupEncryptionDB 

to disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.bak'

with init, STATS=10;

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.bak'

GO

If we open the non backup file with notepad, we can find our data is present in the backup file as clear text.

Backup database with encryption

BACKUP DATABASE [BackupEncryptionDB]

TO DISK = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'

WITH

  init,format,

  ENCRYPTION (

   ALGORITHM = AES_256,

   SERVER CERTIFICATE = BackupCert

   ),

  STATS = 10

GO

EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'

GO

The content of the backup file has been scramble by encryption.

BackupSET metadata

SELECT backup_set_id, name, backup_size, compressed_backup_size, key_algorithm, 

encryptor_thumbprint, encryptor_type 

FROM msdb.dbo.backupset

GO

SELECT media_set_id, is_password_protected, 

is_compressed, is_encrypted 

FROM msdb.dbo.backupmediaset

GO

Restore encrypted database backup

To simulate restore to another server or if the server certificate is lost for some reason. Lets drop the certificate and restore.

--- Remove the certificated to simulate in other server

DROP CERTIFICATE BackupCert

go

select * from sys.certificates

go

Restore database [BackupEncryptionDB]

from disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'

with replace, stats=10

go

Because there is no certificated therefore SQL server can not validate the backup. Restore will fail with below error: 

Msg 33111, Level 16, State 3, Line 64
Cannot find server certificate with thumbprint '0x35FA80FB4FC5F4555B3F89FF914A98ABA212C5C3'.
Msg 3013, Level 16, State 1, Line 64
RESTORE DATABASE is terminating abnormally.

Lets restore the certificate and restore the database.

-- Restore the certificate

Use [master]

CREATE CERTIFICATE BackupCert

    FROM FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'

    WITH PRIVATE KEY ( 

            FILE =  'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 

    DECRYPTION BY PASSWORD = 'Pass@word' );

GO

Restore database [BackupEncryptionDB]

from disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'

with replace, stats=10

go

database successfully restore.