Encrypting a SQL Server Database Backup

Encrypting a SQL Server Database Backup

Encrypting a SQL Server Database Backup

Share on Facebook8Share on Google+0Tweet about this on TwitterShare on LinkedIn2Share on Reddit0

Encrypting a SQL Server database backup is necessary in many cases, especially when the database has sensitive data.

SQL Server provides an easy way to encrypt database backups.

Let’s further examine this functionality with a step-by-step example.

In this example, we are going to backup a SQL Server 2014 database, encrypt it, and then restore it on a SQL Server 2016 instance. The sample database’s name is “TestDB1” (not quite an original name for a database 🙂

In SQL Server Management Studio, if we right-click on the database and go to “Tasks”, “Back Up…”, we are presented with the well-known backup dialog:

SQL Server Database Backup Encryption - Screen 1
If we go to “Backup Options” we see that there is a checkbox named “Encrypt Backup”. By default, this is not available as you prior need to set a new media set in “Media Options”. It will be made available, only if you set a new media set.

 

So, we now navigate to “Media Options”, and select to Backup to a new media set by setting up the new media set’s name and description:
SQL Server Database Backup Encryption - Screen 2
Now, if we navigate back to “Backup Options”, we can see that we can check the “Encrypt Backup” checkbox and select an encryption algorithm, but still we need a backup certificate or asymmetric key:
SQL Server Database Backup Encryption - Screen 3
So, we go back to the query window and create a backup certificate for the database as per below example (always use strong passwords, oh well, at least stronger than the one in this example 🙂

 

--Create Database Master Key and Encrypt it with a Strong Password
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyComplexMasterKeyPassword';
GO
--Create Backup Certificate
USE master;
GO
CREATE CERTIFICATE TestDB1BackupEncryptCert
WITH SUBJECT = 'TestDB1 Backup Encryption Certificate';
GO
--IMPORTANT NOTE: It is critical that you backup the master DB key and the database backup certificate to a secure location
--Backup Master DB Key
BACKUP MASTER KEY
TO FILE = 'c:\tmp\MasterKey.key'
ENCRYPTION BY PASSWORD = 'S3curePass!';
GO
--Export the Backup Certificate to a File
BACKUP CERTIFICATE TestDB1BackupEncryptCert TO FILE = 'c:\tmp\TestDB1Cert.cert'
WITH PRIVATE KEY (
FILE = 'c:\tmp\TestDB1CertKey',
ENCRYPTION BY PASSWORD = 'S3curePassCert!')

Note that the above file keys are created by the service account that runs SQL Server Database Engine and it is the only user that has full access. In order to get access to these files, if you are a local administrator on the machine running SQL Server, you can do so by editing the permissions (via Advanced dialog).

Now, let’s try again to take an encrypted backup of the database:

 

SQL Server Database Backup Encryption - Screen 4

SQL Server Database Backup Encryption - Screen 5
As you can see, backup was successful. Now, the backup file create is encrypted with the newly created backup certificate.

 

Now, let’s try to restore the encrypted database backup set to another instance of SQL Server (instance name: “DEMO-PC1”, it is a default instance name).

 

First, for illustration purposes, let’s just try to restore it without creating a backup certificate on the destination SQL Server instance:
Restoring Encrypted SQL Server Databases
As you  can see, it was not possible to restore the encrypted database.

 

Now, let’s try again but this time prior to running the restore command, we re-create the master DB key as well as the database backup certificate (based on the exported cert/key) on the destination SQL Server instance:

 

--Recreate master DB key on destination SQL Server instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3curePass!';
GO
--Restore the Certificate Based on the Previously Exported Key/Cert files
CREATE CERTIFICATE TestDB1BackupEncryptCert
FROM FILE = 'c:\tmpBackups\keys\TestDB1Cert.cert'
WITH PRIVATE KEY (FILE = 'c:\tmpBackups\keys\TestDB1CertKey',
DECRYPTION BY PASSWORD = 'S3curePassCert!');
GO
--Restore Encrypted Database 'TestDB1'
RESTORE DATABASE [TestDB1]
FROM DISK = 'c:\tmpBackups\TestDB1.bak'
WITH MOVE 'TestDB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Data.mdf', 
MOVE 'TestDB1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Log.ldf';
GO

As you can see, now the encrypted database has been successfully restored on the destination SQL Server instance:

Restoring and Encrypted SQL Server Database

This article explained by example, a way of encrypting a SQL Server database backup using SQL Server’s built-in security mechanisms and restoring it in another SQL Server instance. Encryption is a very powerful feature in SQL Server and must be used wisely.

 

SQL Server is a powerful data platform that provides all the necessary mechanisms for achieving almost anything you would like to do with your data. All these mechanisms are built-in and can be easily used just like in this example, where we encrypted a database backup and then restored it on another SQL Server instance.

 

For the list of my articles on SQL Server Security, click here.

 

If you are interested in learning more about SQL Server administration, check out the second edition of my eBook, “Administering SQL Server“.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 5.00 out of 5)
Loading...

Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.