Migrating or Recovering a TDE Protected Database

January 30, 2023May 10, 2023 ~ Matthew McGiffen ~ 2 Comments

When encrypting a database with Transparent Data Encryption (TDE), a vital consideration is to make sure we are prepared for the scenario where something goes wrong. For instance, if the server hosting our SQL instance goes belly-up, can we recover the data that we have encrypted with TDE?

In the ordinary recovery scenario, we would make sure that we have appropriate backups of our database, and that they (or copies of them) are stored off the server itself so that we can access them in case of a failure.

If you have followed the instructions in the previous posts, then you will also have taken a backup of the certificate and private key via the following command:

BACKUP CERTIFICATE MyTDECert TO FILE = 'C:\Test\MyTDECert.cer' WITH PRIVATE KEY ( FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7' );

You need to make sure that these are also stored securely off the server and that you have kept the password you used somewhere you can access it – but not so accessible that unauthorized users can read it.

In summary you need:

The database backup file
The backup of the certificate
The backup of the private key
The password used to encrypt the private key

Armed with those objects, you are equipped to restore your database to another SQL Instance. Working on the new SQL instance, the steps are straightforward.

Create a Database Master Key (DMK) if One Doesn’t Exist

The new SQL instance will need a DMK if one doesn’t already exist. You can create one with the following code:

USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';

Note that this will be a new and unique DMK, it will not be the same as the one you had on your old instance – and you don’t need to use the same password to protect it.

Restore the Certificate and Private Key

On the new SQL instance, you need to restore the certificate and private key into the master database with the following SQL:

USE master;
CREATE CERTIFICATE MyTDECert
FROM FILE = ‘C:\Test\MyTDECert.cer’
WITH PRIVATE KEY
(
FILE = ‘C:\Test\MyTDECert_PrivateKeyFile.pvk’,
DECRYPTION BY PASSWORD = ‘UseAStrongPasswordHereToo!£$7’
);

This will decrypt your key using the password supplied, and then re-encrypt it using the DMK you created. Then the certificate and its key will be stored in the master database on your new SQL instance.

If you’ve done something wrong, it’s entirely possible you may get an error at this stage, commonly:
Msg 15208, Level 16, State 6, Line 56 The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

If you’re confident that all details specified are correct, and that the certificate and private key were backed up properly, then the most likely issue is that the current SQL instance doesn’t have access to the file path you’ve placed the files in.

Another possible issue is that you receive an error telling you the certificate already exists. If you have checked and verified that a certificate with the same name doesn’t exist, then the most probable cause is that you have another certificate with the same thumbprint. The thumbprint is the actual identifier for the certificate (the name we give it is more of a friendly name).

Restore the Database

Once you’ve completed the previous steps you are ready to restore the database from the backup. You do that as you would restore any other database. Potentially as simply as with the following command:

RESTORE DATABASE TestTDE FROM DISK = 'C:\Test\TestTDE.bak';

Then you’ll find you can access your database and view data without any issues. At this point you can celebrate – you are done. You only get a problem if you haven’t set up the certificate and key correctly, or you have the wrong one, in which case you get an error like the following:

Msg 33111, Level 16, State 3, Line 2 Cannot find server certificate with thumbprint ‘0x682C8797633B9AD8875967502861CCAE33ECAD66’. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

In the next post we’ll look at one case where you “may” be able to recover your TDE database if you’re in the unfortunate position of not having the certificate and private key from the old SQL Server instance.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

How Long Will it Take to Encrypt Your Existing Data with TDE?

January 13, 2023May 10, 2023 ~ Matthew McGiffen ~ Leave a comment

As we’ve seen in previous posts, the process of turning TDE on for an empty database with no data is instantaneous. Most of the time though you’ll be applying TDE to an existing system where you may have a large amount of data that needs to be encrypted.

The process of setting up TDE is the same whether you’ve just set up a new database, or whether you’re working with a live database. Once you turn encryption on, SQL Server will automatically begin the process of encrypting any data in your database. Be that one row or terabytes of data.

There is nothing extra you need to do to make that happen. However, there are some questions you should be asking. How long will it take to encrypt your database? What sort of overhead is that going to put on your server – particularly for a live system? What should you do if you run into any issues?

Win this post we’re going to look at the first of those questions, how you can get an estimate of how long it might take for the initial encryption of your database. If you want to follow the examples in this post then you should first set up TDE as shown in https://matthewmcgiffen.com/2023/01/06/setting-up-tde/

Benchmarking TDE Performance on Your Server

I’m going to load up my previously created TestTDE database with about 10GB of data so we can see how the encryption process performs. We’ll do this with TDE turned off and then turn it on once the data is loaded. I’m running this on a reasonably quick laptop and it takes a couple of minutes to load the data. If you’ve been following through these examples on your own SQL Server, then first you’ll need to turn TDE off with the following SQL:

ALTER DATABASE TestTDE SET ENCRYPTION OFF;

Then we can load the dummy data with the following code:

USE TestTDE; CREATE TABLE dbo.SomeData(Id INT IDENTITY(1,1), SomeText VARCHAR(255)); GO INSERT INTO dbo.SomeData (SomeText) SELECT TOP 1000000 ('XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c CROSS JOIN sys.objects d; GO 100

After I turn encryption on again, I’m going to run a query to monitor the progress of encryption – polling every 5 seconds. If you’re working through this example on your own machine, you’ll want to have the query ready to go before you turn encryption on.

The command to turn TDE on is as previously stated.

ALTER DATABASE TestTDE SET ENCRYPTION ON;

Then you can immediately execute this query to report the progress.

DECLARE @state tinyint; DECLARE @encyrption_progress TABLE(sample_time DATETIME, percent_complete DECIMAL(5,2)) SELECT @state = k.encryption_state FROM sys.dm_database_encryption_keys k INNER JOIN sys.databases d ON k.database_id = d.database_id WHERE d.name = 'TestTDE'; WHILE @state != 3 BEGIN INSERT INTO @encyrption_progress(sample_time, percent_complete) SELECT GETDATE(), percent_complete FROM sys.dm_database_encryption_keys k INNER JOIN sys.databases d ON k.database_id = d.database_id WHERE d.name = 'TestTDE'; WAITFOR delay '00:00:05'; SELECT @state = k.encryption_state FROM sys.dm_database_encryption_keys k INNER JOIN sys.databases d ON k.database_id = d.database_id WHERE d.name = 'TestTDE'; END SELECT * FROM @encyrption_progress;

The following image shows the results from running this test on my laptop.

You can see it took about a minute to encrypt 10GB of data. Figures will be different on your hardware, but that gives us an idea of the order of magnitude involved. If that scaled up linearly then it might take between 1 and 2 hours to encrypt a terabyte of data. I’m working here with data on an SSD, if you’re using older style disks then it would take considerably longer. I’d recommend using this technique to benchmark how your hardware will perform before you turn encryption on for your production databases.

In the next post we’ll look at how you can monitor for problem during the encryption process and what to do if you encounter an issue.

The Cost of not Following the Regulations Around Data

January 10, 2023May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

Brent Ozar shared in his weekly links this week a GDPR penalty tracker which makes for thought-provoking reading. Regular readers of this blog will know I have a keen interest in data protection and encryption – on which topic I’ve written a book – so it’s interesting so see some figures of what failing in those areas can cost you.

Here’s a link to the report, though it only goes as far as July 2022 at the time of writing:

https://www.enforcementtracker.com/

The biggest fines (both individually and in total) have been levied for improper use of data, with Amazon, WhatsApp, Google and Facebook topping the list. After that though we have fines for insufficient protection of data. In most cases this is where companies have had some form of data breach and the safeguards in place weren’t deemed sufficient. The largest fine in this case was against British Airways who were hacked in 2018 and they received a fine of over 22 million euros for the lack of safeguards. That was calculated as 1.5% of the company’s turnover in 2017.

In the case of British Airways, the problem was an application vulnerability but there have been other cases where the database (or backups) were accessed directly. In these cases, encryption would have prevented the attack and saved the companies in question fines as well as reputational damage. As I’ve mentioned elsewhere in this blog, the advances made to the encryption feature set by Microsoft over the last few releases to SQL Server make it a no-brainer to implement encryption on at least your most sensitive data.

Let’s look at some of the summary figures in a bit more detail. Here we see a table of the total fines levied by type of violation:

And the same information in a graph:

We can see the total fines are in the billions, with incorrect processing and inappropriate use of data receiving the highest penalties. Protection of data is of the most interest to me and that comes 4^th with around 80 million euros of fines in total (that was across about 200 separate violations) – still a large figure.

Some of the areas that the violations cover are probably outside our remit as DBAs and architects, but in some cases we do have some responsibility for pushing to make sure things are doing correctly.

In terms of following data principles these include (but are not limited to) things like:

Data Retention – making sure that personally identifiable data is kept for no longer than required.
Data Minimization – making sure we only collect the data we legitimately need.
Making sure data is processed securely.
We have adequate protection against data loss – things such as backups and checking for corruption.

In terms of illegal data processing, we should call out if we think data is being used for something we don’t have permission to do.

Protection of data where it resides in the database is certainly something we have to own, and shout loudly if things are not being done correctly. This includes having appropriate access controls and thinking about whether it should be encrypted.

One other important takeaway from the GPR tracker is the fact that the number of fines being levied is increasing over time so following the rules around data is continuing to become more and more important.

I should state in closing that this is just a quick post on the subject, and you need to do your own research to understand the rules and how they apply to your role. Many of us (in Europe at least) will have looked at the GDPR in some detail before it came into place, and in many companies, there was flurry of activity to make sure things were up to code. We shouldn’t rest on our laurels though and need to continually think about this stuff.

Got a problem or embarking on a SQL Server project and want some help and advice? I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

Securing the Root Keys for TDE

January 9, 2023May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

In this quick post we’re going to look at an additional step you should take to secure your TDE databases. This is a step you won’t find in any other documentation on setting up TDE in SQL Server that I’ve seen, so it probably bears a little explaining.

We looked at the encryption hierarchy in earlier posts. Let’s look at that again to give us context for what we are about to discuss:

At every level in the diagram, the keys are securely encrypted by the level above. What concerns us here is the very top level in the diagram, the keys used by the DPAPI. These keys are unique to your server, but as they sit at the root level there is nothing left to encrypt them, so they have to be stored unencrypted somewhere.

They keys are held in the following directory on most Windows systems:
C:\Windows\System32\Microsoft\Protect\S-1-5-18

It’s not a trivial technical task, but if someone can access these keys as well as a copy of your database files (including the master database) – or a copy of your database backups including master, then it is possible for them to decrypt the chain of keys working from the SMK down and eventually be able to decrypt your TDE-protected data.

That means if someone has read access to the DPAPI keys then they could access your data. However, this is easy to protect against. You just need to secure the above directory to ensure that only Local Administrators, the LOCAL SYSTEM account and the SQL Server service account can read anything within it. You can do that by applying the appropriate file system permissions if they are not already in place.

In the next post we’ll start to look at things you want to consider when encrypting existing databases that contain data.

Setting up TDE

January 6, 2023May 10, 2023 ~ Matthew McGiffen ~ 3 Comments

Transparent Data Encryption (TDE) is one of the easiest ways of encrypting your data at rest. In the previous posts we looked at what TDE is and how it works.

In this post we go through the steps of setting TDE up. You can set up TDE when you first create a database (which we will look at in this post), or you can apply it to an existing database (which we’ll cover in posts I’ve got coming up). In the latter case, once TDE has been enabled it will encrypt your existing data in the background. In either case the steps are the same.

Creating the Keys and Certificate

The first step in setting up TDE is to create the required keys and certificate. We’re going to focus on the default encryption hierarchy we’ve already looked at where we have the Service Master Key (SMK) at the top level, which protects the Database Master Key (DMK) in the master database, in turn protecting a certificate and associated asymmetric key pair, also in the master database. Finally, at the bottom we have the Database Encryption Key (DEK) in the user database which is protected by the certificate’s asymmetric key.

The SMK always exists for a SQL Server instance, so we just need to create the objects underneath it in the hierarchy. If you are planning on using Extensible Key Management (EKM) for managing your keys, then some of these steps are unnecessary. We’ll cover EKM later on.

Creating the Database Master Key (DMK)

First of all you must have a DMK. This lives in the master database and you can only have one per instance of SQL Server. You can create a DMK with the following SQL command (substitute your own password):

USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';

We’ve already mentioned that the DMK is encrypted by the SMK, so it’s reasonable to ask why in this command we must also specify encryption by a password. What happens when you create the DMK is that a key is generated, two encrypted copies of that key are then created and stored in the master database, one encrypted by the SMK, and one by the password specified (both encrypted using the AES_256 algorithm). Having the copy encrypted by the password is necessary where you might need to restore a backup of the master database (including the DMK) to a separate SQL Server instance where the original SMK will not be available. In general, you don’t need to do this to recover a TDE enabled database to a separate SQL Server instance as long as you have backups of the certificate and private key, however you may use the DMK for purposes other than TDE.

It is recommended you backup the DMK. In most cases this backup is not useful in the context of TDE, but as mentioned you may have other objects not related to TDE that depend on the DMK so it is good practice. Backing up the DMK is a single command:

BACKUP MASTER KEY TO FILE = 'C:\Test\MyDMK' ENCRYPTION BY PASSWORD = 'UseAnotherStrongPasswordHere!£$7';

Creating the Certificate

Next, we need to create a certificate for use by TDE. We do that with the following code:

USE master; GO CREATE CERTIFICATE MyTDECert WITH SUBJECT = 'Certificate used for TDE in the TestTDE database';

This command tells SQL Server to generate a self-signed certificate and associated public\private key pair which can be used for asymmetric encryption. The private key will automatically be encrypted by the DMK. As mentioned previously, you can create a separate certificate for each database you wish to protect with TDE – or you could share the certificate between multiple databases. Having a separate certificate for each database minimizes the attack area if someone gets access to one of them, but you may choose to share the certificate between multiple databases on the same instance for ease of management. Make sure you give your certificate a meaningful and unique name as you may want at some point to migrate it to another server which might already have other certificates used for TDE. You wouldn’t want them to have the same name, and it is useful to easily be able to identify what each is for – for instance you may want to include a suffix to represent the environment the certificate belongs to.

These objects are absolutely critical in being able to encrypt and decrypt your data, so it is essential that you back them up. That can be achieved with the following SQL:

USE master; GO BACKUP CERTIFICATE MyTDECert TO FILE = 'C:\Test\MyTDECert.cer' WITH PRIVATE KEY ( FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7' );

This creates two backup files, one for the certificate and one for the private key. The private key is backed up encrypted by the password supplied. These files, and the password used to protect the private key, need to be stored securely. It is impossible to over-stress how important this is. The most common pitfall people fall into with TDE is needing to recover or restore a TDE-protected database and not knowing where to find these backups or what the password is. Particularly where the person who set TDE has left the organization. If the backup has not been taken, cannot be found, or you don’t have the password it was protected with, then you have the potential to permanently lose all data that has been protected by TDE. If you enter a new organization or role where you are responsible for TDE-protected databases, you should make sure you know where these items are stored or take your own backups if they can’t be produced.

We have seen a few cases in this post where we need to use a password to protect an object. If your organization doesn’t already use one, then you should consider using a password manager such as KeePass (many others exist) to manage your passwords securely and safely.

Creating the Database Encryption Key (DEK)

The DEK is what is actually used to encrypt and decrypt data stored in your TDE-protected database. The DEK is stored in the database itself (in the database root record) but is stored encrypted by the private key we created in the previous step. The DEK is a single key used for symmetric encryption, the same key is used to both encrypt and decrypt data. A symmetric key is used in this case as symmetric encryption is much faster than asymmetric, and we want our transparent encryption activities to occur with a minimum of overhead and latency.

Before we create the DEK we need a database we are going to protect with TDE. If you haven’t already created the TestTDE database then you can do so with the following SQL:

CREATE DATABASE TestTDE;

Now we can go ahead and create the DEK.

USE TestTDE; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyTDECert;

You can see we specify the algorithm – AES (Advanced Encryption Standard) with a 256-bit key is recommended. We also specify the certificate to be used which will identify the public/private key pair to be used to encrypt the DEK.

Unlike the other keys, you don’t need to backup the DEK as it is automatically included in any backups you take of the database itself.

Encrypting the Database

Encrypting the database is the simple action of turning encryption on for the database.

ALTER DATABASE TestTDE SET ENCRYPTION ON;

In this case we have just created a new empty database so there is no data to encrypt and the action is instantaneous. As we then start to add tables and data the data will be automatically encrypted as it is written to disk for the first time. Similarly, any backups taken, Full Log or Differential, will automatically be encrypted as they are written to disk.

In sys.databases you can see which databases have TDE turned on by looking at the is_encrypted column, you can query that as follows:

SELECT name FROM sys.databases WHERE is_encrypted = 1;

We can see the results of the query in this image:

We can see that both our TestTDE database and tempdb get encrypted. As discussed previously tempdb gets encrypted when any other database uses TDE. We can view more details about our TDE encrypted databases by looking at the sys.dm_database_encryption_keys view. Let’s query that view and look at some columns of interest.

SELECT d.name, k.encryption_state, k.encryptor_type, k.key_algorithm, k.key_length, k.percent_complete FROM sys.dm_database_encryption_keys k INNER JOIN sys.databases d ON k.database_id = d.database_id;

This image shows what I see if I executed this query before I turned encryption on.

We can see information about the DEK. We also see the encryption_state column which describes the current state of the database. The possible values you’ll see are:

Unencrypted
Encryption in progress
Encrypted
Key change in progress
Decryption in progress
Protection change in progress (this occurs where the object protecting the DEK is being changed)

The below image shows what we see if we execute the query after encryption has been enabled:

We see that both my database and the tempdb database are now encrypted. We also see the percent_complete column, which confusingly says zero. This column only has meaning when an encryption state change is occurring. So, if the encryption state was 2 (encryption in progress) – then we would see a value here while the database was in the process of being encrypted. Here my database contained no data, so it was instantaneous to flip encryption on. This column becomes relevant when we are encrypting an existing database that has a reasonable amount of data, we’ll look at that shortly.

The query we’ve just looked at will work on all versions of SQL Server. From SQL 2019 however we have a few extra columns of information available to us in the sys.dm_database_encryption_keys view. In particular there is the column encryption_state_desc that give us a plaintext description of the encryption state.

That’s all there is to setting TDE up, in the next post we’ll look at an extra step you may wish to take to improve the security of your data.

How Secure is TDE?

January 3, 2023May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

When we consider how secure a form of encryption is there are two things we want to consider.

What threat scenarios we are protected from.
How easy is it to break down the encryption.

Let’s discuss each of these in turn.

What are We Protected From?

TDE encrypts data stored on the file system, so it should be pretty clear that we are trying to protect ourselves from an attacker who gets access to our files. You would be right in suggesting that shouldn’t be allowed to happen. Access controls should be in place to prevent inappropriate access. The reality though is that sometimes we get hacked and someone is able to work around our access controls. Sometimes backup files are stored offsite with a different organization where we do not control the access. That is why we have encryption – encryption is an extra line of defense. TDE offers no protection however against individuals who have direct access to query the database.

Let’s say someone does get access to our files – does TDE mean we are still sufficiently protected?

The answer unfortunately is that it depends. If someone has managed to get admin access to your database server then if they are sufficiently motivated, they will be able to read your data. TDE only protects you against lower levels of access.

The fact that an admin can get around the protection is an inevitability of many forms of encryption. TDE is managed by an administrator who has the sysadmin permissions on the database instance. They can enable TDE, they can disable TDE. They can change keys or export existing ones with their own password – but also they have direct access to the data anyway, so they can just run queries to view data.

Not all users who have admin rights over the box will have admin rights over the SQL instance, but anyone who has admin rights over the box can add themselves as an admin of the instance – though that usually requires restarting the SQL instance.

There are also other ways an admin can extract the keys from the file system – this is more complicated, but can be done if someone is knowledgeable enough.

So TDE only offers a very specific, but still very important protection. If you need more then you will have to consider other forms of protection – such as Always Encrypted – possibly in conjunction with TDE.

How Easy is It to Break Down the Encryption?

TDE implements symmetric key encryption using standard encryption algorithms based on AES (Advanced Encryption Standard). When you set up TDE you can specify which AES algorithm you wish to use, AES_128, AES_192 or AES_256. In each case the number specifies the length of the key to be used for encryption in bits. Currently the only known way to crack such encryption is by brute force, i.e. try all the possible keys until you get lucky.

Obviously the longer your key, the harder the encryption should be to break, however even for AES_128, estimations of how long it would take to break down the key by brute force vary between a thousand years, to numbers many times greater than the age of the universe – trillions of years.

The difference in those estimates is based on how we anticipate processing power to grow in the future. In particular, whether the development of quantum computing might allow such activities to be carried out millions or billions of times faster than with conventional processors.

Even with the lowest estimates AES_128 should theoretically be sufficient in most scenarios but most people go for AES-256 which requires the same number of operations squared to crack. I recommend using AES-256 which should remain safe even if we see a quantum leap in processing power that exceeds all current expectations.

Up to 2016, SQL also supported the TRIPLE_DES_3KEY encryption protocol. This is now generally not considered to be as secure as AES, and from SQL 2016 its use is deprecated. So, it is best if you stick to AES even if you are on an older version of SQL Server.

In the next post in this series we’ll look at setting TDE up.

Understanding Keys and Certificates with TDE

December 21, 2022May 10, 2023 ~ Matthew McGiffen ~ Leave a comment

When you first look at the encryption hierarchy for TDE in SQL Server it can be a bit daunting. There seem to be a lot of objects involved and it might not be clear why each is required. It can be tempting to skip a full understanding of all the objects and just get on with setting things up – which is relatively straightforward.

I’d encourage you not to do that and I’ll explain why. There are a lot of scenarios that might crop up in the lifecycle of a TDE protected database instance. Recovering a protected database from backup. Migrating database from one server to another. Managing high availability. The list goes on.

There are a lot of resources out there that will advise you on how to do these things – but many of them are inaccurate. Only by understanding how TDE works – and in particular how it uses the various encryption objects, can you be sure what approach is correct. And better than that – if you have a good understanding of the architecture of TDE you won’t need these resources, the correct approach will follow logically from your understanding of how things work.

TDE uses multiple keys and certificates in the process of protecting your database. This is something we’ll see in common with other forms of encryption. The reason for that is both about providing the best level of protection as well as supporting ease of management for your protected databases.

Keys in SQL Server often have at least three components:

The Encryption Key itself – Usually can be thought of a number expressed in binary format. Long and random enough to make it difficult to guess even by brute force attempts. This is the key that is actually used to encrypt and decrypt your data.
Another object that’s used to protect the key – This object might be another key, it might be a certificate, or it might just be a password. This object is used to encrypt the encryption key.
The encrypted value of the encryption key – Formed from the original value of the key, encrypted by the protecting object.

In SQL we rarely (maybe even never) see the actual value of the key. We have the encrypted version and we usually know what object was used to encrypt it. That second object may even be another key that is itself encrypted by a third object.

When it comes down to it though, the actual thing that is used to encrypt or decrypt data is the key itself, not the encrypted value, and not the hierarchy of objects that may have been used to protect it.

So, all I really need to read your data, is your key.

Let’s look at that in the context of TDE. Here we see the hierarchy of encryption objects supporting TDE. There are other ways of working with TDE but the approach shown here is the standard.

Let’s look at the objects one at a time.

Database Encryption Key (DEK)

Right at the bottom of the hierarchy is the DEK. This is stored in the database itself and is what is actually used to encrypt and decrypt data in the database. The DEK is a symmetric key, which means the same key is used to both encrypt and decrypt data. Symmetric key encryption is much quicker than asymmetric key encryption which is why such a key is used in this case. We want the “transparent” encryption activities to have as little overhead as possible while keeping things secure.

The DEK is actually stored encrypted in the database. Encryption wouldn’t be very effective if the key was stored in plain sight for anyone to access and use. As it is stored encrypted, even if someone has your files, there is no way for them to access the unencrypted version of the DEK and use it to read your data.

The encrypted DEK is also stored in any database backups

Certificate and Associated Asymmetric Key Pair

At the next level up we have the Certificate, which contains a Public Key which can be used to encrypt data and has a reference to a Private Key which must be used to decrypt data. The two keys together are known as a Public\Private Key pair and are used to encrypt the DEK using asymmetric encryption.

The Public key can be held in plain sight as it can only be used to encrypt data. The Private Key is required to decrypt data. The Private Key is in turn encrypted to make sure it is protected.

There are other options for protecting your DEK which include using an asymmetric key that is stored externally to your SQL Server. This is managed through something called Extensible Key Management (EKM) and we’ll look at that in later posts.

Database Master Key (DMK)

The DMK is stored in the Master database and is what’s used to encrypt the private key. However, the DMK isn’t just used for your database, and indeed isn’t just used for TDE. It can support a number of activities. There can be only one DMK for your SQL instance. The DMK is in turn encrypted.

Service Master Key (SMK)

The SMK is created when you first install your SQL Server instance and is unique to the instance. The SMK is used to protect your DMK. It is in turn protected by the operating system Data Protection API (DPAPI).

Understanding the Need for the Hierarchy

That’s a lot of layers of encryption to deal with. It can be difficult to understand why we need so many.

Let’s look at one scenario to aid our understanding. We’ll look at how it works in practice and what the problems might be if we didn’t have all these different levels of encryption.

Let’s say you want to restore a TDE protected database to another server – a common requirement. The encrypted DEK is already stored in your backup file – so we have that. You would be forgiven however in thinking that we need to migrate copies of all the layers above it in order to be able to read our protected database.

We run into problems however as soon as we think about migrating the DMK. Remember there can only be one DMK per instance, what if the instance we are restoring to already has a DMK, and that DMK is already used to protect other objects. We can’t just replace it. That is why it is critical there must be an object between the DMK and the DEK, the DMK cannot be used to encrypt the DEK directly.

However, we still need the DMK to read the private key associated with the certificate. Don’t we?

In practice you are able – and pretty much required – to back up the certificate to disk and also the private key associated with it. If you don’t do this and it is lost (for instance if the server crashes and is unrecoverable) then your data is pretty much lost.

The command for backing up the certificate looks something like this:

USE master; GO BACKUP CERTIFICATE MyTDECert TO FILE = 'C:\Test\MyTDECert.cer' WITH PRIVATE KEY( FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', ENCRYPTION BY PASSWORD = 'C0rrecth0rserbatterystab1e' );

You can see that when we backup the certificate we specify a password to encrypt the private key. Remember that the private key was already encrypted by the DMK? This BACKUP CERTIFICATE command doesn’t just encrypt it a second time – what would be the point of that? No, the reason we need to supply a password is that the command retrieves the unencrypted version of the private key, re-encrypts it with the password INSTEAD and then that is what gets saved to disk.

Remember that at the point I run the command SQL has access to all the objects, all the way up the chain, that are used for the encryption. So, it has no problem getting the actual value for the private key.

Now, when I restore that certificate to – let’s say another instance of SQL Server – the command looks like this:

USE master; GO CREATE CERTIFICATE MyTDECert FROM FILE = 'C:\Test\MyTDECert.cer' WITH PRIVATE KEY( FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', DECRYPTION BY PASSWORD = 'C0rrecth0rserbatterystab1e' );

Considering what the BACKUP command did, you can figure out that the above command will do the opposite. It first of all decrypts the private key using the password supplied. Then it encrypts it again using the DMK for the local instance before saving it in the master database locally.

Remember it is the actual values of keys that get used for encryption – not their encrypted value. So, the private key for this certificate is exactly the same as it was where we backed it up from – though the encrypted value will be different as it is protected by a different DMK.

If I now go to restore a copy of a database whose DEK has been encrypted with this certificate onto the new instance I can do so without any problem.

Many articles will tell you that you also need to migrate the DMK from your old instance, and some will say that you also need the SMK. This is simply not correct – I mean, it will work if you do that, in the right order, but it isn’t necessary.

So all you need to have to restore a database protected by TDE, is the database backup, the certificate/private key backups, and the password specified when the certificate and key were backed up.

Equally that’s all someone else needs too, so make sure those things are protected.

There are other reasons it would not be desirable to have the DEK directly protected by the DMK. We can have a separate certificate for each database protected by TDE if we wish – and I would suggest that is good practice. Someone could steal the backup of that, not a big deal unless they have the password used to protect it also. If they do, then they can access our data. That’s not good, but at least we have minimized the attack surface and they have only accessed one database. If the DMK was used to protect the DEK directly then we would need to back that up in a similar manner – and if that was stolen in the same way then protection for all objects and databases protected by the DMK would be breached.

We’ll see other scenarios where the multiple levels in the encryption hierarchy for TDE help us in the later posts when we talk about managing TDE and its lifecycle with your databases.

What is TDE (Transparent Data Encryption)?

December 19, 2022May 10, 2023 ~ Matthew McGiffen ~ 2 Comments

TDE is referred to as a “transparent” form of encryption. What that means is that the process of encrypting and decrypting data is fully performed in the background. The queries we write to access data are unchanged whether TDE is enabled or not. So, enabling TDE has no impact on application functionality, does not require refactoring of code, and is therefore relatively easy to implement. TDE encrypts all the data in a database, so you don’t need to choose which data items to encrypt.

TDE allows us to encrypt “at rest” data. When we talk about “at rest” data we are referring to data that has been written to disk. TDE works at the IO level, encrypting data automatically as it is written to disk, and decrypting it as it is read from disk.

In terms of our SQL databases the assets that are protected include:

Any data files for our database.
Any log files for our database.
All backup files for the database, Full, Log or Differential backups.
Database snapshot files.
Also the TempDB database data and log files are encrypted.

The last item in that list, TempDB, needs to be encrypted for completeness of protection. Imagine that you query your database and as part of the query execution TempDB is used. If that data were written to disk, then that creates a hole in our protection, someone could potentially read or copy the TempDB files and might see some of the data we are trying to protect. As a result, when you enable TDE against any database on your SQL Server instance, the TempDB database is automatically encrypted as well to prevent this from happening.

It’s reasonably obvious to state that data “at rest” doesn’t include the following things:

Data loaded/stored in memory (buffer pool).
Data returned from a query and being passed across the network.
Data received by a client as a result of a query.

If you want to cover those scenarios as well then you need to look at other forms of encryption such as TLS and Always Encrypted.

There are also some less obvious exceptions:

Filestream data.
Data persisted to disk using Buffer Pool Extensions.

And there are a couple of other exceptions that can occur in particular circumstances:

Where the buffer pool gets paged to disk due to memory pressure.
SQL dump files when there is a crash.

What does and doesn’t get encrypted by TDE is summarized in the below diagram:

Let’s have a look at the contents of some SQL data files so you can see the difference with and without TDE. I’ve created a database with a single table and inserted a row of data with the following code:

CREATE DATABASE TestTDE; GO USE TestTDE; GO CREATE TABLE dbo.SomeData (Id INT IDENTITY(1,1), SomeText VARCHAR(255)); GO INSERT INTO dbo.SomeData (SomeText) VALUES('This is my data'); GO

I’ll close my connection from the database, and detach it so I can open the files in a Hex Editor. You can detach the database with the following SQL:

USE master; GO EXEC master.dbo.sp_detach_db @dbname = N'TestTDE';

Then I open the file in my Hex Editor and search for the text “This is my data” in the data file:

As you can see the data is stored as clear as day in the data file.

Now let’s look at the same data file once TDE has been enabled (we will look at enabling TDE in later posts). This time if I search for the same text it’s not found, and my data looks like that shown below.

It’s interesting to also look at the end of the database file where there is free space. In the unencrypted version that free space would have simply been represented by zeros. In the encrypted version that free space too has been encrypted, so an attacker cannot even see where your data ends (Figure 2-4).

TDE works by using an encryption key that is stored in the database being encrypted – but that key is itself stored encrypted by an object outside of the database. We’ll look at the various objects involved in the next post.

Recommended Approach to Encryption in SQL Server

December 14, 2022May 10, 2023 ~ Matthew McGiffen ~ 2 Comments

We looked earlier at the various places our data lives – on disk, in memory, in-transit across the network. A good strategy will protect all of these locations – sometimes with multiple layers. Exactly how you go about doing that for your applications may vary – but as long as you are on SQL Server 2016 or higher there is a default strategy that you should consider. This combines a number of the available SQL Server features to provide the best protection.

TDE. For at-rest protection of all your data.
Always Encrypted. To encrypt all (or most) columns that contain personal identifiable, or sensitive, information.
TLS. To make sure network communications between the application and server are encrypted.
Hashing and Salting of passwords. To make sure passwords are secure – and we never need to store the actual password in the database.

On top of that you should definitely consider using EKM. In previously times, when we had to have dedicated hardware to support it, there was a fair barrier to entry. Now with easily usable and cost-effective cloud solutions it’s easy to get started with EKM, and certainly has many advantages.

Encryption is easiest to build in “by-design” when developing new applications, but more often than not we are implementing or enhancing encryption against our existing applications. It’s great if you are in that situation and have the scope to implement a full encryption strategy as outlined above. Such projects often have time and budget constraints though, or need to be delivered incrementally. If that’s where you are at, then you may want to focus first on where you can achieve the most with the least effort. You need to assess whether implementing any of the features above is going to have a performance impact that worries you, once you’ve dealt with that you might want to look at things in this order:

Hashing and Salting of passwords. I put this item first because you should never be storing passwords in plaintext in a database. If you are encrypting passwords before storing them then that’s better than plaintext but still you should be looking at a hashing method.
TLS. You really should have this on for all your connections between your applications and SQL Server that could contain data or other information you care about protecting. TLS is very easy to set up and can be done very quickly.
TDE. TDE is again very easy to setup, and it comes for free with the standard edition of SQL Server 2016 SP1 onward. It used to require you to be on enterprise edition so that was a good reason why many people didn’t use it. TDE is only going to protect you against a limited number of scenarios, but you still get that for not much more than the flick of a switch.
Always Encrypted. Column encryption with Always Encrypted is a little harder to understand and there are limitations on the ways you can work with encrypted data. It is however the best tool for protecting your personal and sensitive data. If your project has limited resource (what project doesn’t) focus on encrypting first the most sensitive items, and those where you won’t need to make code changes to work around the fact that data is encrypted. Code changes are likely to be required where you need to search against, or perform calculations on, data that you wish to encrypt.

Overview of SQL Server Encryption Features

December 12, 2022May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

SQL Server has had the native ability to encrypt data since SQL Server 2005. This included functionality that could be used to encrypt individual items and columns of data as well as the Transparent Data Encryption (TDE) feature which was available with the enterprise edition of SQL Server and could be used to encrypt all data where it is stored on disk. We then didn’t see significant additions to the features available for encryption until 2016 when Microsoft added Always Encrypted for column encryption. In SQL Server 2019 Microsoft made TDE available in standard edition, and also in SQL Server 2019 the ability to use enclaves was added to Always Encrypted to improve the available functionality for interacting with encrypted data. Finally in SQL Server 2022 further enhancements were made to the set of functionality available when working with Always Encrypted with enclaves.

We can see Always Encrypted (which was introduced in 2016) as the successor to previous methods of column encryption, and in later posts we will go into a high level of detail on Always Encrypted, although we’ll also cover the older methods in brief.

The table below shows the list of encryption features in SQL Server and what each tool is intended to protect.

Encryption features available in SQL Server

Feature	What is Protected
Transparent Data Encryption (TDE)	Data saved to disk. This includes data files, transaction log files, backup files and database snapshots.
Backup Encryption	Backup files.
Always Encrypted	Data stored in columns. With Always Encrypted the data is protected on disk, in memory and in-transit across the network.
Transport Layer Security (TLS)	Network traffic. TLS protects data in-transit across the network as well as commands executed against the database server.
Hashing and Salting	This is not strictly encryption, but we generally use it to protect passwords.
Encryption Functions	Data stored in columns. Here we are referring to the encryption functions introduced in SQL 2005 that pre-date Always Encrypted.
Extensible Key Management (EKM)	This provides extra protection and ease of management for encryption keys by enabling them to be stored with an external provider.

Let’s take a brief look at each of these features in turn.

TDE

TDE protects our data stored on disk, what we often refer to as “at-rest” data. It offers good protection against the scenario where the file system is accessed, and an attacker might attempt to retrieve data directly from the database files themselves – or copy the backup files so they can be restored to another SQL Server to access the data. It doesn’t protect us at all though where an attacker may have access to query the database directly. The “transparent” part of the name refers to the fact that TDE works transparently in the background with no impact on our queries or other application functionality. TDE protects all of the data in a database, unlike methods of column encryption which usually target specific types of information to encrypt.

Backup Encryption

Backup encryption just encrypts our backup files. This includes full backups, differential backups and log backups. This is particularly useful where we might store backups, possibly on tape, off site and want to make sure they are inaccessible if stolen. TDE also does this for us, so we only consider using backup encryption where we can’t use TDE for some reason.

Always Encrypted

Always Encrypted is a form of column encryption. It works hand in hand with the client driver that your application uses to connect to and query the database to ensure that data remains encrypted all the way to the point it reaches your application. That’s what the “always” part of the name refers to. Data is protected at-rest, in-memory and in-transit across the network. Encryption and decryption actually take place within the client driver rather than within SQL Server.

On this blog we will look in depth at two flavors of Always Encrypted. We have the basic version that was introduced in SQL Server 2016, and Always Encrypted with Secure Enclaves that was added in SQL Server 2019. What’s nice about Always Encrypted is that encryption and decryption are carried out automatically for you by the client driver so in many cases you may not even have to make code changes. There are limitations on how you can interact with encrypted data though. The version with enclaves removes some of those restrictions by allowing certain activities to place in a secure portion of memory (called an enclave) on the database server. The use of enclaves does however come with an extra overhead in setup and management.

TLS

TLS is used to encrypt network traffic. That means that data and queries sent between the application and database server are all encrypted. This is similar to SSL, which most people are familiar with for encrypting internet traffic (SSL in most cases actually uses the TLS protocol).

Hashing and Salting

Hashing and salting isn’t actually encryption because it is a one-way process. Hashing is where we run a value through a function that produces a seemingly random output. That output will always be the same for the same input value, but cannot be reverse-engineered to find the original value. Salting is a method to provide extra security for hashed values. Hashing and salting is considered the best practice for storing passwords as it means we don’t even need store actual passwords – so there should be no way for an attacker to access them.

Encryption Functions

Here we refer to the set of encryption functions that SQL Server implements to allow you to encrypt your own data. I see Always Encrypted as the successor to these functions and would recommend you use that where possible. Encryption using the functions is a bit more limiting, a bit less secure and a bit harder to implement than with Always Encrypted. There may be some scenarios where you want to use them though, so we’ll cover them in brief later on – though hopefully in enough detail that it tells you everything you need to know.

EKM

Most encryption is based on keys, and we need to think about how and where we manage them over time. EKM is functionality that allows you to store them outside of your server, either on a piece of kit that sits in a rack in your server room called a Hardware Security Module (HSM), or more commonly these days, using a cloud service like Azure Key Vault. You don’t need to use EKM in order to implement a secure encryption strategy but it’s certainly worth considering due to the ease of management that comes from having all of your keys in one place. It’s also easier to manage policies such as access control when you take a centralized approach to storing your keys.