TDE in Standard Edition on SQL 2019

Recently, Microsoft quietly let us know that TDE (Transparent Data Encryption) will be available in the Standard Edition of SQL Server 2019. If you don’t follow SQL topics on Twitter then it would have been easy to have missed that.

Transparent Data Encryption is the ability to have all your data stored encrypted on disk – otherwise known as encryption at rest. This is data files, log files and backups. TDE allows this without you having to change anything in your applications or code (thus the transparent part).

This is big news. Given the upsurge in awareness of data protection – largely driven by GDPR and other data regulation – encryption is a hot topic.

Management in most organisations tend to be keen to have encryption at rest – especially if it doesn’t cost much, and is easy to implement. It doesn’t always achieve as much as people might think – but it’s still very much a desirable feature.

For us as SQL Server DBAs I can see this change meaning a couple of things. The first is that there’s likely to be a push to upgrade to SQL Server 2019 much sooner than we might have seen with previous versions. It’s often having a “killer feature” that pushes upgrades. For SQL 2019 – if you run on Standard Edition – I believe this is that killer feature.

The other thing it means is that if you haven’t worked with TDE before, you’re going to want to get knowledgeable about it. It’s very easy to set up, but you’re going to want to understand the potential pitfalls, issues around managing it, how it’s going to affect performance. What exactly it’s protecting you from, and additional steps (that may not be obvious) you should take to make it more secure.

If you understand these things before you have to think about implementing TDE then you’ll be able to plan a smooth implementation – and be able to advise management from a strong knowledge base.

I tried to cover all these topics on this blog previously, so here’s some links. I also have some new stuff coming up about TDE.

What is TDE?
Setting up TDE
Encrypting an Existing Database with TDE
Understanding Keys and Certificates with TDE
How Secure is TDE – And how to Prevent Hacking
Thoughts on Query Performance with TDE Enabled
Migrating or Recovering a TDE Protected Database
Recovering a TDE Protected Database Without the Certificate
TDE – Regenerating the Database Encryption Key
Rotating TDE Certificates Without Having to Re-Encrypt the Data

Thoughts on Query Performance with TDE enabled

Microsoft state that enabling TDE (Transparent Data Encryption) 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?

The critical thing to remember is that with TDE your data is encrypted on disk, but data held in memory (i.e. the buffer pool) is unencrypted. Therefore, you would only expect an overhead when reading from and writing to disk.

SQL Server tries to keep data that is referenced repeatedly in 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, 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 logfile 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 backups, or 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.

Let’s look at some examples and try and quantify what amount of overhead we might be talking about. In this post we’re just going to focus on the effect TDE has when you are reading data.

First, I’ll create two databases, one with TDE enabled and one without. Then I’ll load the same set of data into each (Total size about 1GB).

You can find the script I used for this in my previous blog post:

Encrypting an existing database with TDE

In the first test we’ll perform a like query of the worst kind, one that tries to match for a value within a column. We have no indexes on the table, but none would be that helpful with this query anyway.

SELECT *
FROM dbo.SomeData 
WHERE SomeText LIKE '%Wibble%';

I’ll run across 4 test cases (capturing the total CPU consumed in each case). The test cases are:

  • TDE Protected database where the buffer cache is empty (i.e. all data has to be read from disk)
  • TDE protected database where all the data for the table is in the buffer cache (i.e. no data has to be read from disk)
  • Database without TDE where the buffer cache is empty (i.e. all data has to be read from disk)
  • Database without TDE where all the data for the table is in the buffer cache (i.e. no data has to be read from disk)

In each test I’ll run the query 5 times and total the CPU to even out variance between executions. For the tests involving disk reads I’ll run the command DBCC DROPCLEANBUFFERS in between executions of the query to empty the buffer cache.

The results looked like this, with time shown in seconds. Note that MAXDOP was set to 4 and each query went parallel over 4 threads:

TDE_ReadPerf1

