Setting Up Transparent Data Encryption Within Azure

One method of securing data uses transparent data encryption (TDE). For the sake of this post, we’ll be looking at transparent data encryption (TDE) for Azure assets such as a PaaS SQL database, Azure Synapse or an IaaS SQL Server full service on a VM. TDE encrypts data at rest, such as data that has been persisted to the database or file level (in the case of Synapse).

Terms:

• Transparent data encryption (TDE): data security technique that encrypts data at rest through securing the data and log files on disk.
• Data masking: data security technique that hides sensitive data through a variety of techniques, such as randomizing the actual data values, obfuscating a fraction of the data values, or other techniques.
• Row level data security (also called row level security): complex data security technique that acts like a row filter on data (ie: WHERE Country = 'Serbia').
• Always encrypted: a data security technique that occurs on a level higher than the data (application layer), as the database will not have the encryption keys. This means that if the database is compromised, it’s meaningless because even a high level database administrator wouldn’t have access to the data.

In this post, we’re looking at the first one — transparent data encryption. First, let’s look at the context of when we would want to use TDE.

When Would We Use TDE?

When would we use TDE? Some of the cases that I list below would make good use cases for using TDE and there are more than these three:

1. Complying with strict security requirements where we want to prevent an attacker from gaining access to data at rest without an encryption key.
2. Ensuring least privilege access within an organization by preventing lower level employees or employees that shouldn’t have access to the data.
3. Protecting intellectual property in cases where data may be involved in the intellectual property or where data may be used for feeding automation of intellectual property.

(As a quick note here since we’re looking at Microsoft technologies in this context — the master, msdb, model and tempdb databases are cannot be protected. This means that you should avoid using these databases if you are protecting data or intellectual property.)

Process

When you install a SQL Server, a service master key (SMkey) is created, which is encrypted by a windows API called Windows Operating System Level Data Protection. This service master key (SMkey) encrypts the database master key (DMkey) for the master database. From here, the database master key (DMkey) creates a certificate in the master database, which encrypts the database encryption key (DEK) in the user database. This database encryption key (DEK) secures the whole user database with TDE.

Let’s look at these steps in list form:

1. Install SQL Server (SMkey created).
2. Create master key in master database (SMkey). See the below example where noted.
3. Create a certificate (CertificateStep) that is protected by the SMkey.
4. Create a DEK that is protected by the certificate. This occurs on the user database — in our example OurDatabase .
5. Enable encryption on the database. This is the final step in the below example.

---- Steps 2 and 3:
USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SMKey_Step2';

CREATE CERTIFICATE CertificatesStep WITH SUBJECT = 'Certificate_Step3';

---- Steps 4 and 5:
USE OurDatabase

CREATE DATABASE ENCRYPTION KEY
---- In this case, we're using TRIPLE_DES_3KEY
WITH ALGORITHM = TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE CertificatesStep;

ALTER DATABASE OurDatabase SET ENCRYPTION ON

As a quick note here: for both Azure Synapse and Azure SQL, you can set TDE encryption on through the Azure Portal. When you click on an Azure SQL database, there is an option under settings called Data Encryption under the Security settings. For Azure Synapse, under the Security settings, you would select Encryption , though you may be limited with double-encryption depending on how you designed your initial workspace.

Performance

Enabling TDE will affect the performance of SQL Server, as TDE affects the log and tempdb (by default, tempdb will become encrypted if any database on a server has TDE). In general, you can expect to see an increase in resources being used for most DML operations (less than 10% in most cases). In the case of administrative operations, such as backups and indexing, you’ll also see an increase in resources being used. I would highly recommend testing in larger data environments before enabling TDE without first measuring the impact.

You may want to consider scaling ahead of time to prevent any performance impact. Additionally, you may also partition your data in a way that allows you to use TDE with one set of data where the impact will be smaller.

More details from Microsoft and recommended reading: Microsoft learn on TDE.

Note: all images in the post are either created through actual code runs or from Pixabay.