We all go to great lengths to make sure that our databases are secure (or at least I really hope that we do), we make sure that only authorised users have access and then only to the databases and levels that they need.  We’re careful that all our passwords conform to crazy complexity rules.  We’re a pretty security minded lot aren’t we?

But what about our backups?  Most of the time they’re just sitting on, either a local disk on the server itself or some share somewhere.  Wherever they are, what happens if someone manages to get hold of our backup files?  They can easily just restore them onto a server where they have permissions and voila, all our super secret data is now theirs.

They could even just open the files with a hex editor read the data, it’s really not a difficult thing to do.

By default, SQL Server makes absolutely no effort to encrypt or in any way protect our backups.  That’s pretty bad in my mind.

So how do we go about encrypting our backups?

If we want to encrypt our backups, the first thing that we’re going to want to do is create a certificate to encrypt the backup with.

Before we can create a certificate, we’re going to need to create a Database Master Key (DMK) in the master database.  Certificates can be encrypted using either a password or a DMK.

In the case of backups, the certificate used to encrypt them must firstly reside in the master database and secondly, be encrypted by the master database’s DMK.

Create the DMK using the following…

USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'D4t4b4s3'

obviously, the password can be any strong password that you choose.

Now that you’ve got a DMK, you can go ahead and create the certificate that we’re going to use to encrypt the backup.

CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Encryption Certificate'

Just to check that our certificate has successfully been created…

SELECT name, pvt_key_encryption_type, subject
FROM sys.certificates

As we can see, we’ve got a certificate named BackupCert that was encrypted using the DMK.

Right, now lets get backing up our database to an encrypted backup file,

BACKUP DATABASE SQLUndercover
TO DISK = '\\Backups\SQLUndercover.bak' WITH REPLACE
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupCert)

As you can see that it’s pretty much a standard backup statement with one little exception, the WITH ENCRYPTION clause.  We have to specify two things here, the encryption algorithm and the certificate that we want to use to encrypt the backup.

As of SQL 2016, the only algorithms that are supported are AES_128, AES_192 and AES_256.  All others are considered insecure and you should avoid using them.  The number after AES_ represents the algorithm’s key size, I’m not going to get into what that actually means here other than the bigger the number, the ‘potentially’ more secure the algorithm is, but on the flip side, it’ll also be slower.  To be honest, AES_128 is easily good enough for the vast majority of uses, unless you’re a government or military organisation what stipulates one of the other flavours.

So now that we’ve got our encrypted backup, do we need to do anything special when restoring it?

Well, as the certificate that was used to backup the database is on this server, there’s nothing special that we need to do so go ahead and just give have a go at restoring it.

RESTORE DATABASE SQLUndercover
FROM DISK = '\\Backups\SQLUndercover.bak'
WITH REPLACE

Processed 336 pages for database ‘SQLUndercover’, file ‘SQLUndercover’ on file 1.
Processed 2 pages for database ‘SQLUndercover’, file ‘SQLUndercover_log’ on file 1.
RESTORE DATABASE successfully processed 338 pages in 0.100 seconds (26.333 MB/sec).

So we’re happy that we can restore our backups, but what happens if someone comes in, steals our backup file and then has a go at restoring it onto a different server?

Give it a go and see what happens…

Msg 33111, Level 16, State 3, Line 11
Cannot find server certificate with thumbprint ‘0x0CCAD78D8D72004F34FB0D5466FBB2B4BA929AB0’.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

Because the certificate doesn’t exist on our little backup thief’s server, he can’t restore our backup and our data is now safe.

But hold on, what happens if we need to restore the backup onto another server for some reason?  Well to do that, we’ll need to first put the certificate onto that server.  To do this, we’ll need to backup the certificate (which is something that you should always do with any certificates that you create).

BACKUP CERTIFICATE BackupCert TO FILE = '\\Backup\Certificates\BackupCert.cer'

When you backup your certificates, make sure that you store them in a very secure place, these are essentially your encryption keys so you really don’t want anyone naughty getting hold of them.

You can now restore the certificate onto the second server,

CREATE CERTIFICATE BackupCert FROM FILE = '\\dc01\Quorum\BackupCert.c3'

Now that you’ve got the certificate on the server (you did remember that it needs to go into the master database, didn’t you?) you can have another go at restoring that encrypted backup file.

Msg 15507, Level 16, State 30, Line 11
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

erm…. what’s that all about???  Well, the certificate, itself is encrypted by a private key.  In order to be able to decrypt the certificate, we’re going to need to export the private key.  It just so happens that we can easily export that private key at the same time as backing up the certificate.

BACKUP CERTIFICATE BackupCert TO FILE = '\\Backup\Certificates\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\Backup\Certificates\BackupCert.pvk', ENCRYPTION BY PASSWORD = 'D4t4b4s3')

Notice that we’ve now included the private key in the backup statement, you’ll need to give a password to encrypt the private key with (keep this safe as you’ll need it when you import it into the second server).

Lets get it imported, first drop the certificate that we restored earlier and restore it again but this time also importing the private key.

CREATE CERTIFICATE BackupCert
FROM FILE = '\\Backup\Certificates\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\Backup\Certificates\BackupCert.pvk', DECRYPTION BY PASSWORD = 'D4t4b4s3')

So now that we’ve got both the certificate and the private key on the second server, we should now be able to restore the database.

Processed 336 pages for database ‘SQLUndercover’, file ‘SQLUndercover’ on file 1.
Processed 2 pages for database ‘SQLUndercover’, file ‘SQLUndercover_log’ on file 1.
RESTORE DATABASE successfully processed 338 pages in 0.179 seconds (14.711 MB/sec).