Backup Encryption | Part-2

In the first part we discussed the new feature of Backup Encryption which is introduced in SQL Server 2014, the post describes the benefits, restrictions, permissions required etc.

Now let’s see this in action, the below mentioned scripts will generate Service Master Key, Database Master key and a Certificate which will act as an encryptor and post that we’ll use the encryptor to take a backup with Encryption for a DB named Repro_FGDN.

 

–Regenerating Service Master Key
ALTER SERVICE MASTER KEY REGENERATE;
GO

–script to Backup Service Master key
BACKUP SERVICE MASTER KEY
TO FILE = ‘C:\backup\SQL2014_service_master_key.key’
ENCRYPTION BY PASSWORD = ‘ZlseImRtjSnuEiheYf8a’;
GO

–Creating Database Master Key also known as DMK
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘lseImRtjSnuEiheYf8’;
GO

–script to backup DMK
BACKUP MASTER KEY
TO FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b’;
GO

–Creating Certificate (Encryptor) which is encrypted by DMK
CREATE CERTIFICATE FGDN_DMK
   WITH SUBJECT = ‘FGDN_DMK_Subject’

–Script to Backup certificate as soon as it gets created.
BACKUP CERTIFICATE FGDN_DMK
TO FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
WITH PRIVATE KEY
        (
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , ENCRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’
        );
GO

–Script to backup Database using Encryption
BACKUP DATABASE repro_FGDN
TO DISK = N’C:\backup\Repro_FGDN_22.bak’
WITH INIT, CHECKSUM, COMPRESSION, STATS = 1
        , ENCRYPTION
        (
        ALGORITHM = AES_256
        , SERVER CERTIFICATE = FGDN_DMK
        )

This shows the backup completion from the Server named MBTEST.

MBTest

 

This is the screenshot of the Backup folder which consists backup of all Certificates, Keys along with Encrypted backup.

image

 

Learning\Points to consider:

1. Backup the Certificate\Keys as soon as you create them, you never know when you might need to recover the database and without these encryptors you won’t be able to recover the database.

2. Certificate to be used as an Encryptor for backup encryption should be encrypted by DMK only.

 

Thanks,
Sarabpreet Singh

Sarabpreet Singh Anand

SQLServer - MVP Sarabpreet is SQLServer MVP, Consultant, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 11+ years of Experience and worked with Industry Leaders. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”.

You may also like...

Leave a Reply