Impact of TDE on Performance

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

Microsoft states that enabling TDE usually has a performance overhead of 2–4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest. However, you may have heard other sources saying that it’s actually a lot more than that – and the performance impact is a high price to pay for the level of protection offered. So, what’s the truth?

Where Do We See an Overhead?

When we talk about performance, we are likely to be concerned about two things. One is the impact on query performance. Are my queries going to execute slower with TDE enabled? The other is what overall pressure is going to be added to the server.

The important point to start with is in understanding where and how TDE adds overhead. Encryption occurs as data is written to disk, and decryption occurs as data is read from disk. Each of those activities uses CPU. So, the CPU overhead added by TDE is going to be in proportion to your disk activity. If you have a system that is heavy on IO, then there is going to be more CPU overhead.

SQL Server tries to keep data that is referenced repeatedly in memory (the buffer pool). So, if your SQL instance is provisioned with enough memory, a lot of your read queries can access the buffer pool and don’t have to go out to disk. Such queries should not be affected performance-wise by TDE. There may be other read queries however that access older data that hasn’t been read for a while, and these queries would need to retrieve that data from disk and so there would be an overhead from TDE.

Any queries that modify data will need the outcome to be written to disk, so in these cases we will see an overhead. This overhead is likely to come in two parts: first when the transaction is written to the log file before committing and then later as the updated data gets written to the data file as part of a checkpoint operation.

We also have other operations that write or update encrypted data on disk, so we would also expect these to have some overhead. This would include operations such as index rebuild operations.

You can see from this that the overhead will very much depend on how your application interacts with your data. At one extreme, if you have a set of static data that is small enough to be held in memory and is queried regularly, then there should be no overhead. At the other end of the spectrum, if you have an application that writes to the database a lot and reads less often, then the overhead will be higher.

How to Estimate the Performance Impact for Your Server?

Performance impact for your queries is going to be very much on a case-by-case basis, but in reality, it’s generally likely to be quite small. The reason for that is that, as discussed, we’re only going to see extra CPU requirements when our query needs to access the disk. Reading from and writing to disk is itself an activity that takes time, and even with the fastest disks, encryption/decryption is likely to take no longer than the disk access time. The encryption activities can usually be carried out in parallel to the disk activity, so you don’t see much increased time to read or write data. We’ll see an example of that shortly when we look at how you can get an idea of likely overhead on your server.

In terms of estimating overall overhead on your server, you need to understand the level of IO on the server as well as how well encryption will perform on the box.

Let’s work through an exercise to get an idea of the sort of numbers we might be talking about. For this, we’re going to need the database we created in How Long Will it Take to Encrypt Your Existing Data with TDE? that has about 10GB of data in a single table. We’ll also need a database that has the same set of data but without encryption turned on so we can get comparison figures. You can create that using the same scripts – just don’t run the final step of turning encryption on. We’ll call that database TestTDEOff.

We’re first going to run a query that will force SQL Server to read all the data in a table. We’ll repeat that across four scenarios:

TDE-protected database where the buffer cache is empty, so all data has to be read from disk
TDE-protected database where all the data for the table is in the buffer cache, so no data has to be read from disk
Database without TDE where the buffer cache is empty, so all data has to be read from disk
Database without TDE where all the data for the table is in the buffer cache, so no data has to be read from disk

Here is our query:

DBCC DROPCLEANBUFFERS; SET STATISTICS IO, TIME ON; SELECT * FROM dbo.SomeData WHERE Id = 100000000; SELECT * FROM dbo.SomeData WHERE Id = 100000000;

The DBCC DROPCLEANBUFFERS command flushes all data held in the buffer cache. You won’t want to do this on a live system as it will affect performance, but if you have a server with similar hardware, you can run this to get an idea of how encryption performs.

The test runs the same select statement twice, once with no data loaded into memory and a second time once data has been loaded by the first run. We use the SET STATISTICS command to output information about performance to the messages tab in SSMS. The table we are querying from is a heap and has no indexes defined, so SQL Server has no option but to scan the whole table in order to generate the result set.