There’s quite a variance between each run so I’m not going to take anything significant from small differences we see. However, we can see that the timings are pretty much the same when the data is in memory, but there seems to be about a 10% overhead with TDE when reading from disk.

In case you’re wondering why reading from disk didn’t add much elapsed time for the No-TDE database – the reads were “read-ahead” so were able to complete while the CPU was burning through the data.

Let’s try a different query, this one will still have to scan the whole table as we are dealing with a heap, but it uses an equality predicate so there is less work to do in matching the data:

SELECT *
FROM dbo.SomeData 
WHERE Id = 100000000;

I’ll run the same set of tests as above and we can look at the results:

TDE_ReadPerf2

The first thing we notice is that this query runs a lot quicker in general. And again, there is little difference with and without TDE when the data is in memory.

But look at the comparison when the data has to be read from disk. With TDE the CPU consumption is more than 10 times as large, or in percentages, over 1000% worse.

At this point you could be forgiven for panicking – are you willing to risk that TDE makes your queries that much worse.

In both the above two tests, the same amount of data is being read from disk. And if you re-examine the numbers, you’ll see that (very roughly) the same amount of CPU has been added in each case where we have TDE enabled – about 50 seconds. That 50 seconds was split over 4 cores so it would have been about 12.5 seconds per core.

In terms of elapsed time, we had approximately that increase with the first query because CPU was the resource under most contention – i.e. the reads were able to occur while waiting for the CPU to complete. In the second query we can see the reading from disk occupied most of the elapsed time for those queries, so the extra CPU consumption didn’t make the query run particularly longer.

By the time it had been executed 5 times (with the memory flushed between each execution) each query read about 600,000 pages sized at 8kb each – just under 5GB. If it took 50 seconds on the decryption of those pages, then each page took about 1 twelfth of a milli-second to decrypt – or alternatively, TDE decrypted about 12 pages per millisecond. Or in terms of disk size, 100MB per second. These were tests on a server with magnetic spinning disks (not SSDs) and you can see from the above figures, the straight disk access took about 40 seconds on its own.

When TDE doesn’t read from disk it doesn’t add any overhead, but how do we quantify what the overhead to queries is when it does have to access the disk?

From the above tests we could suggest it adds from 10% to over 1000% CPU.

Or alternatively between 10% to 0% elapsed time. Note that those figures are the same way round, i.e. when it added 10% CPU it added 10% elapsed time, but when it added 1000% CPU time – the elapsed time was about the same.

I could go on with this type of confusing analysis, but instead I’ll suggest this is the wrong way to think about performance in terms of TDE.

Don’t think about query performance.

Think about read performance.

TDE overhead depends on the level of your physical disk access. In the case of read query performance, it depends wholly on the level of physical reads, and seems to be a reasonable fixed overhead for each physical read.

That makes perfect sense, the overhead for querying is in decrypting data, and surely it will take pretty much the same amount of CPU to decrypt each 8KB page.

In theory this makes it simple for us to calculate what this overhead would look like on our production SQL Servers. There’s all sorts of ways of capturing physical reads (and writes).

Let’s say I take a quick look at Resource Monitor to get a ballpark figure for one of my databases on this server that I know to be quite heavy on physical reads. And let’s say I see that it averages 25MB/s during the peak hour.

From that, and from the figures above I can estimate what impact enabling TDE for this database would have on the CPU.

Well I know that 25MB equates to about 0.25 seconds of CPU to decrypt the data, and I know I have 4 cores, so I can expect that in the average second this adds 0.0625 seconds of CPU per core. I multiple that be 100 and I find that I’ve added 6.25% CPU.

The calculation I’ve just done is:

(Reads/Second) * 100

Divided by

(MBs TDE decrypts every second/CPU) * (Number of CPU cores)

This doesn’t include writes, and it doesn’t include backups – I hope to look at that in a later post.

Now, let’s say that produces a scary number, and I’m worried about the strain that’s going to put on my CPU…

My first question would be why am I experiencing so many reads and can I alleviate that? Does data have a short shelf-life in memory? Do I have enough memory in my server – and is enough allocated to SQL?

This isn’t just in terms of TDE. SQL Server is going to perform much better if your current dataset – i.e. the data you are currently accessing most, can be held in memory. So, if TDE is causing a problem, then it’s possible your queries are slow anyway.

Again, I’m not talking about writes just yet.

Or maybe your database is heavy on physical reads because it’s a data warehouse, regularly querying historical data. In that case, is it a suitable target for encryption? Hopefully the data is well anonymised if you’re using it for reporting and therefore doesn’t contain anything personal or sensitive.

I hope.

In summary…

Just to repeat myself, if you’re wondering about TDE and its impact of query performance, which we all have done, try to reframe the question and think about its impact on read performance.

It makes more sense and it may help you to more easily quantify the impact on your servers. And if it does look like the performance may be an issue – perhaps there is tuning you can perform on your database instance to reduce the physical disk access.

RDS encryption vs TDE

If you’re starting to use cloud services, the number of options available can be confusing. Particularly when they may seem to do the same thing.

If you’re on AWS, and you want to create a SQL Server instance on RDS (Relational Database Service), then you potentially have a couple of different options for enabling encryption at rest.

If you’re deploying an Enterprise Edition SQL Server instance then you could use TDE (Transparent Database Encryption), the technology most of us in the SQL Server world already have some awareness of.

RDS also has its own at-rest encryption though, so what’s the difference?

The answer is that (at least in terms of what they protect) they are pretty much equivalent. RDS encryption can be used with whichever database platform you choose to use – Aurora, MySQL, MariaDB, PostgreSQL, Oracle or SQL Server. As many people want this functionality it made sense for Amazon to provide it.

Only Oracle and SQL Server have their own built-in equivalent – TDE – so in these cases you have a choice of which want you want to use. You may prefer to use the one you are familiar with from your on-premise deployments, or you may prefer to go with the RDS one.

A key difference is going to be database backups. RDS handles backups for you, and of course these backups will be encrypted whichever option you choose. However, you would be wise to also take your own set of backups which you store outside the RDS instance. With RDS encryption these backups would not be encrypted, however with TDE they would be.

Even this though is not a killer point, with SQL Server (from 2014 onward) you have backup encryption, so even if you were using RDS, you could use this to make sure that externally stored backups were also encrypted.

A big reason you might want to use the RS encryption is price. TDE is only available on SQL Server Enterprise Edition, whereas you can use RDS encryption on Standard Edition also.

UPDATE (03/12/2019): From SQL Server 2019 TDE is available in standard edition, so price is no longer a factor.

The difference in cost for SQL Server Standard vs Enterprise licences is significant. Last time I checked the standard price was around $2,000 dollars per CPU core for Standard, but $7,000 with Enterprise.

The price difference in RDS is also large. If I look at a “db.m4.xlarge” instance which is 4 virtual CPUs and 16GB RAM, then the price quoted is roughly  $750 dollars a month for SQL Server Standard Edition, $1,650 for Enterprise.

Of course, there are differences between each technology in terms of how you set it up and manage it, how and where the keys are stored etc. But if you’re confronted with the choice, then mostly, you can just pick the one you prefer, it doesn’t really matter.

What does matter is that if you prefer not to pay for Enterprise Edition, then you still have the option of at-rest encryption. Which is great news.

TDE – Regenerating the Database Encryption Key

As discussed in the last post, with TDE (Transparent Data Encryption), you generally do not need to change the Database Encryption Key (DEK) as it is separately protected by a certificate in the master database and you can rotate the certificate periodically.

Rotating TDE Certificates without re-encrypting data

As also mentioned, if you did need to change the DEK then this will be a resource intensive operation as SQL Server will need to decrypt all your existing data using the existing DEK value, then re-encrypt it with the new one.

