DataGinger.com

With the release of Microsoft SQL Server 2014, we have the first version of SQL Server that supports encrypting database backups directly from the database engine without any third party software being installed on the SQL Server.

Preparing the Instance for Encrypted Backups

Before you can have the SQL Server database engine encrypt your backups, you have some basic setup which needs to be done. The first thing to check is that the master database has a master key in the database. Odds are that there is already a master key within the master database, as SQL Server will put one there by default when SQL Server is installed. Whether a master key has been installed can be verified by querying the master.sys.symmetric_keys catalog view and looking for a key named ##MS_DatabaseMasterKey##. If the row exists, then there is nothing else which needs to be done. If the row does not exist, then create a master key within the master database by using the CREATE MASTER KEY command as shown in Listing 1.

CREATE MASTER KEY ENCRYPTION 
BY PASSWORD='MyPa$$w0rd'

The nest preparation item which needs to be done is that a certificate or an asymmetric key must be created within the master database. If an asymmetric key is going to be used (not shown in this article), then the asymmetric key must be protected through an EKM (Extended Key Management).

If there is already a certificate within the master database, this certificate can be used, provided that it has not expired. You can see the list of certificates which exist within the master database by querying the sys.certificates catalog view, with the expiration date shown in the expiry_date column.

If there is no certificate which is usable, then a certificate can be created using the CREATE CERTIFICATE command as shown in Listing 2.

CREATE CERTIFICATE MyBackupCert 
WITH SUBJECT='Backup Encryption Certificate'

If using a certificate, the certificate must be backed up and the backup placed in a safe location. This backup will be needed if the server needs to be rebuild and the backup restored, or if you wish to restore the database to another server. Backing up the certificate is done via the BACKUP CERTIFICATE statement as shown in Listing 3.

BACKUP CERTIFICATE MyBackupCert 
TO FILE='c:\backup\MyBackupCert.crt'

Backing up the database using SQL Server Management Studio (SSMS) setup requirements, are the same as when using T-SQL. Using encryption with the GUI requires that you set the database to backup to a new media set on the “Media Options” page as shown in Figure 1.

The encryption options are available on the “Backup Options” page of the database backup screen. You can enable the backup encryption by checking the “Encrypt backup” check box as shown in Figure 2. You can then set the Algorithm and the certificate or asymmetric key that will be used from the two dropdowns which are shown in Figure 2.

Backing up the Database

Once the certificate or asymmetric key has been created, the database can be backed up using the certificate or asymmetric key, to secure the backup. When backing up the database you can select from four different encryption keys. These are shown below, in order of strength:

• DES
• AES 128
• AES 192
• AES 256

The stronger the encryption that is used, the more CPU power required to encrypt the data, and the more CPU power required for someone to attempt to break the encryption. With the CPU power available in modern SQL Servers and the amount of CPU power available to people who may attempt to break the encryption, selecting AES 256 is strongly encouraged.

When using backup encryption with SQL Server backups, each backup must be written to a new media set. Essentially, this means that the backup must be written to a new backup file each time a backup is taken.

Encrypting a database when it is being backed up is very similar to a traditional SQL Server backup. The normal BACKUP DATABASE (or BACKUP LOG) command is used, simply supplying the WITH ENCRYPTION parameter as shown in Listing 4.

BACKUP DATABASE [AdventureWorks2008R2] 
TO DISK = N'C:\Backup\AdventureWorks2008R2.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks2008R2-Full DB Backup',
ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [NewCert])

Restoring an Encrypted Database

Restoring an encrypted database is just as simple as restoring an unencrypted database. The same RESTORE DATABASE command is used, as normal. The only requirement is that the certificate, or asymmetric key, that was used to encrypt the backup, must exist on the instance before attempting to restore the database. If the certificate or asymmetric key does not exist on the server, then the database will not be restored. If the certificate or asymmetric key has been lost, then there is no way to restore the database from its backup.

If the certificate or asymmetric key is not already on the server, you’ll not be able to use the RESTORE FILELISTONLY or RESTORE HEADERONLY commands to view the metadata about the backup, either, as the entire backup file is encrypted.

Restoring the database from an expired certificate is done just like normal, provided that the certificate already exists on the server. If the certificate has expired, but does not exist on the SQL Server instance, the certificate can still be created from the backup file. The creation of the certificate from the backup file where the certificate is expired returns a warning, but it will import correctly.

Instance Limitations

Database backup encryption is a Standard Edition feature, meaning that it’s available on the Standard and Enterprise editions of Microsoft SQL Server 2014. While you can only backup with Standard or Enterprise Edition, an encrypted backup can be restored to any edition of Microsoft SQL Server 2014, including Express and Web editions. The only limitation is that the database must be under 10 GB in size in order to be restored to an Express Edition instance.

Summary

When used properly, database backup encryption is a great way to protect database backups from being viewed by unauthorized people. SQL Server database backup encryption is easy to setup and simple to use requiring only a master key within the master database and either a certificate or asymmetric key. However, if the certificate is lost, then the database backup becomes useless to you as you’ll have no way to restore the database from the database backup file.

Read Full Post »