Saturday, January 20, 2018

SQL Server - Encrypting Database Backups

By Saleem Hakani on 9/21/2014

Imagine this: You have sensitive information in your database (ex: SSN or Employee Payroll Information). You perform weekly full database backups, daily diffs and hourly T-log backups. You ship out one copy of the database backup to an offsite location. At this point the backup copy of the database is not in your control as it has already left your office. It is very possible for someone to restore the copy of the database since Microsoft supports restoring the database on any SQL Server with the right version in which the backup was performed. However, you would still feel uncomfortable knowing that your database has sensitive information and that it is possible for someone to get their hands on to the backup copy of the database. What would you do?

SQL Server 2014 Now Supports Encrypted Backups.

SQL Server 2014 introduces a powerful new native feature that allows you to encrypt database backups. It’s been a long time coming. Many customers have been asking for this feature for years. Though there were 3rd party tools out there that would support backup encryption but there was no native solution from Microsoft until SQL Server 2014.

What you CAN do?

What you CAN’T do?

  1. You can encrypt both TDE and non-TDE databases in SQL Server 2014
  1. You can encrypt backups performed on local disk, shared storage and even on Windows Azure.
  1. Supports multiple encryption algorithms that includes:
    1. AES 128
    2. AES 192
    3. AES 256
    4. Triple DES
  1. You can integrate encryption keys with EKM (Extended Key Management)
  1. SQL Server web and Express editions do not support backup encryption (Restore is supported)
  1. Previous versions of SQL Servers will not be able to read encrypted backups
  1. Backup Encryption is not supported when you are appending to an existing backup set / media. (It must be a new media / device)
  1. If the backup has been encrypted using asymmetric key then only asymmetric keys residing no EKM is supported.


Steps to create an encrypted backup in SQL Server 2014:

  1. Create a database master key of the Master system database

  2. Create a backup certificate in the Master system database

  3. Perform database backup with encryption

Please make a note of the below additions to the backup screen in SQL Server 2014 Management Studio:

This screen shows options to select for performing encrypted backups

This screen shows the options to select encryption algorithm for backup encryption


With the introduction of encrypted backups in SQL Server 2014, you can now encrypt not just the data within the database but also the backups of the database.

Backup Encryption
Data Security
Encrypted Backups
SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM