New features of native backup encryption for SQL Server 2014

SQL Server 2014 CTP2 publishes a feature for backup, which is native backup encryption. In view of the previously bad impact on the network database leakage events, is essentially a database backup leaked to the third party, SQL Server's original data backup can make even if the backup itself is stolen, without the encryption certificate can not be used, this effectively resolves the above data leakage problem.

The original solution

Before SQL Server 2014, if you want to implement encryption of the backup, it is implemented in one of the following two ways:

Encrypt the entire database with transparent data encryption (TDE) so that the backup and transaction logs are also encrypted

Using a Third-party Backup encryption tool

First, let's talk about using transparent data encryption, if only to encrypt the backup, using TDE a bit too much, because using TDE can cause the database itself and the log is encrypted, the CPU overhead, in addition, the use of TDE encrypted database if the use of backup compression, compression ratio will be very low. If you use SQL Server 2014 's native backup encryption, you do not need to encrypt the entire database, and the backup is encrypted only before it is written to disk. Original sound backup encryption if combined with backup compression, then compression, and then encryption, resulting in a very high compression ratio.

Second, the use of Third-party Backup encryption tool requires additional costs, and in the implementation process will also have a relatively cumbersome operating costs, the use of Third-party backup when it is necessary to encrypt backup to Azure, it will be very cumbersome, and the use of raw backup encryption, you can directly backup to Azure backup to encrypt, This provides additional security for off-site backups.

Encrypting using native data

This feature is included in SQL Server 2014 Enterprise, Bi, and Standard editions. There are two prerequisites to using Backup encryption:

Certificate or asymmetric key exists

Select Backup Encryption algorithm

The following uses the acoustic data encryption to encrypt the backup, first establishing the certificate:

Createcertificate backupcertificate
    
withsubject = ' Backup encryption certificate ';
    
Go

After the build is complete, use the certificate for backup encryption directly:

BACKUP databasetestbk todisk = ' C:\Test_Encrypted.bak ' withcompression, encryption  (algorithm = aes_256, SERVER certificate = backupcertificate);

As shown in Figure 1, in Figure 1, note that SQL Server gives the hint to back up the certificate and the certificate's private key, otherwise the data will not be available. Usually in best practice, when a certificate is created, it should be backed up, and data security will always be the first.

Figure 1. Prompt to back up certificate private key

In Figure 1, note that the encryption algorithm specified by the backup is the aes_256 algorithm. SQL Server 2014 provides 4 algorithms for backup encryption, namely the AES128, AES196, AES256, and Triple DES algorithms.

The Management Studio in SQL Server 2014 also provides GUI support for acoustic backup encryption, as shown in Figure 2.

Figure 2. GUI support for native backup encryption

The effect of primary encrypted backup on compression

Previously mentioned, the use of raw backup encryption can make backup compression is almost not affected by encryption, the following backup a test library around 2.5G, specify a compressed backup, the first example using only compressed backup, 第2-4 example using compressed backup + different compression algorithm, the test statement as shown in Figure 3.

Figure 3. Test statement