Nonetheless, there may be scenarios when you wish to do this. There could be a security breach where you don’t feel confident unless all the keys are updated. Or you might need to support a higher level of encryption than had been previously used.

More commonly, you may publish a database from a development environment and wish to regenerate the keys so that there are unique to your production environment.

In either case, the process is simple and transparent from your point of view, you issue the ALTER DATABASE ENCRYPTION KEY command, specifying REGENERATE and the encryption algorithm you wish to use:

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;

In the previous post I talked about rotating the certificate, and it’s worth noting that you can perform both these actions at the same time e.g.

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE
MyTDECert_with_longevity

Where you have a lot of data in the database this could take a while to complete. You can check the status of the operation by looking in the dm_database_encryption_keys view:

SELECT k.encryption_state, k.create_date, k.regenerate_date,k.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’

TDE_Regenerate1

Note that the encryption_state is set to 4 which means “key change in progress”.

In a brief test I compared the time taken to change the key with the time taken to encrypt a fresh un-encrypted database. Surprisingly it was about the same, I had expected it would be longer due to the need to decrypt and re-encrypt each data item.

Rotating TDE Certificates without re-encrypting data

I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent Data Encryption).

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

At the time I mused about why we need the encryption key in the database itself, rather than just encrypting with the keys associated with the certificate stored in the master database. I mentioned one reason – that these keys are intended for asymmetric encryption which has a higher overhead (the DEK is a symmetric key).

In a light-bulb moment, the more important reason has come to me since, and that is about key rotation.

In any form of encryption, it is possible that at some point an individual could gain access to the keys. For example, administrators of your SQL Server instance can freely make a backup of the certificate with a password they choose which can be used to encrypt it. Whilst that in itself is not a problem, it is good practice to periodically refresh those keys so that if someone gained access to an old copy it would be of no use to them. Or if you are aware that there has been a potential breach of security, you can quickly replace your keys.

If you have any form of security certification in your organisation, then it is likely it will be stipulated that you must change these in a defined timescale-  e.g. annually.

However, this forms a problem if you want to change the key that is used to actually encrypt the data, to change that you would need to decrypt all the data using the old key and re-encrypt it using the old one. That would be an intensive activity, but also could lead to vulnerability while the process was underway.

As a result, many forms of encryption use separation between the actual key that is used for the encryption, and another object that is used to protect that key. You can back up the latter separately outside the database to ensure you can recover your data in case of a failure.

With TDE we have the database encryption key (DEK) – the key that is actually used to encrypt/decrypt the data, stored encrypted in the database itself. In the normal run of things, we never access this key directly and never store it anywhere else, so it should be fairly safe, i.e. we should rarely have to worry about replacing it – though the functionality exists to allow us to do so if we require.

Then we have the certificate which is used to encrypt that key – without the certificate the key is useless and TDE can’t work. The certificate we keep a password protected backup of, so is a bit more vulnerable/public.

Due to that, 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.

If you think about it, we might not like it if TDE did enforce this, it would be a big impact if we suddenly could no longer read or write to our database. Of course, MS could probably implement functionality to lock us out, but still allow us to use the certificate for rotating the key, but still it would probably upset a lot of people and be less than “transparent”.

If we look at the sys.certificates view we can find our TDE certificate and examine the expiry date:
SELECT name, subject, expiry_date
FROM
sys.certificates
WHERE name = ‘MyTDECert’;

TDE_Rotate1

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 rotate the encryption to use that one.
CREATE CERTIFICATE MyTDECert_with_longevity
WITH SUBJECT = ‘Certificate used for TDE in the TestTDE database for years to come’,
EXPIRY_DATE = ‘20201231’;


And let’s have a look at the values that has in the store:

TDE_Rotate2

What do we mean by rotation?
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 additional work.

To rotate the certificate used is as simple as:
USE TestTDE;

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE
MyTDECert_with_longevity;

And you’re done. 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!

Recovering a TDE protected database without the Certificate

