Transparent Data Encryption with SQL

Introduction

Transparent Data Encryption can be configured for SigningHub, so that the data will be encrypted at rest for the 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, the following are the high-level steps that need to be done.

  • Create a 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 a certificate protected by the 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 the same certificate has been installed.

  • Take a backup of the 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 the 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 the command is:

CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

Create database encryption key

Now, we must utilise 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 the 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 the 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.

  1. 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 back up 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 the 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')

  1. 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

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 it 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. Something to note before applying TDE is its drawbacks, which include that 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

To verify if the TDE has been implemented on a specific database, use the following queries:

  1. The following query will return the database list and ‘encryption_state =3 shows the database is TDE-enabled. Select * from sys.databases

  2. The 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. The following query will return the database's 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 processes like CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE...SET ENCRYPTION The 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 initialisation 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.

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

PreviousDatabase securityNextTransport Layer Security with SQL

Last updated

Was this helpful?