Backup Encryption in SQL Server 2014

While backups are the backbone for any database administrator, there hasn’t been much improvement in this space for a long time.

Now, a new feature in SQL Server 2014 called Encrypted Backups, lets you choose to encrypt the backup file during a backup operation. You must use either a certificate or an asymmetric key to perform encryption during backup. This feature works seamlessly with other capabilities, too. Encrypted backups can also be used for databases that are encrypted using TDE. Encrypted backups are also supported for backups done by SQL Server Managed Backup to Windows Azure too.

These are some of the salient points working with Encrypted backups:

--Encryption Options

 ENCRYPTION (ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } , encryptor_options )

<encryptor_options> ::=

   SERVER CERTIFICATE = Encryptor_Name | SERVER ASYMMETRIC KEY = Encryptor_Name

Eligible Asymmetric Keys and Certificates

Only asymmetric keys and certificates are eligible encryptors for Encrypted Backups.  Asymmetric keys must be created from an Extensible Key Management (EKM) provider to be eligible.  All certificates are eligible provided that they don’t begin or end with ‘##’ (which is only used for the built-in certificates that are already there when SQL Server is installed) and they aren’t expired.  To know which keys can be used with Encrypted Backups, you can query the DMV’s as below:

<ASYMMETRIC KEYS>

SELECT a.name FROM sys.asymmetric_keys AS a

LEFT OUTER JOIN sys.cryptographic_providers AS cp

ON cp.guid = a.cryptographic_provider_guid

WHERE cp.name IS NOT NULL;



<CERTIFICATES>

SELECT c.name FROM sys.certificates AS c

WHERE c.name NOT LIKE '##%'

AND c.name NOT LIKE '%##'

AND c.expiry_date !< GETDATE();

Recommended Practices for Certificates

It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. This is a critical step while working with encryption and SQL Server because without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable. There are no shortcuts or hacks to get a backup that was encrypted and we don’t have the key. For disaster recovery purposes, you should store a backup of the certificates to an off-site location and secure it too.

A typical error administrators will encounter when the certificate used for backup is not present on the server where the backups are being restored:

Msg 33111, Level 16, State 3, Line 12

Cannot find server certificate with thumbprint '0xACB4A345A8F8828A68A4DB4D1F603497339AE35C'.

Msg 3013, Level 16, State 1, Line 12

RESTORE DATABASE is terminating abnormally.

Permissions

To encrypt a backup or to restore from an encrypted backup: VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database backup. Be careful on this permission because you don’t want to expose the certificate access to all.

Encryption Hierarchy

Encrypted backups are fully integrated into the overall Encryption Hierarchy utilized by SQL Server.  SQL Server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. Asymmetric keys and symmetric keys can be stored outside of SQL Server in an Extensible Key Management (EKM) module.

Conclusion

In this article, we took a simple view of what is available with SQL Server Encrypted backups and how it can be used inside SQL Server 2014. These are the basic building blocks when working with this new feature and I highly recommend you doing extensive testing especially when working with Keys and Certificates for backup before building a process to use the same. As we wrap up this blog, would love to know if you have implemented the same in your environment and if you have used any of these capabilities in production today.