SQL Server 2014 Database Backup - Encryption

Overview

The database is one of the most important elements in an organization/company's information system. It is often a target for hackers.

Recently, in my previous organization, a group of persons hacked the online trading database ofa bank. YES, a bank.

Here, Microsoft SQL Server has developed a key feature introduced in SQL Server 2014, which is backup encryption; i..e., encryption of database backup with a particular algorithm and while restoring a particular backup restoring with a key.

As part of the daily routine activities of DBA, if a particular requirement comes from business users, the activity that DBA does takes backup from one server and restores in another server as shown in below diagrams.

So let’s see how encryption is done in SQL Server 2014. Here I will illustrate what you will find in SQL server 2014



Note: The diagrams are been drawn by me in Microsoft Visio.

Tip: Microsoft Visio is the best tool for presentation and representating data and it is recommend you buy the license version so you will be able to showcase your creativity in articles or in presentations.

1. SQL Server offers two ways of encryption

• Transparent Data Access (TDA): From SQL Server 2008, SQL provides TDA i.e. transparent data access it enables encryption to database backups and restricts to access storage. Here major drawback in TDA is huge database generally ifGB. Export back up is taken on storage and ten on to tape library. Here I am referring tape library are nothing but IBM storage. In export backup it is observe red in TDA as CPU Utilization is high.
  
  
• Backup Encryption: SQL Server introduced yet another feature in SQL Server 2014 which is backup encryption as it supports database backups directly from backup engines. Now here in backup encryption, encryption is only done while taking backups and restoring backups (decryption with and key).

2. How does SQL Server Encryption work

• As you can see in below diagram SQL Server has a hierarchical encryption infrastructure. Here the term Service Master Key automatically gets generated during SQL Server 2014 installation and it gets stored in master database and it will be unique for every SQL Instance.
  
  
• SQL Master Key depends on SQL Service Account and Windows Data Protection API i.e. it depends on this both credentials.
  
  
• The next architecture layer is Database Master Key. Here DMK resides in master database and it is encrypted with the help of Service Master Key.
  
  
• The next architecture layer is the Private Key which is supported or protected by Database Master Key or you can say asymmetric key.
  
  

3. SQL Server Backup Algorithms

SQL Server backup encryption provides algorithm for data encryption like AES 128, AES 192, AES 256 and DES algorithm respectively.

• Advanced Encryption Standard (AES): Advanced Encryption Standard is specification which is mainly used to encrypt electronic data. Advanced Encryption consists of three blocks ciphers AES 128, AES 192, AES 256 here each blocks encrypts data in 128 block cipher.
  
  
• Data Encryption Standard (DES): Data Encryption Standard is also known as Data encryption algorithm. DES is a block cipher that encrypts 64 bit blocks. DES is a symmetric algorithm meaning a same algorithm is used for encryption and decryption. In Order to improve security they had introduces Triple DES it generates 168 bit block data but again it’s not been proven ineffective against brute force attacks.

4. SQL Server Backup Encryption

Let’s start SQL Server Backup Encryption in SQL Server 2014 while installing SQL Server 2014 Service Master Key (SMK) and Database Master Key (DMK) is generated automatically while installing SQL Server. Now you can check by performing a query sys.symmetric_keys.