Introduction

Transparent Data Encryption can be configured for SigningHub, so that the data will be encrypted at rest for SigningHub database, which means the data and log files are protected from any potential threat of hacking or misusing of data.

Transparent Data Encryption (TDE) encrypts SQL Server data files, which are commonly known as encrypting data at rest. The user can take several precautions to help secure the database such as designing a secure system, encrypting confidential assets, and building a firewall around the database servers. However, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data, but this kind of protection must be planned.

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.


It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.


How It Works?

To configure TDE, following are the high level steps that need to done.

  • Create master key. The Server Master Key is created at the time of the initial SQL Server instance setup. The Service Master Key encrypts the database Master Key for the master database.
  • Create certificate protected by master key. The database master key creates a certificate in the master database. Keep in mind that you must create a backup of this certificate. Not only for environmental refreshes but disaster recovery purposes.
  • Enable database encryption. Once Transparent Data Encryption is enabled on the database, you won’t be able to restore or move it to another server unless this same certificate has been installed.
  • Take a  backup of certificate. Keep good (and secure records) of the certificate and password.
  • Verify the TLS configurations by executing queries on SQL Server, which are mentioned in upcoming sections.

Create Master Key

We must first create the master key. It must be created in the master database, so as a precautionary measure, begin this statement with the USE MASTER command. The syntax of the command is:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD=’ProvideStringPassword’;
GO


Create Certificate Protected by Master Key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate. The syntax of command is:

CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO


Create Database Encryption Key

Now, we must utilize our USE command to switch to the database that we wish to encrypt. So, that we can create a connection or association between the certificate that we just created and the actual database. Then, we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption. The syntax of command is:

USE TDE_TestDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO


Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command. The syntax of command is:

ALTER DATABASE TDE_TestDB
SET ENCRYPTION ON;
GO 


Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys


Backup Certificate

It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers. The syntax of command is:

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\temp\TDE_Cert'
WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='ProvideStrongPasswordHere')


Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.


Restoring Certificate

In order to restore the certificate, you will once again have to create a service master key on the secondary server.

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='ProvideStrongPasswordHere';
GO


Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.


USE MASTER
GO
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\Temp\TDE_Cert'
WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );



  1. Make sure while restoring the database, the same certificate and private key must be used that were saved while enabling TDE on Primary server. Password for decryption must be same that was provided at the time of enabling TDE.
  2. Something to note before applying TDE are its drawbacks which Transparent Data Encryption encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption.


Verification of TDE under SQL Server

In order to verify if the TDE has been implemented on a specific database, use following queries:
  1. Following query will return the databases list and ‘encryption_state =3 shows the database is actually TDE enabled.
    Select * from sys.databases
  2. Following query will return the list of certificates and the one that was created above can be looked in the table.
    Select * from sys.certificates
  3. Following query will return the databases encryption keys details.
    Select * from sys.dm_database_encryption_keys


Limitations and Restrictions on TDE

The following operations are not allowed during initial database encryption, key change, or database decryption:

  • Dropping a file from a filegroup in the database
  • Dropping the database
  • Taking the database offline
  • Detaching a database
  • Transitioning a database or filegroup into a READ ONLY state


The operations which are not allowed while performing the processes like CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements are:

  • Dropping a file from a filegroup in the database
  • Dropping the database
  • Taking the database offline
  • Detaching a database
  • Transitioning a database or filegroup into a READ ONLY state
  • Using an ALTER DATABASE command
  • Starting a database or database file backup
  • Starting a database or database file restore
  • Creating a snapshot


The following operations or conditions will prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION statements:

  • The database is read-only or has any read-only file groups.
  • An ALTER DATABASE command is executing.
  • Any data backup is running.
  • The database is in an offline or restore condition.
  • A snapshot is in progress.
  • Database maintenance tasks.
  • When creating database files, instant file initialization is not available when TDE is enabled.


In order to encrypt the database encryption key with an asymmetric key, the asymmetric key must reside on an extensible key management provider.

All these configurations are verified on SQL Server 2019 Standard Edition for now. TDE is only supported to specific SQL Server versions



See Also