Introduction to Backup Encryption In SQL Server

Daniel Jones | December 5th, 2016 | SQL Backup & Restore |

One of the most essential need of every Database Administrator is a backup file. This file acts as a life savior when any disaster occurs either on machine or on the server. When talking about SQL server, such backup files are very much essential in order to have a duplicate copy of the server data.

SQL Server 2014 and 2016 is capable of encrypting data while creating backup. A user just needs to go through some legal formalities for creating an encrypted server backup file. The server users will have to select an encryption algorithm and an encryptor for performing SQL server backup encryption. The server storage locations like on-premises and Windows Azure storage both are supported. Moreover, algorithms and encryptors used in the server are as follow:

  • Encryption Algorithms: The algorithms, which are supported by SQL server 2014 for encrypting backup files are: AES 128, AES 192, AES 256, and Triple DES.
  • Encryptors: An encryptor can either be a certificate or an asymmetric key.

Note: While restoring the encrypted backup files, users not need to mention any algorithm used for encrypted backup file creation. However, they have to mention a certificate or an asymmetric key for decryption of encrypted SQL server backup file. This key or certificate will be used as a means to authorize the person who is restoring data from backup file.

Need For SQL Server Backup Encryption

The backup encryption in SQL server is needed due to following reasons:

  • Way to Keep Database File Secure: Users need to encrypt SQL server database backup files because this procedure provides complete security to copy of SQL server data. This security measure will keep transaction logs, tables, and other server data safe from any person, who wants to make use of these data in wrong manner.
  • Accessed Only By Authorized Person: It is impossible to restore an encrypted backup file, if a person is not having certificate or asymmetric key for decryption. Therefore, it means that only authorized persons who are knowing credentials of encrypted backup file can restore data with its full access.

Prerequisites to Encrypt SQL Server Backup Files

There are few requirements, which need to be fulfilled before performing SQL server backup encryption:

  • Generate One Database Master Key: Before starting up with encryption procedure, users need to create a symmetric key for protecting private certificates and asymmetric keys, which are already present in database.
  • Develop A Encryptor For Encryption: Another thing which users need to generate is either a certificate or an asymmetric key, which will used as an encryptor during encryption procedure.
  • Enable a Permission On Any Encryptor: A permission named as VIEW DEFINITION is to be enabled either on certificate or on asymmetric key, which will used while creating a secure database backup file.

Methods to Create Encrypted SQL Server Backup File

There are several techniques by which one can perform SQL server backup encryption. Users are going to find description of each technique in following points:

Approach #1: Backup Encryption Using SQL Management Studio

Users can discover a secured backup file of the SQL server when they are creating database backup file with help of any one of the two following procedures:

  1. Go to Backup Options page, select Encryption box and then mention desired encryption algorithm. Now, end up by selecting any one encryptors from current window.
  2. With help of Maintenance Plan Wizard, you can select a backup task, then go to Define Backup () Task >> Options >> Backup Encryption, and then define the algorithm to be used along with certificate/key to be used.

Approach #2: Create Backup Using Transact-SQL Statements

Execute following set of Transact-SQL commands for SQL server backup encryption:

backup-encryption-t-sql

Approach #3: SQL Server Backup Encryption Using PowerShell

Launch PowerShell window on your machine and then use Backup-SqlDatabase cmdlet. Execute following two commands to get a secured database SQL server file:

backup-encryption-powershell-1
backup-encryption-powershell-2

Time to Conclude

It is recommended to the server users that they should locate encrypted SQL server files on some other computer storage location. This location can be external storage, internal storage, or both. However, users cannot open or restore an encrypted backup file without certificate used while encryption therefore, it is advised to the users that they should remember security credentials while performing SQL Server Backup Encryption.

Transaction Log Restore