Encrypted Backups

For one of our client we were asked to provide the backups to a different team which was completely disconnected from the client network. We had to provide a copy of the backup file by putting the backup files in some ftp location.

When doing such we need to make sure the security of the backup file and in this case we used Encrypted backup.

To get the database encrypted we need to have a server certificate which is similar to one used in TDE. Steps to have a server certificate are as following:

Step 1

Create a server certificate.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd';
GO
CREATE CERTIFICATE BackupCert WITH SUBJECT = 'Backup for certificate';
GO

Step 2

When taking backup of the database use the above created server certificate.

BACKUP DATABASE AdventureWorks
TO DISK = N'S:\SQLBackup\adventureworks.bak'
WITH ENCRYPTION (
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCert
);

When restoring this database we need to have backup of the server certificate BackupCert restored at destination server. We shared this certificate by using a third party certificate store being used by the client.

I hope it helps!

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 )

Connecting to %s