If you’ve been careful and done everything right when you’re setting up TDE then you shouldn’t run into this problem.

We all make mistakes though, and we’ve all been asked to deal with environments that haven’t been so carefully managed.

But what if you do? You have access to the backups for one or more TDE protected databases, but you don’t have the certificate and private key backups – or you don’t have the password to decrypt them.

I’m assuming here that you also can’t simply recover your old server from a complete file system backup. Obviously if you can do that then you are going to be fine.

If the two following things are true, 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 is until we get right to the top, the Service Master Key (SMK) which exists in the master database is itself encrypted. There are two copies of it:

  • One encrypted by the machine account
  • Once encrypted by 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) direct 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

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 simple 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 [LINK] for recovering your TDE protected database(s).

  • 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!

That’s fairly straightforward, but let’s just go into 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 one’s 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 out there that that tell you how to do this in detail such as Thomas LaRock’s post here:

https://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/

In short you need to first stop your new SQL Server instance and then from a command prompt start it in single user mode e.g.

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:

C:\> sqlcmd -s {InstanceName}

Then at the prompt that opens up within your command window:

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

2> GO

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

If you restart SQL before doing this, 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:

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'  
WITH PRIVATE KEY   
(  
    FILE = 'C:\Test\MyTDECert_PrivateKeyFile',  
    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).

Not losing these backups – or the password – is a serious issue. 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.

Other articles about TDE:

What is Transparent Data Encryption?

Setting up Transparent Data Encryption (TDE)

Encrypting an existing database with TDE

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

Migrating or Recovering a TDE protected Database

TDE and backup compression – still not working?

TDE and backup compression – still not working?

Until SQL 2016 if you used TDE (Transparent Data Encryption) you couldn’t use backup compression.

In 2016 Microsoft changed this, but it has been a rocky road. Backups work okay but in some circumstances people have found that they are corrupt when they come to restore them.

We thought that was all fixed, as highlighted by Brent Ozar, MS have been recommending that you are on at least SQL Server 2016 RTM CU6 or 2016 SP1 CU4 where these issues are resolved.

https://www.brentozar.com/archive/2017/09/breaking-news-using-tde-2016-backups-time-patch/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?forum=sqldatabaseengine

It seems like people are still having problems though. Ken Johnson is on 2106 SP1 CU6 and is having problems:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dad3dfe3-c914-44f8-96b9-ab56cc825fe9/tde-corrupt-backups-when-using-backup-database-with-compression-maxtransfersize?forum=sqldatabaseengine

Our production DBA has just run a test and confirmed that in one test backing up 20 databases from one server and restoring them to another, about 10 have failed. Although in other environments this is working fine.

You can see the error if you try to verify the backup:

Date and time: 2018-02-16 09:52:10
Command: BACKUP DATABASE [XXXXX] TO DISK = XXXXX’ WITH CHECKSUM, COMPRESSION, MAXTRANSFERSIZE = 131072
Processed 95488 pages for database ‘XXXXX’, file ‘XXXXX’ on file 1.
Processed 3 pages for database ‘XXXXX’, file ‘XXXXX’ on file 1.
BACKUP DATABASE successfully processed 95491 pages in 2.375 seconds (314.113 MB/sec).
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2018-02-16 09:52:12

Date and time: 2018-02-16 09:52:12
Command: RESTORE VERIFYONLY FROM DISK = ‘XXXXX’ WITH CHECKSUM
Msg 3189, Level 16, State 1, Line 1
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Outcome: Failed
Duration: 00:00:03
Date and time: 2018-02-16 09:52:15

And it certainly doesn’t then work if you try to restore it:

5 percent processed.
Msg 3183, Level 16, State 1, Line 2
RESTORE detected an error on page (1:7763) in database “XXXXXXX as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

We’re raising a call with Microsoft, will update when we know more.

