******** IMPORTANT WARNING ********

DO NOT ENCRYPT ANYTHING WITHOUT FIRST TAKING BACKUPS OF ALL KEYS, BACKUP THE CERTIFICATE, AND NOTE THE PASSWORDS USED.  

When dealing with encryption of any kind in SQL Server it all begins with the Service Master Key (SMK). The SMK is created the first time any other key is required to be encrypted or created and is the root of the SQL Server encryption hierarchy.

The first step in the configuration of encryption is to create the Master Key (MK), which will also create the SMK. This is easily done by using the following T-SQL:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Some password here’;

It should be noted that a password is supplied to create the Master Key. This can be any password you would like, it must however be recorded and stored in the event of disaster.

The next step is to create the Certificate which will be used to encrypt the database. The certificate is generated using the following T-SQL:

CREATE CERTIFICATE <Certificate Name> WITH SUBJECT = ‘Backup Certificate’;

The create certificate command requires the certificate name and the subject fields to be completed. Both the certificate name and the subject can be any text desired, however it is recommended use meaningful names.

At this point the backups can be configured for encryption using the Certificate that has been created.

Do not encrypt any backups until all the keys and the certificate are backed up.

As with any type of encryption if the password is forgotten the data that was encrypted is now no longer accessible and becomes the equivalent of a file system paperweight. The same will happen in SQL Server the Service Master Key, Master Key, and the certificate are all the “key” to unencrypting anything that was encrypted with them. The combination of the loss of these items and a single failure could potentially cause a complete irreversible data loss.

 

The following T-SQL will create the backups for the Keys generated and the certificate:

USE master
GO
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_fileservice_master_key.bak' ENCRYPTION BY PASSWORD = ‘password'
GO
BACKUP MASTER KEY TO FILE = 'path_to_filemaster_key.bak' ENCRYPTION BY PASSWORD = ‘password’
GO
BACKUP CERTIFICATE <Certificate Name> TO FILE = 'path_to_filecertificate.cer' WITH PRIVATE KEY (FILE = 'path_to_fileprivate_key.pvk', ENCRYPTION BY PASSWORD = 'password')
GO

Both the SMK and MK backups only require the path to where the backup file will be located and a password, it is critically important to save the password used also.

The Certificate backup works a little differently. Again we include the name we gave the certificate when it was created, the path to where we will keep the backup, and then we create a private key.

The private key is used to encrypt the certificate backup. Without the private key or its password the certificate will not be able to be recovered. We only need to provide a path to store the private key and a password to generate the private key. Again the files and passwords must be saved and documented properly in order for any recovery to be possible.

We have now created all the required keys and certificates, they have been backed up, the passwords saved in a sage place, and we are ready to encrypt some backups.

If the backups are configured through maintenance plan to encrypt them update the maintenance plan in SQL Server Management Studio (SSMS). In the backup step there is an options tab which contains a check box for encrypting the backups, check this box and select the certificate from the drop down.

If backups are run off a script the T-SQL for the backups will also need to include the following T-SQL:

WITH ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = <Certificate Name>
),
 

A complete example

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘passwprd;
GO
CREATE CERTIFICATE BackupCer WITH SUBJECT = 'Backup Certificate';
GO
USE master
GO
BACKUP SERVICE MASTER KEY TO FILE = 'd:backupsservice_master_key.bak' ENCRYPTION BY PASSWORD = ‘password'
GO
BACKUP MASTER KEY TO FILE = ' d:backups master_key.bak' ENCRYPTION BY PASSWORD = ‘password’
GO
BACKUP CERTIFICATE <Certificate Name> TO FILE = ' d:backups certificate.cer' WITH PRIVATE KEY (FILE = ' d:backups private_key.pvk', ENCRYPTION BY PASSWORD = 'password')
GO
BACKUP DATABASE [MYTestDB] TO DISK = N'd:backupsMyTestDB.bak'
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCer
),
STATS = 10

 

It is very important to keep a copy of the backed up keys and certificates, keeping three copies is recommended. Keep a copy local on the server, a copy should be stored to tape and offsite, and a third copy should be burned to a CD or DVD and stored in a locked safe (the one you keep your domain password in).

Share This