Database Backup Encryption in SQL Server 2014

 

This blog describes process of database backup with encryption. Database Backup Encryption is new feature that is available in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

This blog will show you how we can performed encrypted database backup by creating master key and certificate. Once backup is encrypted then you can not restore it on other SQL Server Instance. To restore encrypted backup on other instance of SQL Server, first of all you need to create master key and create certificate on instance of SQL Server where you want to restore encrypted backup.

 

Restriction for database backup with encryption

The following are restrictions that apply to the encryption options:

a. Backup encryption feature is available in all editions of SQL Server 2014 except Express and Web editions.

b. Restoring of encrypted backup is supported in all editions of SQL Server 2014.

c. Previous version of SQL Server cannot read encrypted backup.

d. Appending to an existing set option is not supported for encrypted backup.

 

Steps to perform database backup with encryption on source instance of SQL Server named “GGN-AM-19\S2K14” are as follows-

Step 1

First of all, connect to source instance of SQL Server instance named as “GGN-AM-19\S2K14” as shown below.

Step 2

Next we will create master key on master database at source instance of SQL Server using below query.

Use Master
GO
Create master key encryption by password='Password@123'

Once master key is created on master database then we can check this key by using below query –

select * from master.sys.symmetric_keys where name='##MS_DatabaseMasterKey##' 

Step 3

Next we will create certificate named as “TestDB_Backup_Certificate” which will be used for encrypting database backup file.

Use Master
GO
Create Certificate TestDB_Backup_Certificate
with subject='Backup Encryption in SQL 2014'

Step 4

Now perform backup of certificate created in previous steps using below query.

BACKUP CERTIFICATE TestDB_Backup_Certificate
TO FILE = 'D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.cer'
WITH PRIVATE KEY(FILE='D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.pvk'
ENCRYPTION BY PASSWORD='Password@123')

Step 5

Now perform database backup with encryption using algorithm AES_256 and certificate as shown below.

Backup database Test
To Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'
with encryption(ALGORITHM= AES_256,Server Certificate=TestDB_Backup_Certificate)

Step 6

Now we have three files as shown below.

 

Now we will restore encrypted database backup on destination instance of SQL Server.

Steps to perform restoration of encrypted database backup on destination instance of SQL Server named “GGN-AM-19\S2K14_A” are as follows-

Step 1

Now we will restore database backup on destination instance of SQL Server. Connect to instance named GGN-AM-19\S2K14_A.

Step 2

After connecting to Instance named as “GGN-AM-19\S2K14_A”, run below query check files in backup files.

Restore filelistonly From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'

But we will get error as mentioned below –

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0x4C1FF9AFD5DBB5C3B5B0C89D757632B0D9573B56'.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

Above error is coming because certificate is not exists on instance of SQL Server where we want to restore encrypted backup.

Step 3

Now first of all, we will create master key on master database on destination instance using below query.

 

Use Master
GO
Create master key encryption by password='Password@123'

Step 4

Next we will restore certificate from backup performed at Source instance (GGN-AM-19\S2K14).

 

Use Master
GO
Create CERTIFICATE TestDB_Backup_Certificate
From FILE = 'D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.cer'
WITH PRIVATE KEY(FILE='D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.pvk',
DECRYPTION BY PASSWORD='Password@123');

Step 5

Now we will try to read files inside encrypted backup of database on destination SQL Server using below query.

 

Restore filelistonly
From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'

Step 6

Now we can restore encrypted backup of database on destination instance of SQL Server.

 

Restore Database Test
From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'
with move 'Test' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.S2K14_A\MSSQL\DATA\Test.mdf',
move 'Test_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.S2K14_A\MSSQL\DATA\Test_log.ldf'