In the meantime, can we trust backups of TDE with backup compression enabled? Given that we want to be absolutely sure our backups can save us in event of disaster the answer may be no. At a minimum we need to test each time we update our applications or patch SQL Server.

The side lesson if course is that we should always be regularly checking our backups are restorable.

More articles on TDE

What is Transparent Data Encryption?

Setting up Transparent Data Encryption (TDE)

Encrypting an existing database with TDE

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

Migrating or Recovering a TDE protected Database

 

Migrating or Recovering a TDE protected Database

When encrypting a database with Transparent Data Encryption(TDE), a vital consideration is to make sure we are prepared for the scenario when 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 Setting up Transparent Data Encryption (TDE) then you will also have a backup of the certificate and private key used to protect the database encryption key used by TDE e.g:

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

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 unauthorised users can get it otherwise you are defeating the object of TDE somewhat.

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 if one doesn’t exist

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

Note that this will be a new and unique database master key, 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:

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 database master key 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.

Restore the Database

Once you’ve completed the previous steps you are ready to restore the database(s) from the backup(s). You do that as you would restore any other database. Potentially as simple as:

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

So what do I do if I can’t restore the certificate?

Of course you’re never going to run into this problem because you’ve followed all the instructions carefully, and you’ve made sure you have your certificate and key backups – and the password used to protect them.

Let’s say for the sake of argument though that you’ve taken ownership of an environment that hasn’t been so carefully managed. You’ve had a server failure and there are no certificate and key backups – or they exist but no-one knows the password.

Is your data lost forever? Or rather is it now so safe that no-one can access it – even those who are supposed to? Don’t panic just yet, we’ll look at a technique you may be able to use to recover your data in my next blog post.

Other articles on TDE:

What is Transparent Data Encryption?

Setting up Transparent Data Encryption (TDE)

Encrypting an existing database with TDE

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

 

Encrypting an existing database with TDE

