Prashanth Jayaram

Understanding Database Backup Encryption in SQL Server

May 3, 2017 by

Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.

While this is less of a challenge in an all-on-premises environment, database backups stored off-site or on the cloud require some more precaution. The data literally resides on someone else’s infrastructure. Data security has to be thought of from a different perspective now. If someone gets unauthorized access to the site, they could simply restore a copy of your database from a backup, onto their own hardware. What good, then, is it to lock the doors of your own equipment, when the soul has already departed?

Security best-practices are in place in order to secure the metaphorical soul. They need to be implemented not just in the production environment, but within our backup solution as well.

Earlier versions of SQL Server had a limitation on this security feature; we had to use third-party solutions to encrypt and compress the backup files. Microsoft, with SQL Server 2014, has begun introducing database backup encryption within the native backup capability—now, SQL Server has the ability to encrypt the data while creating a backup using various encryption algorithms!

In this article, I will discuss backup encryption application internals, how this feature is applied with the latest versions of SQL, the importance of security, how to recover/restore the certificate and the database in case of system/database failure, asymmetric key and EKM provider, and give step-by-step examples of the process of demonstrating the encryption and the recovery of a backup.

In this article:

  • Key challenges in securing data
  • What database encryption is, and why it’s critical in today’s data environment
  • The two classifications of database backup encryption
  • The benefits of implementing a database backup off-site or on the cloud
  • The impact of database backup and restore/recovery

Prerequisites

  • SQL Server 2014+ Enterprise or Standard Edition
  • Write access to a local/remote file system
  • Storage with adequate space to create a backup of the database

Benefits

  1. Encryption for native SQL Managed Backup
  2. Security and integrity of the backup
  3. It can also be used for databases that are encrypted using TDE
  4. Additional security for off-site backups
  5. Use of various encryption algorithms, which provides you with flexibility in selecting an algorithm that aligns with your requirements
  6. Use of asymmetric key to manage and integrate security with EKM providers

Feature Support – SQL [2014-2016]

Feature Enterprise Standard Web Express with Advanced Services Express
Encrypted backup Yes Yes No No No

Database Backup with Encryption

Almost every organization has a challenge of protecting the data. It is almost impossible to run a business without protecting the sensitive data. This feature comes handy where backups are managed and handled at a remote site or cloud. In order to encrypt the backup, we will need a certificate. And, in order to create a certificate, we will need a master key for the database.

Demonstration

Let’s see how we can create a secure backup for off-site storage. We’ll do that by creating a new database. Let’s call it “SQLShack”. I’m going to switch into the SQLShack database to create a sample table to hold dummy data which are needed to demonstrate the Tail Log backup process. A stored procedure is created to generate the sample data. On execution of the stored procedure, the data will be fed to the table.

Generate Master Key

Now, I’ll change the current context of the database to Master. Next, we’re going to create a master key and the certificate on our server. 

Create Certificate

The certificate “SQLShackDBCert” will be created with a subject line, which’ll just appear in the metadata for the certificate, of SQLShackDBCert Backup Certificate. I’ll execute the below line to create the certificate.

Now, since we’re working in the master database, I can find that certificate created in the System database. Browsing the Object Explorer, expanding master, going down to Security, and then opening up the Certificates folder tells that the certificate is created. 

In order protect the data from failure event; make sure that back-up the certificate to an external file. Let’s backup the certificate using the BACKUP CERTIFICATE command, the name of the certificate is SQLShackDBCert, and we’ll export it to a file to remote secured location

The name of the certificate file is “f:\Program Files\SQLShackDBCert.cert”. We’d also want to export the private key file, “f:\Program Files\SQLShackDBCert.key” which will encrypt the certificate file. Finally, we’ll protect the entire thing with a strong password.  Let’s create the backup of the certificate at a secured offsite location

If you get an error message at this point stating that your password doesn’t meet Windows’ policy requirements, it simply means that your installation of Windows is configured to require stronger passwords. Make sure you enter a strong password.

Database Backup

Next, I’m going to back-up the SQLShack database with the encryption protocols enabled. The AES_256 encryption is a very strong and a recommended encryption algorithm. We’re going to encrypt this backup with the server certificate that we just created.

