SQL Server 2017 Encrypted Backups And Compression

Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.

To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:

  • Encryption Algorithm: AES 256
  • Encryptor: A certificate

First step is to create a certificate.


USE master;
GO
CREATE CERTIFICATE DBBackupEncryptCert
   WITH SUBJECT = 'DBBackupEncryptCert ';
GO 

Next step, Issue the backup command and state the certificate (above) and the algorithm choice. As you can see I opt for AES_256, the algorithms are implemented using the Windows Crypto API.

This is the main code.

BACKUP DATABASE [AdventureWorks2020]
TO DISK = 'C:\SQLSERVER\backups\AdventureWorks2020Full2.bak'
WITH
  COMPRESSION,
  COPY_ONLY,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = DBBackupEncryptCert
   ),
  STATS = 5
GO

Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t?  You will get the dreaded thumbprint error.

Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint ‘0x78FAB5A2A5D593FD3C4E163C90B745F70AB51233’. Msg 3013, Level 16, State 1, Line 25

RESTORE DATABASE is terminating abnormally.

So make sure you respect this certificate (and the key) and back it up and re-create them on the target server for a successful restore.

 -- SOURCE SERVER
USE master
GO
BACKUP CERTIFICATE DBBackupEncryptCert
TO FILE = 'C:\SQLSERVER\backups\DBBackupEncryptCert.cer'
WITH PRIVATE KEY
(FILE = 'C:\SQLSERVER\backups\DBBackupEncryptKey.pvk',
ENCRYPTION BY PASSWORD = 'Hslfm30dBLOB1278EAT2!')

 -- TARGET SERVER
CREATE CERTIFICATE DBBackupEncryptCert
FROM FILE = ' C:\SQLSERVER\backups\DBBackupEncryptCert.cer'
WITH PRIVATE KEY (FILE = ' C:\SQLSERVER\backups\DBBackupEncryptKey.pvk',
DECRYPTION BY PASSWORD = 'Hslfm30dBLOB1278EAT2!');
GO

USE [master]
RESTORE DATABASE [AdventureWorks2020]
FROM  DISK = ' C:\SQLSERVER\backups\AdventureWorks2020Full2.bak 'WITH  FILE = 1, STATS = 5

RESTORE DATABASE successfully processed 24266 pages in 1.038 seconds (182.634 MB/sec).

What effect does this have on backup compression?

I ran the following 3 backup commands, first one with no extra options, second command with compression then finally compression with encryption – the question is does encryption on the backup affect compression rates?

-- NOTHING - NAKED
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull1.bak'
WITH  

 COPY_ONLY,
 STATS = 5
GO  

-- COMPRESSION ONLY
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull2.bak'
WITH  

  COPY_ONLY,
  COMPRESSION,
  STATS = 5
GO  

-- WITH COMPRESSION AND ENCRYTPION
BACKUP DATABASE  [NEWdb]
TO DISK = 'C:\SQLSERVER\backups\NewDBFull3.bak'
WITH
  COMPRESSION,
  COPY_ONLY,
  ENCRYPTION
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = DBBackupEncryptCert
   ),
  STATS = 5
GO

A picture (below) speaks a thousand words.

BackupSizes

 

 

4 thoughts on “SQL Server 2017 Encrypted Backups And Compression

  1. Pingback: Backup Compression And Encryption – Curated SQL

  2. actually, you first need to create a database master key in the master database if it doesn’t exist already (which it likely won’t)
    Also, when you create the certificate, set the authorization
    and provide a start and end date.

    Backup the certificate first before you do anything else.

    If moving to a new server, don’t forget to create a DMK there first before restoring the certificate.

    For more, see my article at this link

    http://www.sqlservercentral.com/articles/Encryption/109028/

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s