Let’s look at an extract of the information outputted by STATISICS IO, TIME to see what we’re getting. This is for the database with TDE enabled:

Table 'SomeData'. Scan count 13, logical reads 1204820, physical reads 0, page server reads 0, read-ahead reads 1203777, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:

CPU time = 10046 ms, elapsed time = 5580 ms.

Table 'SomeData'. Scan count 13, logical reads 1204820, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:

CPU time = 12407 ms, elapsed time = 1050 ms.

We have two sets of output here: one for the first run where there was no data loaded into memory and one for the second once the data was loaded. The key difference is that in the first we have a large number of “read-ahead” reads, which are where data is read from disk. Read-ahead refers to the fact that they are read in parallel with the processing, rather than all needing to be read before the CPU can get to work. In the second output we only have “logical” reads where data is read from memory.

You get a reasonable amount of variance in the CPU and elapsed times when running such tests, so I executed the query five times against each database, averaged the results, and rounded off to the nearest 100ms. The figures are shown below.

	CPU time (ms)	Elapsed time (ms)
TDE with all data read from disk	10,600	5,600
No TDE with all data read from disk	7,600	5,700
TDE with all data read from memory	12,400	1,100
No TDE with all data read from memory	12,200	1,100

Due to the variance between test runs, we’ll ignore small differences. There are a few key takeaways:

The elapsed time was about the same with and without TDE.
The CPU consumption was about the same where data was read from memory.
When reading from disk, there was a higher CPU consumption when TDE was enabled.

That is about what we would expect; TDE only adds overhead when reading or writing to disk. When we were reading from disk, my disk was being accessed at full speed and the disk access time was the bottleneck, so the decryption required by TDE was easily able to complete while that was occurring.

In terms of what this shows regarding the performance impact on a production server, there are a few ways you can think about the data.

The scariest way of looking at it – and not necessarily the correct one – is to focus on the fact that when reading from disk TDE added about 3 seconds of CPU. That was about a 40% increase. The reason that’s not going to be the impact you see in live though is that (hopefully) most of the data accessed by SQL Server is already going to be sitting in memory and so will not be affected. Still, I might envision that I’m going to see between a zero and 40% impact.

Another way to look at it is to realize that my disk was maxed out during this test, loading about 2GB of data per second (actually slightly less but we’ll stick with round numbers). I can calculate that during every second of execution about an extra half a second of CPU, power was consumed by the TDE decryption. That equates to half a CPU core being busy. My machine has 12 cores so that’s about 4% of physical CPU overhead added. Running the Windows Performance Monitor (perfmon) during the tests, I can see that is about right. If I only had four cores in this box, I’d be using the same half a core, so that would be about 12.5%. It couldn’t go any higher though because my disks are already maxed out. I’d have to be able to physically read data from disk quicker in order to create more TDE overhead. On this box, I can see that decrypting data with TDE costs me about 0.3 seconds of CPU per GB of data.

Where this leaves us is that the best way to get an idea on TDE impact on a particular server is to look at the level of disk access and the number of CPU cores. You can look at how much disk access you have (to the relevant database data and log files only) at peak times and get an idea from there. Hopefully you can see that to add a significant percentage of CPU overhead with TDE, you’re going to need to be reading a huge amount of data from disk, have superfast disks, and not have a lot of CPU power.

If you are able to run through something similar to this exercise to benchmark a production system – and you come up with numbers that worry you – I’m going to suggest that you might have an excessive amount of disk access, and that might be indicative of a problem. In particular, you may want to consider if the server would benefit from having more memory so that data can be held in the buffer cache for longer – and not read from disk so often. Also, are queries being forced to scan whole tables due to a lack of adequate indexes – or because they are poorly written.

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 to discuss what I can do for you, or check out my services page to see what kind of things I offer.

Errata for my book: Pro Encryption in SQL Server 2022

March 21, 2023May 10, 2023 ~ Matthew McGiffen ~ 3 Comments

My biggest fear when my book went into production was that any factual errors had slipped through my checks and the various reviews. I had a lot of reviewer support from Apress, but at the end of the day any issues are my responsibility.

