Configuring SQL Server database encryption

The Verba system supports database encryption features provided by Microsoft SQL Server. More information on encryption technology: https://msdn.microsoft.com/en-us/library/bb510663.aspx

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. For more information on TDE, refer to http://msdn.microsoft.com/en-us/library/bb934049.aspx.

Important considerations:

Microsoft offers TDE as part of its Microsoft SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017. TDE is only supported on the Evaluation, Developer, Enterprise and Datacenter editions of Microsoft SQL Server.
Only the complete Verba databases can be encrypted, there is no option to encrypt a single database table.
Some performance overhead is involved in using TDE. The encryption and decryption process does require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload. SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

Configuring database encryption

The verba database has to be updated/altered in order to use the encryption feature. The following T-SQL script shows the required steps to enable database encryption. You need to adjust it according to your needs.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO
USE verba;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE verba
SET ENCRYPTION ON;
GO

There is no configuration required on the Verba side.

Creating a backup of the certificate and the private key

When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. The encrypting certificate should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, parts of the transaction log may still remain protected, and the certificate may be needed for some operations until the full backup of the database is performed. A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.

For more information on the T-SQL commands, please check https://msdn.microsoft.com/en-us/library/ms178578.aspx.

Microsoft offers TDE as part of its Microsoft SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017 and 2019.^[1] TDE was only supported on the Evaluation, Developer, Enterprise and Datacenter editions of Microsoft SQL Server, until it was also made available in the Standard edition for 2019

[1]