As mentioned previously, the process of setting up TDE (Setting up Transparent Data Encryption (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 begin the process of encrypting any data in your database. Be that 1 row, or be that terabytes of data.

I’m going to load up my database with about 1 GB of data so we can get an idea of how long this process takes.

CREATE DATABASE TestTDE;
USE TestTDE;
CREATE TABLE dbo.SomeData(Id INT IDENTITY(1,1), SomeText VARCHAR(255));

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 10

I then go through all the steps mentioned previously to set up encryption:

Setting up Transparent Data Encryption (TDE)

After the last step:

ALTER DATABASE TestTDE SET ENCRYPTION ON;

I run a 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:01';

   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;

I’m sampling every second, let’s look at the results:

TDE_Progress

You can see that the encryption took about 30 seconds to complete for (just under) 1GB of data. This isn’t intended to be a formal benchmark, but rather just to give you an idea of the order of magnitude of time this might take to encrypt your own databases.

Let’s say – just for the same of argument – that this scaled up linearly on a given system and you wanted to encrypt a 1TB database. Then it might take as long as 500 minutes (8 hours and 20 minutes).

The encryption occurs as a background process, but it will take some resources while it runs, so if you are implementing this against a system with large databases where performance is critical then you will want to either run it in a period of quiet – or down time, or you will want to monitor to check that encryption isn’t impacting your system too much. Experience suggests that it shouldn’t be a problem unless your server is already under strain.

There are a few things to look out for if you are monitoring during the background encryption process:

  • CPU and IO, both these could take a hit
  • You may want to look out for blocking caused by encryption, you can do this by checking the sys.dm_tran_locks view where the resource_subtype is “ENCRYPTION_SCAN”
  • Monitor transaction log usage with DBCC LOGINFO. While the encryption scanner is running the transaction log can’t be truncated and VLFs marked for re-use. This could mean the transaction log might grow larger than normal, so you need to watch out if you are constrained for disk space.

What if you run into any performance problems during the scan?

First things first – DON’T TURN ENCRYPTION OFF!

If you turn encryption off, i.e.

ALTER DATABASE TestTDE SET ENCRYPTION OFF;

This isn’t going to stop the encryption scan, rather it’s just going to change direction. So now it will be decrypting everything it’s just encrypted, that’s likely to have just as much impact, and then sooner or you’re going to have to start again.

There is no “ALTER DATABASE TestTDE SET ENCRYPTION PAUSE;” command. There is however a trace flag (5004) that achieves the same thing.

If you enable the trace flag it will disable the encryption scanner:

DBCC TRACEON(5004);

When you do so, if you then query dm_database_encryption_keys you will see the database is set to a state of 2 (Encryption in progress) and the percent_complete column will show zero – as nothing is actually currently in progress.

When you wish to begin the scan again, you need to disable the traceflag and then set encryption on again (even though it’s not actually off):

DBCC TRACEOFF(5004,-1);
ALTER DATABASE TestTDE SET ENCRYPTION ON;

When you do this the scanner will pick up where it left off, i.e. if it had got to 50%, it will then continue from there.

It’s important to note that the traceflag doesn’t actually turn TDE off. In fact if you add new data, or update data and it get’s written to disk while the traceflag is enabled, the data will still become encrypted. It just stops the background process of converting all the data already on disk.

If performance impact is an issue for you with the scanning process, you can use the traceflag to incrementally run the scan out of hours, pausing it when you want to ensure maximum performance of your systems.

The traceflag seems to exist for internal use by SQL if the encryption scanner runs into a problem, for instance if it encounters corruption in your database files then it will halt and the traceflag will be enabled.

This could be useful to know if you find you tried to turn TDE on for a database but it’s stuck at “Encryption in Progress” but the percent_complete remains set to Zero. Check to see if the trace flag is enabled, and if you didn’t enable it then you may want to check for corruption in your database.

More Articles on TDE

What is Transparent Data Encryption?

Setting up Transparent Data Encryption (TDE)

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking

Setting up Transparent Data Encryption (TDE)

You can set up Transparent Data Encryption (TDE) when you first create a database, or you can apply it to an existing database. In the latter case, once TDE has been enabled it will set to work encrypting your existing data in the background.

In either case the steps are the same. We’ll run through those quickly before going into more detail.

First you must have a Database Master Key (DMK) in the Master database, and a certificate that will be used by TDE:

USE MASTER;

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHere!£$7';
CREATE CERTIFICATE MyTDECert 
WITH SUBJECT = 'Certificate used for TDE in the TestTDE database';

This certificate is critical to you being able to access data encrypted by TDE, so you should make sure you back it up:

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

Then, in the database being encrypted with TDE you must create a Database Encryption Key (DEK) and specify the certificate:

USE TestTDE;

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

Finally, you turn encryption on:

ALTER DATABASE TestTDE SET ENCRYPTION ON;

And that’s all there is to it in practice.

A potential problem is that it is easy to set this up without really understanding it. Maybe that’s fine in many cases, but can you be sure that there’s nothing that can go wrong and have confidence that whatever the scenario, you will be able to get your data back? And can you be sure that your data is properly protected.

To gain that level of surety and to have confidence, I think it’s best to understand this in a bit more detail. In particular, I think it’s good to understand why each step is required and how the objects created are used.

So let’s go through those steps again in more detail.

Creating the Database Master Key (DMK)

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

A DMK is used to protect other keys that are created in the database. It does this by encrypting them and only the encrypted value is stored. You can only have one DMK in a database.

The DMK itself is also stored encrypted, you can see when we created it we specified a password to encrypt it by. SQL Server also makes separate copy of the key encrypted by the Service Master Key (SMK). The SMK is the root level Key in SQL Server. This additional copy of the DMK means that SQL can access the actual value of your DMK without you having to specify the password again.

Creating the Certificate

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

The certificate is going to be used in the next step down – to protect the Database Encryption Key (DEK) in your TDE enabled database. When you create a certificate, it contains an asymmetric key that can be used for encryption. An asymmetric key includes a public key that can be used to encrypt data and a private key that must be used if you want to decrypt data – that private key gets automatically protected (encrypted) by the DMK.

A logical question to ask is why we need the certificate? Why couldn’t we just protect the DEK in our TDE enabled database with the DMK from the master database directly?

Imagine the scenario that you need to migrate your database to another SQL Server instance. We can do this but we will need also to migrate the object that was used to protect/encrypt the DEK – which is itself stored in the database.

If TDE used the DMK to protect that then we would need to migrate the DMK to the new instance. But what if the new instance already had a DMK in the master database and objects that it was used to protect – such as other databases using TDE. At this point we would be stuck, we can’t migrate our DMK without overwriting the one that’s there, so we would have a choice, enable encryption for the migrated database but break it fore the existing ones, or vice versa.

Neither is a good option, but by having a certificate we can migrate that happily as we can have as many certificates as we want.

This also gives us the option that where we have multiple databases encrypted by TDE we could use a separate certificate for each. That means if one certificate is breached the others could remain protected.

This does raise a good point though that you may want one day to migrate your certificate, so call it something more meaningful and unique than “MyTDECert”.

Backing up the certificate

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

We backup the certificate in case we ever need to move or restore our database to a different server or SQL instance. It’s critical if we want to be able to access our encrypted data.

When you back it up, you specify a password to encrypt the key. What happens is SQL grabs the stored version of the Private Key (which is encrypted by the DMK) decrypts it, then re-encrypts it with the password. This means that you would be able to restore it to a different SQL instance where the DMK didn’t exist.

This covers us against the scenarios explained above regarding why we use a certificate rather than just relying on the DMK. It should also make it clear that if we need to migrate or recover the database all we need is:

  • The database backup
  • The certificate and key backups and the password used when creating them

Creating the Database Encryption Key (DEK)

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

In this, almost the final step, we create the actual key that will be used to encrypt the data. It’s stored in the database, but encrypted by the key associated with the certificate created in the previous step. The DEK is a symmetric key, i.e. the same value is used to encrypt or decrypt data.

It’s logical to ask why we don’t just use the private key from the certificate to encrypt the data directly. This is a bit more difficult to justify than the previous scenario with the DMK versus use of a certificate. It is certainly feasible that TDE could have been implemented this way.

One consideration is that the certificate is created with an asymmetric key – these are easier to work with in some ways as we only need the public key to encrypt data so can keep the private key concealed most of the time. Asymmetric encryption however is slower that symmetric encryption so to reduce the performance impact of TDE we want to use a symmetric key.

The concept of a DEK was new in SQL 2008 and created specifically for TDE. It makes sense that if we are to have a separate DEK then it should be stored in the database itself. That way migration/recovery is eased as it minimises the number of objects required.

It’s worth noting that you can only have one DEK in each database.

Enabling Encryption

ALTER DATABASE TestTDE SET ENCRYPTION ON;

In sys.databases you can see which databases have TDE turned on by looking at the is_encrypted column:

SELECT name
FROM sys.databases
WHERE is_encrypted = 1;

TDE sys_databases

We can find more details in the sys.dm_database_encryption_keys server view. Let’s query looking at some particular 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;

Here’s what I see after I created my DEK but before I enable encryption:

TDE_State1

We can see information about the DEK. We also see encryption state which describes the current state of the database. The main values you’ll see are:
1 = Unencrypted
2 = Encryption in Progress
3 = Encrypted

If I now enable encryption on this database and run the query again:

TDE_State2

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 a state change is occurring. So, if the encryption state was 2 – then we would see a value here whilst the database was in the process of being encrypted. Here my database only had one row, so it was fairly 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 next:

Encrypting an existing database with TDE

 

More articles about TDE

What is Transparent Data Encryption?

Understanding Keys and Certificates with Transparent Data Encryption (TDE)

How Secure is Transparent Data Encryption (TDE) – and How to Prevent Hacking