Database Recovery

This section discusses the demonstration of the Tail Log backup using database encryption. The data is inserted after a full backup of SQLShack. We’re going to drop the database SQLShack, and the certificate file, just to simulate movement to a new, clean instance of SQL Server.

Let’s demonstrate the recovery process.

  • Insert sample data
  • Simulate database corruption by detaching the database
  • Delete the file from the drive
  • Bring the database back online
  • Refresh the database
  • Initiate the Tail Log backup with database encryption

Check the backup meta-data

If you use encryption during the backup you wouldn’t be able to append the backup to an existing media set. The restoration just works like normal restoration steps, except ensuring the corresponding certificates are created and configured on the destination server.

We have four new files that have just been created. We have the backup certificate, the encryption key file, as well as the full backup and the tail log backup of our database. Let’s go back into SQL Server Management Studio and see how we can restore this backup.

After running the commands above, if I right-click on the Security folder and say Refresh, you’ll notice that it is no longer inside of our Certificates folder. And, if I go back up and refresh databases, you’ll notice that SQLShack is no longer available either. Now, let’s attempt to restore the database from the backup. I’ll specify that I want to restore the database, SQLShack, from the disk. When I execute the command, I get an error message, saying that it couldn’t find the server certificate required in order to restore the backed-up database.

Without configuring the certificate, any attempt to restore would result in the following error:

Recreate the master key and the certificates

In order to restore this encrypted database, we first need to restore the certificate. But this time, instead of creating it based off of the master key for the database, we’re going to restore it from the file. I’ll specify FROM FILE and the path to that file that we exported. We’ll also specify the private key file. Finally, we’ll enter DECRYPTION BY PASSWORD and we’ll re-specify the password that we established earlier when we created that key. After execution, if I go back into System Databases -> master, -> Security, and back to Certificates, we should see the SQLShackDBCert certificate back there.

Database Restoration

Now, go ahead and try the restore again, the RESTORE DATABASE SQLShack from the disk file. Execute the command, and voila, it processed successfully this time! We see that the database, SQLShack, came online

Database backup Using Asymmetric Key

In order to encrypt the database encryption key with an asymmetric key, please use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature that gives SQL Server the ability to store the encryption key used for TDE on hardware specifically designed for key security and management. Such devices are called High Security Modules (HSM), whose vendors are referred to as EKM providers. A good key management vendor should supply you with software libraries that easily add and implement it in SQL Server encryption.

SQL Server stores encryption keys separately from the database server on a secure key manager, in order to meet various compliance requirements. Encryption is supported for backups done by SQL Server Managed Backup, which provides additional security for off-site backups. For example, a database backup file placed on the cloud.

In asymmetric encryption, two different keys are used: A “public key” for encrypting and a “private key” for decrypting. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI) or Public-key Cryptography.

Summary

  • In an environment that relies on SQL-managed native backup methodology, this would be a great feature to secure the data. Given that the backup files are encrypted, we can be confident that they’ll be unusable even if they fall into the wrong hands, while still taking advantage of an off-site storage strategy.
  • While the process of recovery is no different from restoring the normal database backup file, there’s no “backdoor” to recover the database access if you lose all access to the keys. So keep the keys safe.
  • The restore operation validates the thumbprint of the certificate during the restore operation. Therefore, the certificate used to create the backup must be retained in its original state.
  • If restoring a database from an encrypted backup file is performed on the same SQL Server instance, the restore operation is performed as usual, since the keys and the certificate are already contained in the master database. They’d be opened automatically during the process of decryption
  • You won’t be able to append the backup files to the existing media set in case of encrypted backups; this is not the case with normal backup methodology.
  • Even after several attempts, I didn’t notice a significant difference in the usage of system resources during the process of encryption of database backup, when compared to the normal database backup operation. However, AES is a block cipher, and requires the input to be a multiple of the block size (16 bytes, a.k.a. 128 bits). This means that padding schemes are used. Most of the time, the padding is negligible, which may be a reason why it doesn’t increase the size of the backup.
  • Use a combination of hardware- and software-supporting AES with a 256-bit key to ensure that the encryption doesn’t impact your backup or recovery time objectives.

References


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
SQL backup and restore

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

580 Views