So far I’m not aware of any factual errors but one kind reader (Ekrem Önsoy) has shared with me a few typos they have found. I’m going to document them here and will keep this post up to date as I’m made aware of any others:

Page 158

“In practice however some of the steps detailed earlier can’t be carried out directly from T-SQL, and these include generating the certificate and generating the new values of your CEKS encrypted by the new CEK.”

The highlighted term is incorrect, should read:

Page 204

“Now is a good time to check that you can connect to SQL Server on your VM from SSMS on your host machine. Connect with the IP address 192.168.03”

The IP address referenced here should read 192.168.0.3

Page 221

“We’re going to use the lab environment we created in Chapter 14 and connect to our SQL Server via the IP address 192.168.03 again;”

Again, the IP address referenced here should read 192.168.0.3

Page 232

“To be specific, how you can you ensure that the enclave is hydrated with the right CEKs”

Should read:

“To be specific, how can you ensure that the enclave is hydrated with the right CEKs”

That’s it so far. Thanks again to Ekrem, and if anyone else spots any errors please let me know.

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.

Rotating a Certificate Used by TDE

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

In terms of encryption, Key Rotation is the process of replacing your encryption keys on a periodic basis. This is considered good practice and is required by many security certifications.

In practice, if you had to rotate/replace the key that is used to encrypt your data then that would be an intensive activity requiring all your data to be decrypted with the old key before being replaced with the new. This could also create a vulnerability where data sits in an unencrypted state during the process.

This is a reason why many forms of encryption maintain a separation between the actual key used to protect the data and a second key used to protect the encryption key. In terms of TDE, we have the DEK which is protected by the certificate and associated key pair. In general with TDE, when we talk about rotation, we just rotate the certificate. This means the activity can be done without changing the underlying DEK, so the data does not need to be decrypted and re-encrypted. Thus, there is minimal overhead and the process is quick and secure

Many sources will tell you that this process DOES decrypt and re-encrypt your data. This is not correct and can be demonstrated with a simple test.

The DEK used by TDE is held securely. It is only stored encrypted in the database, and we never see the unencrypted value of the key. The certificate however is a little more public and we must backup it and the private key outside of the database. This makes it a little more vulnerable and so it makes sense that we would want to rotate it periodically. Due to that consideration certificates have an expiry date. This date is a reminder to us that, as a good practice, we should create a new certificate and use that going forward before the existing one expires.

TDE doesn’t stop working if the certificate expires, it is up to you to monitor your certificates and replace them when they come to the end of their life. One option is to monitor them using Policy Based Management.

Creating a New Certificate

If we query the sys.certificates view with the following SQL we can find our TDE certificate and examine the expiry date:

USE master; SELECT name, subject, expiry_date FROM sys.certificates WHERE name = 'MyTDECert';

Here is the output:

I didn’t specify an expiry date for this certificate when I created it, so it was automatically given one that was a year in the future. Let’s create a new certificate, and this time we’ll specify a longer expiry. Then we will rotate the encryption to use that one. Here’s the code to create a new certificate and specify the expiry date:

USE master; CREATE CERTIFICATE MyTDECert_with_longevity WITH SUBJECT = 'Certificate used for TDE in the TestTDE database for years to come', EXPIRY_DATE = '20251231';

Let’s have a look at the values that has in the sys.certificates view using the same query from above:

We’re now ready to “rotate” from the old certificate to the new one.

Rotating the Certificate

Rotation is the process of moving from the old certificate to the new one. In this case all that happens is that the encrypted value of the database encryption key (stored in our TDE protected database) is decrypted with the old certificate, re-encrypted with the new certificate, and that new encrypted value is stored in the database, overwriting the old one.

The key value itself hasn’t changed, just the object protecting it, and as such we can still read/write data from the database without any change to the encryption of the underlying data. It is a simple command to rotate the certificate:

USE TestTDE; ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE MyTDECert_with_longevity;

The operation is almost instantaneous, shouldn’t require any database down time, or create additional overhead on your server. Though in production I’d still do it when things are quiet just in case anything goes wrong!

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.

Recovering a TDE Database Without the Certificate

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

