olly - Fotolia


Two ways to encrypt database backups with SQL Server

SQL Server 2014 provides two features that let database administrators easily encrypt database backups. An expert provides two demos that show you how.

During the past decade, customer and business data residing in databases has become the main target for hackers...

attempting high-tech theft. As a database administrator, it is our responsibility to ensure that sensitive data is not only secure within an organization but also when it travels outside the organization through networks or is taken to offsite storage for safekeeping on backup media.

Encrypting database backups is the only option for protecting sensitive data when it is transported on tape or disk for offsite storage. Luckily, like other mainstream commercial database systems, SQL Server 2014 provides two reliable approaches for encrypting database backups. By using these two SQL Server 2014 encryption features you can reduce the likelihood of unauthorized disclosure of confidential customer and business information, even if the SQL Server database backups have been stolen due to weak network security.

A look at transparent data encryption

Before the release of SQL Server 2014, transparent data encryption (TDE) was the only feature available in the SQL Server database engine for encrypting databases, database backups and database snapshots. When TDE is enabled for databases, the SQL Server database engine encrypts the data before it is written to disk and decrypts it before it is retrieved. This process is transparent to the user or application because it's performed at the SQL Server service layer. TDE is available only in SQL Server 2014 enterprise edition.

TDE encryption uses a database encryption key (DEK), which is an asymmetric key that is encrypted using the server certificate stored in the master database. The DEK is stored in the database boot record, which is why it is available during database recovery. The server certificate is encrypted using a database master key (DMK), which itself is encrypted using a server master key (SMK). Both the DMK and the SMK are asymmetric keys. The SMK is created automatically the first time you encrypt something on SQL Server, and is tied to the SQL Server service account. The SMK is also encrypted by the Windows data protection API.

An example using TDE to encrypt database backups

To enable transparent data encryption, you must create a DMK and a server certificate in the master database. To do this, run the following code:

Create a DMK and a server certificate in the master database

You should immediately back up the certificate and the DMK associated with the certificate. If the certificate becomes unavailable or if you want to restore or attach the database on another server, you must have the backups of both the certificate and the DMK. Otherwise, you won't be able to open the database.

The final step is to create the database encryption key and enable encryption on the database you want to secure. For example, run the code below against the AdventureWorks2014 sample database in SQL Server 2014 Management Studio (SSMS 2014) to enable the TDE feature in the AdventureWorks2014 sample database.

Create the database encryption key and enable TDE

Alternatively, you can use SSMS 2014 to enable transparent data encryption in a database. To do this, right-click the AdventureWorks2014 in Object Explorer, choose Properties, then choose Option and then change Encryption Enabled option to True.

Once the AdventureWorks2014 sample database is encrypted with TDE, perform the database backup and attempt to restore the database to another instance of SQL Server 2014. You will notice that the restore process will fail because the database backup file is encrypted:

The restore process fails due to encryption

To restore the TDE encrypted database on a different SQL Server instance, you first need to restore the database master key and then the self-signed certificate that is used to encrypt the database encryption key.

The SQL Server 2014 backup encryption feature

A new feature in SQL Server 2014 has the ability to encrypt data in the database while a backup is created. The pleasant surprise is that this feature for native database backups is available in SQL Server 2014 standard, enterprise and business intelligence editions. Moreover, encrypted backups performed using these editions can be restored to the Web and express editions of SQL Server 2014.

The DMK for the master database and certificate or asymmetric key is a prerequisite for encrypting a backup. To encrypt database backups, you must specify an encryption algorithm, and an encryptor (a certificate or asymmetric key) to secure the encryption key. The supported encryption algorithms are AES 128, AES 192, AES 256 and triple DES. Furthermore, asymmetric keys used to encrypt the database backups must reside in an extensible key management module.

An example using SQL Server 2014 backup encryption to encrypt database backups

To use SQL Server backup encryption, run the following code in SQL Server 2014 Management Studio (SSMS 2014) to create a DMK and server certificate in the master database:

Create a DMK and a server certificate in the master database

Next, execute the following BACKUP (Transact-SQL) Transact-SQL statement WITH ENCRYPTION option, to create an encrypted backup file of the AdventureWork2014 sample database:

Execute the BACKUP T-SQL statement with encryption

Use the BACKUP (Transact-SQL) Transact-SQL statement ENCRYPTION option to specify an encryption algorithm, and an encryptor (a certificate or asymmetric Key). For the full Transact-SQL statement syntax, see BACKUP (Transact-SQL).

Similarly, you can use SQL Server 2014 Management Studio (SSMS 2014) Back Up Database (Backup Options Page) or Maintenance Plan Wizard to encrypt a backup when creating the backup of a database.

The command to restore database from the encrypted database backup file is same as the one you would use when performing the unencrypted database restore. For more information, see RESTORE (Transact-SQL) full Transact-SQL statement syntax on MSDN books online.

Next Steps

Learn about SQL Server 2016's new security features

Read a step-by-step guide on how to protect your data with Transparent Data Encryption

Dig Deeper on SQL Server Security