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.

 

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

 

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