If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then you could be in a lot of trouble. There is one scenario where you have a way out. I’m going to assume you don’t have the possibility to recover your old server from a complete file system backup – if you do then you can do that and access all the keys you require. If the two following things are true though then you can still recover your database:

You have a backup of the master database from the previous instance.
The previous instance used a domain account as its service account.

The reason you are going to be okay is that all the objects in the SQL Server Encryption Hierarchy that sit above the Database Encryption Key (that exists in your TDE database) are stored in the master database. That includes the certificate and associated keys, the Database Master Key (DMK) and the Service Master Key (SMK). There are two copies of the SMK:

One encrypted by the keys associated with the machine account.
Once encrypted by the keys associated with the SQL Server service account.

The first copy is only going to be of any use to us if we can recover the old machine (and its account) directly from backups, but we’ve already ruled that out.

If the service account is a domain account though then we should be able to use it. The method is going to involve:

Setting up a new SQL instance using the same service account as the old instance.
Restore your backup of master from the old instance onto the new instance.
Reboot your new server – that’s the whole server, not just SQL.
Backup your certificate and private key – and don’t lose them this time!

My personal opinion is that it’s not the greatest of ideas to restore the master database from one instance onto a new one and expect everything to work okay. So, I’m only suggesting you use this so you can recover the certificate. Once you’ve got that, I would go back to the steps in the previous post on recovering your TDE protected database(s).

Let’s go into each of these steps in a little more detail.

Setting up a new SQL instance using the same service account as the old instance

What this obviously means is that your server must be on the same domain as the old server (or at least another domain that is trusted). You also must have the credentials for the service account.

You can’t fake this, for example setting up a new account on another domain called the same thing as the old one. The new account won’t have the same keys associated with it as the ones used to encrypt your SMK, so you will achieve nothing.

Restore your backup of master from the old instance onto the new instance

There are a lot of resources available on the internet that tell you how to do this in detail and will give you a number of methods you can use. In short you need to first stop your new SQL Server instance and then from a command prompt start it in single user mode with the following command:

sqlservr.exe -c -m -s {InstanceName}

Then you need to (again from a command line) issue the command to restore/overwrite the master database. First start SQLCMD with this command:

sqlcmd -s {InstanceName}

Then at the prompt that opens up within your command window you can execute the following SQL:

RESTORE DATABASE master FROM DISK = ‘C:\Test\master.bak’ WITH REPLACE;
GO

Reboot your new server – the whole server, not just SQL

If you restart the SQL Server service, rather than the machine itself, you can still go in and everything looks okay. You can even restore a TDE database from your old instance and you’ll find you can access the data.

Everything is not okay though, and if you tried to backup your certificate and private key you would get an error like the following:

Msg 15151, Level 16, State 1, Line 7 Cannot find the certificate ‘MyTDECert’, because it does not exist or you do not have permission.

The reason for this error is that the SMK isn’t in the correct state. The copy that is encrypted by the service account is fine, but the copy that is encrypted by the machine account is currently using the wrong machine account. You need to reboot the whole server to fix this, just restarting SQL doesn’t do it. On a full restart the SMK is retrieved from the copy encrypted by the service account, and then encrypted with the current machine account. That version then replaces the one using the wrong machine account.

Once that’s done the encryption hierarchy is fully fixed, and the certificate becomes accessible for a backup command.

Backup your certificate and private key – and don’t lose them this time

I’ve given the command to backup these a few times, but here it is again:

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

You can now take those backup files and use them to restore the certificate and key to the SQL Server instance of your choice, and then restore the backups of your TDE protected database(s).

This has been mentioned a few times, but I’ll re-iterate once more as it is the most important issue to consider when managing TDE. Making sure you don’t lose these backups – or the password – is a vital consideration. If you’re responsible for setting up any form of encryption you need to think about the process that’s going to manage the objects used to protect your data. People move from one role to another, from one company to another, and often things tick along happily for many years before a failure happens. You need to be confident that come next year, or in five or ten years, whoever is responsible for the data will be able to recover it if the worst happens.

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.

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.

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.