Since SQL Server 2014, database administrators can enable Backup Encryption to protect their sensitive data. In this article I will introduce you how to meet the prerequisites need and detailed steps to achieve it.
As database administrators, securing our data is one of our most important tasks. We certainly don't want someone to view our database at will without authorization, simply by restoring the backups to their own hardware, especially when we have sensitive data stored in there.
Therefore, in order not to leak the data, it’s almost a necessity to perform SQL Server Backup Encryption. I strongly suggest you to perform SQL Server Backup Encryption under these circumstances:
Before SQL Server 2014, Transparent Data Encryption (TDE) was commonly used to encrypt data at rest, however, it will significantly reduce query performance and increase CPU consumption.
Luckily since SQL Server 2014, the new Backup Encryption option allows us to encrypt the backup while creating, only after we specified an encryption algorithm and an encryptor to secure the encryption key.
However, Backup Encryption is still not avaliable on SQL Server Express, SQL Server Web, and versions before SQL Server 2014. Therefore, if you are using SQL Server Express, Web, or you want to perform backup encryption in SQL Server 2012, 2008, etc, I suggest you choose the professional backup software which supports all versions.
Before we start, there are 2 things we need to know.
1. Encrypted backups cannot be appended to an existing backup set. Therefore we need to back up to a new backup set.
2. We need to specify an encryption algorithm and an encryptor to secure the encryption key first. Here are the suppoted encyption algorithm and encryptors. In general we choose AES 256 and certificate for SQL Server Backup Encryption.
To encrypt the SQL Server backup while creating, we need a SMK, a DMK and a certificate, and back up them first.
First let me explain you what are they and how SQL Server Backup Encryption works.
✦ Step 1. Check the Existence of DMK
Launch SSMS and connect to your instance. Click New Query on the upper bar and input the following statements.
SELECT * FROM master.sys.symmetric_keys
Click Execute. In results you can check the presence of DMK. If there’s no, then we need to create one.
✦ Step 2. Create A DMK
Input the following statements and click Execute.
-- Create the Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'
✦ Step 3. Create A Certificate
-- Create Backup Certificate USE master GO CREATE CERTIFICATE Certificate WITH SUBJECT = 'SQL Backup Certificate'; GO
✦ Step 4. Backup the SMK
-- Backup the Service Master Key USE master GO BACKUP SERVICE MASTER KEY TO FILE = 'filepath\SMKfilename.key' ENCRYPTION BY PASSWORD = 'SMKpassword'; GO
✦ Step 5. Backup the DMK
-- Backup the Database Master Key BACKUP MASTER KEY TO FILE = 'filepath \DMKfilename.key' ENCRYPTION BY PASSWORD = 'DMKpassword'; GO
✦ Step 6. Backup the Certificate
-- Backup the Certificate BACKUP CERTIFICATE Certificate TO FILE = 'filepath \CertificateFilename.cer' WITH PRIVATE KEY( FILE = 'filepath \CertificateKeyFilename.key', ENCRYPTION BY PASSWORD = 'CertificatePassword'); GO
Now we’ve created 4 files used for SQL Server Backup Encryption.
This part introduces how to enrypt SQL database backup. Basically, after you’ve created a DMK and a certificate, backed up them, you only need to do 2 more things, which is to select the Backup Encryption option, and Back up to another media set in the normal backup procedure. The rest steps are all the same.
✦ Way 1. SQL Server Backup Encryption using T-SQL
1. Launch SSMS and connect to your instance. Click New Query on the upper bar to input the following statements.
-- backup the database with encryption BACKUP DATABASE databasename TO DISK = 'filepath\filename.bak' WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = certificatename)
2. Then click Execute to create an encrypted backup.
✦ Way 2. SQL Server Backup Encryption with SSMS GUI
1. Launch SSMS and connect to your instance. Right-click the database name you want to back up and select Tasks > Back Up…
2. Select the Backup type and Destination in General page.
3. Then turn to Media Options page. Select Back up to a new media set, and erase all existing backup sets. Name the new set and write some description.
4. Turn to Backup Options page, check Encrypt backup, select an Encryption Algorithm and a Certificate or Asymmetric key.
5. Then click OK to start the backup with encryption.
✦ Way 3. SQL Server Backup Encryption using Maintenance Plan
1. Launch SSMS and connect to your instance. Expand Management menu and right-click Maintenance Plans, select New Maintenance Plan…
2. Click Toolbox next to Object Explorer, double-click Back Up Database Task to create a new backup task.
3. Double-click the task in the right blank to Edit it.
4. Select Backup type and single or multiple Databases in General page, and select a Destination.
5. Check Backup encryption in Options page, and select the Algorithm and Certificate or Asymmetric Key.
6. Then click OK to save the changes. Now you can save the plan and run it in SQL Server Agent jobs.
While SQL Server Backup Encryption is convenient, it is are not available in SQL Server Express and versions before 2014. Therefore, you may need a third-party software that supports all versions to do it for you. I recommend you to apply AOMEI Centralized Backupper Database . Here are the reasons:
Here's a 30-day free trial for you:
To properly use Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.
For those computers which download client program manually, you need to request control over them for further operations.
Click Computers > Uncontrolled Computers, select single or multiple client computers and click Request Control on the upper bar.
A permission request window will pop up on their computers, once all permissions are received, you can manage them at any time.
1.Click Tasks > New Task and select SQL Server Backup to create a new backup task.
In pop-up window, you can change the task name by moving your cursor to the task name and click it.
Then follow the 3 steps beneath the task name.
2. Step 1, click Add Computers to add the controlled client computer you want to back up with.
3. Step 2, click Add to select single or multiple SQL instances and databases you want to back up.
4. Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.
5. Then click Settings. Check Enable encryption for backups in General page, Type and Confirm Password.
You can enable email notification and select a Compression level in Settings.
Click Schedule next to Settings, you can also specify days and time to run this task automatically.
Click Advanced, you can choose to perform full backup or SQL database differential backup.
6. Now you can click Start Backup to select Only create the task, or Create and execute the task.
Once created, the tasks will be listed in Tasks separately for further edit or restore. When restoring, it will list all the backups, and distinguish them by the backup type and dates in their names.
You can choose to restore the backups to the local server, or restore SQL database to another server within LAN.
Launch SSMS and connect to your instance. Click New Query on the upper bar and input the following statements.
RESTORE HEADERONLY FROM DISK = N'filepath\Filename.bak'; GO
Click Execute, and check the values in EncryptorThumbprint and EncryptorType columns. If it is not encrypted, they will be NULL.
Backup Encryption is a useful feature to avoid data leakage. In this article I analyzed when should you perform SQL Server Backup Encryption, and introduced 3 ways to implement it.
However, it is only started from SQL Server 2014, and is not available on Express version. So, if you are using Express or versions before 2014, I suggest you to choose AOMEI Centralized Backupper Database Edition to perform backup encryption in SQL Server. It can save you a lot of effort.
Except for SQL Server Backup, this software centralized many other practical features such like File, System, Disk, and Partition Backup. All of them can be encrypted effortlessly.