Database Backup Encryption
SQL Server Technical Article
Summary: To manage the security of data which has been backed up to the file system in form of database backup files by using SQL Server 2014 backup encryption feature this document provides information on encryption options for SQL Server database backups. Also includes details of the usage, benefits, and recommended practices for encrypting SQL Server database backup during the backup process.
Writer: Sandeep Kumar (sandeep.kumar@microsoft.com), Microsoft
Reviewers: Mandi Ohlinger (mandi.ohlinger@microsoft.com), Microsoft, Lori Clark (lori.clark@microsoft.com), Microsoft
Published: July 2014
Applies to: Microsoft SQL Server 2014 CTP2, RTM
Copyright
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, can change without notice. You bear the risk of using it.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You can copy and use this document for your internal reference purposes.
© 2014 Microsoft. All rights reserved.
SQL Server 2014 Database Backup Encryption 3
Overview – What is database backup encryption? 3
What is required? 4
Benefits of database backup encryption 4
Changes in system tables 4
msdb.dbo.backupset 4
msdb.dbo.backupmediaset 5
Prerequisites: Database backup encryption 5
Master key 5
SQL Server certificate 6
Considerations 7
Permissions 8
Database backup encryption methods 8
SQL Server Management Studio 8
SQL Server Management Studio Maintenance Plan Wizard 9
SQL Transact-SQL statements 14
Using PowerShell 15
Database backup restores 16
Retrieving backup, encryption and certificate information 16
Create the database master key 17
Restoring a certificate from an existing certificate backup 17
Recommended practices 17
Backup encryption impact analysis on system performance 18
System performance analysis 18
The Implementation 27
Steps to be performed on source SQL Server Instance 27
Steps to be performed on destination SQL Server Instance 28
Single certificate approach across the environment 29
Appendix: Reference links 30
SQL Server 2014 Database Backup Encryption
Overview – What is database backup encryption?
With the growing need to keep data secure, be sure to secure your database backup files, especially those on a server file system. With native SQL Server backups, the data in the backup file is stored in plain text on the file system. It is easily read using a text editor. Depending on the data types used in your tables, some data is much easier to view than other data. In the following images, the backup files are opened in Notepad. You can see which database backup file is encrypted:
SQL Server native backup without encryption:
SQL Server native backup with backup encryption:
Starting SQL Server 2014, SQL Server can encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a Certificate or Asymmetric Key) when creating a backup, you can create an encrypted backup file. All storage destinations (on-premises and Microsoft Azure Storage) are supported.
SQL Server backup encryption improves security and works in any context where SQL Server can be used: on-premises, in a Microsoft Azure VM, or in a Hyper-V environment.
What is required?
To encrypt the database backup during backup operation, specify an encryption algorithm and an encryptor to secure the encryption key. Supported encryption options include:
§ Encryption algorithm: AES_128, AES_192, AES_256, and Triple_DES_3Key
§ Encryptor: A certificate or asymmetric key
Caution
It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable.
Benefits of database backup encryption
§ Encrypting the database backup helps secure the data. SQL Server provides the option to encrypt the backup data while creating a backup.
§ Database backup encryption can also be used for the databases that are encrypted using TDE (Transparent Data Encryption).
§ Encryption is also supported for backups done by SQL Server managed backup to Microsoft Azure which provides additional security for off-site backups.
§ Supports multiple encryption algorithms up to AES 256 bit. This gives you the option to select an algorithm that aligns with your requirements.
§ You can integrate encryption keys with Extended Key Management (EKM) providers.
Changes in system tables
In SQL Server 2014, there are changes to system tables that provide information about the key algorithm, encryption type, and encryption thumbprint used while the database backup is encrypted.
msdb.dbo.backupset
This table in msdb system database contains a row for each backup set. A backup set contains the backup for a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device(s). key_algorithm, encryptor_thumprint, encryptor_type columns of this DMV provide information on whether the backup is encrypted, what encryptor type is used, and the encryptor thumbprint.
SELECT TOP 5
name,
key_algorithm,
encryptor_thumbprint,
encryptor_type,
is_password_protected
FROM msdb.dbo.backupset AS backupset with (NOLOCK)
WHERE type IN ('D', 'I') AND database_name = 'MyTestDb'
ORDER BY backupset.backup_start_date DESC
GO
msdb.dbo.backupmediaset
This table in msdb system database contains one row for each backup media set. The is_encrypted column indicates whether the backup is encrypted or not. 0 indicates not-encrypted and 1 means encrypted. Initially this value is set to NULL which indicates non-encrypted backupmediaset.
SELECT TOP 2
media_set_id,
is_encrypted,
is_password_protected,
is_compressed, *
FROM msdb.dbo.backupmediaset AS mediaset with (NOLOCK)
ORDER BY mediaset.media_set_id DESC
...
nieznajomy135