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:
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.
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:
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:
Next, execute the following BACKUP (Transact-SQL) Transact-SQL statement WITH ENCRYPTION option, to create an encrypted backup file of the AdventureWork2014 sample database:
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).
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.
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