James X. from Learning Tree’s Course 535 Developing High Performance SQL Server Databases asked a question (during coffee break) that wasn’t related to performance, but was important nonetheless. “Is it true that someone can circumvent a lot of SQL Server’s security by simply restoring a database backup to a different machine where he or she has administrative privileges?” Sadly, this is a real concern and a demonstration of one of Dan’s Laws: “If you don’t have physical security, you don’t have security at all.” In other words, backups need to be locked up. (In an offsite facility with ample fire and flood protection, but we won’t get into that story now.) SQL Server 2014 provides a new (some might say overdue) capability for addressing this security issue.
New for SQL Server 2014 is the capability of making encrypted backups, which is not to be confused with backing up an encrypted database. Making encrypted backups is easy, but there is a price in terms of greater administrative efforts. There will be new passwords, keys, and certificates, and losing them is about as serious as losing your job. The two may, in fact, be related.
In order to create an encrypted backup, and to restore a database from an encrypted backup, we must prove to SQL Server we are who we claim to be. What better way to do this than with a certificate?
To create a certificate, the master database must have a master key defined. If you have already created a master key for this database, you will need to open it to create a certificate. Here is the code to create a master key:
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘masterdatabasemasterkeypw’;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘masterdatabasemasterkeypw’ — necessary if you are no longer in the same session that created the key
BACKUP MASTER KEY TO FILE=‘C:\Temp\masterdatabasemasterkey’ — will not overwrite existing file
ENCRYPTION BY PASSWORD=‘masterdatabasemasterkeyfilepw’ — necessary
If you have just created a master key, the key will be open for the session that created it. If you are on another session, you will need to open the key for any operation that requires it. Of course, we immediately back up a key when we create one. This should be as natural as reaching for the French vanilla ice cream when you have warm apple pie.
Note that we now have two new passwords that require care and protection, the password for the master key itself and the password for the file that contains the master key backup.
Similarly, we create a master key for the database we will be backing up.
USE MyMusic
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘masterkeypw’
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘masterkeypw’ — necessary if you are no longer in the same session that created the key
BACKUP MASTER KEY TO FILE=‘C:\Temp\mymusicmasterkey’ — will not overwrite an existing file
ENCRYPTION BY PASSWORD=‘masterkeyfilepw’ — necessary
GO
Now we are ready to create the certificate.
Use Master
GO
CREATE CERTIFICATE mymusicencryptioncertificate
WITH SUBJECT = ‘Test Certificate for MyMusic Example’;
GO
— will not overwrite existing file – you must delete an existing file first
BACKUP CERTIFICATE mymusicencryptioncertificate TO FILE=‘c:\Temp\mymusicencryptioncertificate.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\Temp\mymusicencryptioncertificate.key’
, ENCRYPTION BY PASSWORD = ‘privatekeypw’
);
GO
If we have been successful, the new certificate can be viewed in the SSMS Object Explorer.
Now that the pieces are in place, creating an encrypted backup is easy.
BACKUP DATABASE MyMusic
TO DISK = N’C:\Temp\MyMusicEncrypted.bak’
WITH
COMPRESSION,
ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = mymusicencryptioncertificate
),
STATS = 10
GO
This is essentially the standard database backup T-SQL with a new option “ENCRYPTION”. This particular example uses the AES_256 encryption algorithm. If we were regularly encrypting sensitive data to add to our database, the choice of encryption algorithm would be critical. It’s still important here, but since performance is not a primary concern here, we won’t concern ourselves with the details of the various options available.
Restoring a database to the same server is straightforward, since the master database key and the certificate are already in place. If we wish to restore a database to a different server, or perhaps a rebuilt server, we must first make sure the new destination has the same certificate we used to make the backup. This means that the master database on the destination server must have a master key. There is no relation between the master key in the new destination and the original source; a master key simply has to exist. If the destination master database has a master key we are done. If it does not, we must create one.
To create a certificate, the master database master key must exist and be opened in the session that will create the certificate. For the restore, however, we will not be creating a new certificate from scratch, we will be creating a certificate from the file we saved. You do remember the password, don’t you?
CREATE CERTIFICATE mymusicencryptioncertificate
FROM FILE = ‘C:\Temp\mymusicencryptioncertificate.cer’
WITH PRIVATE KEY ( FILE = ‘C:\Temp\mymusicencryptioncertificate.key’ ,
DECRYPTION BY PASSWORD = ‘privatekeypw’ );
Now that we have the correct certificate on the destination machine, we are ready to restore.
RESTORE DATABASE MyMusic
FROM DISK = ‘C:\Temp\MyMusicEncrypted.bak’
CLOSE MASTER KEY
Creating an encrypted backup in SQL Server 2014 is valuable, and actually pretty easy. It does, however, place administrative demands by generating additional security objects which are critical and which must be carefully protected.
For more, have a look at Learning Tree’s new 3-day course – SQL Server 2014 for Performance Enhancements.
