Backup Encryption in SQL Server 2014 Part 2 – Keys management (Backup methods)

In my previous article Backup Encryption in SQL Server 2014 Part 1, I have shown backup encryption steps.

In the current article, I will show you how to manage keys.

How to backup a master key?

Syntax

BACKUP MASTER KEY TO FILE = ‘path_to_file’

ENCRYPTION BY PASSWORD = ‘password’

Let’s backup master key that we have created in our test database. You can specify your secured network share path. In this demo I am saving on my local drive.

— Backup master key.

use master

GO

BACKUP MASTER KEY TO FILE = ‘C:\backups\Keybackups\mymasterkeybackup.key’

ENCRYPTION BY PASSWORD = ‘Password01!’

Now backup certificate.

For syntax explanation please refer to msdn help.

Syntax

BACKUP CERTIFICATE certname TO FILE = ‘path_to_file’    [ WITH PRIVATE KEY       (         FILE = ‘path_to_private_key_file’ ,        ENCRYPTION BY PASSWORD = ‘encryption_password’         [ , DECRYPTION BY PASSWORD = ‘decryption_password’ ]       )     ]

— Backup certificate

use master

GO

BACKUP CERTIFICATE Backup_Certficate_mydatabases TO FILE = ‘C:\backups\Keybackups\Backup_Certficate_mydatabases.cer’

    WITH PRIVATE KEY ( FILE = ‘C:\backups\Keybackups\Backup_Certficate_mydatabases_private.key’ ,

    ENCRYPTION BY PASSWORD = ‘Password01!’ );

GO

Note: backup location could be  unc or network share. Before you place backup files on the folder ask your security team to review.

In my next article I will explain how to restore keys.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s