Setting Up Always Encrypted

June 6, 2023June 6, 2023 ~ Matthew McGiffen ~ 1 Comment

In this post we’re going to go through the steps to set up Always Encrypted and create an encrypted column. As with my last post we’re looking at the flavour of Always Encrypted without enclaves, we’ll look at working with enclaves in detail later on.

It is a straightforward process to set up everything required for Always Encrypted. In fact, there is a wizard provided in SQL Server Management Studio (SSMS) that will do it all for you. In these examples, however, we will focus on performing the individual steps manually as that gives you a better view of what is going on. For all the objects involved we’ll look in detail at what is created so that you have a good level of understanding.

Before starting it is best to make sure that the version of SSMS you are using is up to date so that it has the full support for Always Encrypted. Anything above version 18 is fine.

Create Keys and Certificates

Before we start, we need a database we’re going to work in. We’ll call it TestAlwaysEncrypted – you can create it with the following SQL:

CREATE DATABASE TestAlwaysEncrypted;

Creating the Certificate and Column Master Key

First, we’ll create the Column Master Key (CMK). We have a few options for a CMK; in this post we’ll focus on the option of using a certificate stored on the local machine which contains an asymmetric key. You can also use an asymmetric key stored in an external key store such as Azure Key Vault; we’ll look at that option in a later post. When you create the CMK through SSMS, it also creates a CMK object in your database that identifies the location and identity of the actual key.

If you expand your database in the SSMS Object Explorer, you will find Always Encrypted Keys under the Security folder for the database. Right-click over Column Master Keys and select to create a new one, as shown in below:

This is the GUI that comes up:

At the top you can see I’ve given my new CMK the name TestCMK. In the box below we see a list of certificates in the selected key store available for use by Always Encrypted. I created the highlighted one simply by clicking the Generate Certificate button at the bottom. The Key Store for the certificate to be created in defaults to the Current User store. It is worth noting that when you generate a certificate, it is created on the local machine you are running SSMS on – not the SQL Server you are connected to – unless you are running SSMS on the server itself. Your account will need permissions to create certificates in the selected certificate store, or the generate certificate button will be grayed out – in that case you may have to run SSMS as an administrator to resolve the issue.

The decision of whether to create the certificate in the Local Machine or Current User store becomes more relevant once you are deploying the certificate to environments other than your local development environment. In those cases, I prefer to deploy to the Current User store for the account the application runs under – or if deploying to Local Machine, you can set the access for the certificate so only the application user account can access it. This minimizes the set of accounts that can access the certificate.

Click OK and the new CMK will be created. We can then see it listed under the Column Master Keys folder in SSMS, as shown here:

To understand what this key actually is, I’m going to right-click and generate the SQL definition. This also shows you the code if you want to create the SQL Server CMK object through T-SQL:

CREATE COLUMN MASTER KEY [TestCMK] WITH ( KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE', KEY_PATH = N'CurrentUser/My/CE751A6A9CB3732508D6A7E8368E5B3770CF7328' );

We only have two values, KEY_STORE_PROVIDER_NAME and KEY_PATH. All this is telling us is where to find the certificate we just created; KEY_PATH shows the path in the certificate store including the certificate’s thumbprint. If you are following along, your certificate will have its own unique thumbprint. You can see that the Column Master Key object stored in your database is just a pointer to the actual CMK, in this case a certificate stored on client machines.

If you want to create your certificate without using SSMS, you can do so with PowerShell. Here is an example:

$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In practice, when deploying your applications, you are likely to create the certificate ahead of time and deploy it as part of a deployment package. At a minimum you are also likely to want to use different certificates in your dev/test vs. production environments.

Creating the Column Encryption Key (CEK)

This is the last thing we need before we can set up encryption on our columns. The CEK is a symmetric key that will be used to encrypt our actual data and will be stored in the database encrypted by the CMK. You can find the Column Encryption Keys folder underneath Column Master Keys in SSMS which you saw earlier. Again, right-click to create a new key and bring up the GUI:

You can see I’ve called mine TestCEK, and in the dropdown, I’ve selected the CMK that I just created. Click OK and the CEK is created. We can see it in the object browser:

As with the CMK we’ll script the key out through SSMS so you can see what is created and the code involved:

CREATE COLUMN ENCRYPTION KEY [TestCEK] WITH VALUES ( COLUMN_MASTER_KEY = [TestCMK], ALGORITHM = 'RSA_OAEP', ENCRYPTED_VALUE = 0x016E000001630075007... );

We can see it tells us what CMK this is based on, what Algorithm has been used to encrypt the CEK, and a long binary Encrypted Value (which I’ve truncated here to make things more readable). The Encrypted Value is the actual key used to encrypt data, itself encrypted by the CMK. The Encrypted Value here is unique to my system. If you create your own CEK, you will have a different value.

The unencrypted value of the CEK never exists on the SQL Server, so both the CMK and CEK are required to interact with encrypted data. This is what makes Always Encrypted so secure – even if you have admin rights on the SQL instance, you don’t have access to the unencrypted value of the CEK, so you can’t read data unless you have access to the CMK too.

You can deploy a pre-created CEK using the preceding code, but you cannot generate a fresh CEK using T-SQL. part of the reason for that is that SQL Server cannot access the CMK which is required to encrypt the CEK. you have to use the GUI in SSMS, or if you wish to do it through code, then there are methods available with PpowerShell.

Create an Encrypted Column

We’re going to look here at how you define a column as encrypted when you create a new table. In later posts we will look at methods for encrypting existing data.

Always Encrypted can work with most types of data, but there are some restrictions on which data types you can encrypt – we’ll look at that later too. In general, you can encrypt both numbers and strings, but you are limited in the extra functionality you can have against encrypted columns, such as constraints, and you wouldn’t be able to encrypt columns with properties like IDENTITY. A specific restriction that it is good to be aware of is that string-based columns must use a BIN2 collation type. You’ll see what I mean by that when we create the table.

With Always Encrypted you can also use one of two types of encryption, randomized or deterministic. The difference is that with randomized encryption, where you encrypt the same value multiple times (where the value exists in multiple rows within your data), the encrypted values will all be different. With deterministic, the same unencrypted value will always end up with the same encrypted value. Randomized encryption is more secure and you should use it where you can, but it does limit your functionality. For instance, you can’t have an index on a column with randomized encryption; if you think about it, then that makes sense; if all the underlying values that are the same are stored as different values in the table, then how would it be possible to index them. If you want to index an encrypted column, then it must use deterministic encryption.

Another key restriction is that you cannot query with a WHERE clause against a column with randomized encryption. Let’s understand that. Say I have an encrypted text column using randomized encryption and want to find all rows that match a particular value. I have to try doing that by encrypting the value I am searching for and matching that against the encrypted values in the table, but because we are using randomized encryption, my encrypted search value will be different to all the encrypted values in the table even when the unencrypted value might be the same, so no match would be possible. In such scenarios SQL Server will return an error to tell you the operation is not allowed so you don’t attempt to execute queries where the result would be incorrect.

Deterministic encryption however is slightly less secure. Imagine that you are encrypting a text column which has a limited number of possible values; it may be possible to use frequency analysis to analyze the number of occurrences of each encrypted value and thereby match/guess what real value each corresponds to.

Let’s go ahead and create a table with two encrypted text columns. For the sake of example, we’ll use deterministic encryption for one and randomized for the other. The following SQL creates the table:

USE TestAlwaysEncrypted; CREATE TABLE dbo.EncryptedTable( Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED, LastName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = TestCEK, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, FirstName nvarchar(50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = TestCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL );

We execute our SQL statement, and the table is created. You can see that what we are doing is specifying the details of encryption for each column we want to encrypt. We specify which CEK to use, which type of encryption (deterministic or randomized), and finally the algorithm used to encrypt the data in the column – though the algorithm specified is the only option at the current time.

Let’s just discuss the requirement for a BIN2 collation in more detail as you might be wondering why this is needed. A collation specifies the way data is compared and sorted. One feature of BIN2 is that it is a case-sensitive collation. That means, for instance, that if you search for a string like “Matthew,” it won’t match with “MATTHEW” or “matthew.” If you think about it, then it makes sense that you can’t have case-insensitive comparisons on an encrypted column. When you perform a comparison based on encrypted data, what the engine is doing is comparing one encrypted value with another. To enable a case-insensitive comparison there would have to be some deterministic pattern so that you can tell that two different encrypted values differ only by case. That would be more complicated to implement, would weaken the encryption, and isn’t supported by the encryption algorithm used by Always Encrypted. The requirement to use a BIN2 collation is driven by what will happen in practice when you compare two encrypted values looking for an exact match and also how such data is sorted. That creates some limitations on things like searching against encrypted columns – we’ll talk more about that later.

In terms of setup, that’s all you need to do, so you can see Always Encrypted is quite simple to implement.

Summary

Setting up Always Encrypted is straightforward. The key points to understand are:

You need a certificate and key pair which sit on the client machine or application server; this is the Column Master Key (CMK). Alternatively you can use an asymmetric key in an external key store.
In the database we have a CMK object, but this is just a pointer that tells us where the actual CMK can be found.
The Column Encryption Key (CEK) is stored in the database but is stored encrypted by the CMK. This key is what is used to encrypt or decrypt data.
To access data, you need access both to the CEK and the CMK.

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

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

What is Always Encrypted and how does it work?

June 5, 2023June 5, 2023 ~ Matthew McGiffen ~ 1 Comment

Always Encrypted was a new encryption feature added to SQL Server with the 2016 version of the product. Initially it was just available in enterprise edition, but from SQL Server 2016, SP1 was made available in standard edition also.

Unlike TDE which encrypts the whole database, Always Encrypted is a form of column encryption that means you choose which columns of data you want to encrypt. The “Always” part of Always Encrypted refers to the fact that data is encrypted at rest, in memory, and as it is transmitted across the network. That means that it provides the highest level of protection possible for your data.

The beauty of Always Encrypted, and what makes it a great feature, is that you don’t necessarily need to make any code changes to use it. Neither your application side code nor your database side code – stored procedures/functions – needs to know which columns are encrypted or not. Encryption and decryption of data is carried out automatically for you. It does however come with limitations on how you can interact with data.

A key difference between Always Encrypted and existing forms of column encryption available in SQL Server is the encryption hierarchy used. Previous methods would rely on the encryption hierarchy within SQL Server. That would mean that a user with elevated permissions on the database server would be able to read encrypted data. Always Encrypted relies on a key stored in the database server as well as a certificate and associated asymmetric key pair that are only stored on the application server. That means you need elevated access on both servers to be able to read encrypted data.

A key concept with Always Encrypted is the idea of role separation. That refers to the idea that you should ensure there is a separation of roles between your system administrators. If different individuals (or teams) manage your application and database servers, and no individuals have admin rights on both, then no one can access encrypted data outside of the context it should be accessed. Even without role separation, the access requirements to read encrypted data make it much harder for an external attacker.

SQL Server 2016 vs. SQL Server 2019 and Beyond

In SQL Server 2019 new functionality was added to Always Encrypted that addressed many of the limitations in the previous version. To access the extra functionality however requires additional setup and management. I find it’s worth thinking about these as almost two separate versions of the same feature, and you may choose which to use depending on your requirements and circumstances. We can think of these as “Basic Always Encrypted” which is mainly what we got with SQL 2016 and “Always Encrypted with Enclaves” which was the main addition with SQL 2019.

The difference arises from the reason why we have limitations with Always Encrypted at all. A key thing to understand is the “Always” part. SQL Server never sees the unencrypted value of your data; encryption and decryption is actually carried out on the client side – on the application servers. As SQL Server is unaware of the unencrypted values of data, it is limited in the questions it can answer about your data.

With SQL 2019 the use of enclaves was added. An enclave is a secure partition within memory where protected operations can take place securely. Always Encrypted with Enclaves allows for decryption and encryption of data within a secure enclave on the SQL Server box which can therefore support a wider range of functionality.

In this post and the following ones, we’re going to first focus on basic Always Encrypted and then move onto the version with enclaves later on.

How Does Always Encrypted Work?

As mentioned, we’re going to focus on the basic version to begin with. The concepts around Always Encrypted are reasonably straightforward, but you’ll find yourself asking questions that seem tricky. Will Always Encrypted work if I try to do this or that? Why are you getting a particular error? I’ve blogged before about Always Encrypted and some of the specific questions readers ask me aren’t covered in the documentation; having a knowledge of how it all works generally enables me to answer those questions. If you understand the mechanics of encrypting and decrypting data, then any limitations make sense. As such, I’m going to spend quite a bit of time in this post explaining how it all works.

Encryption Hierarchy

SQL Server has a standard encryption hierarchy that we’ve seen when we looked at TDE and Backup Encryption. Always Encrypted doesn’t use that, mainly because the encryption activities don’t occur on the database server, as they are done on the client machine.

On the SQL Server side we have a Column Encryption Key (CEK) that is used to encrypt and decrypt data. That is stored encrypted in the database. The CEK is encrypted by the Column Master Key (CMK) which is actually a certificate and key pair that commonly sit on the client machine – usually an application server.

On SQL Server we do have a CMK object, but this is just a pointer to the location of the actual CMK.

You can have one CEK for all the encrypted columns in your database, or you could have one for each separate column – or something in between. The CMK can be shared by multiple CEKs so again you could have one or multiple CMKs.

Encryption in Practice

Let’s go through the process of how Always Encrypted works. The below image shows all the steps that occur when you issue a query.

At first glance it seems like a lot is going on, but we’ll go through each step in turn and you’ll see it is quite simple.

Issue Query
The application will have created a connection to the database server. It has to specify that the connection will use Always Encrypted. Potentially, that is the only change you need to make to your application – if you’re lucky. It depends whether you end up needing to do work to get around the natural limitations of the technology – but we’ll get to that later. Once the connection is in place, then the application simply issues the query in the normal manner.
Request Encryption Metadata
When issuing a query, any plaintext values that target encrypted columns must be passed as parameters and must be encrypted before being sent to the database. Where such a parameterized query is being executed, the client driver requests the relevant encryption metadata from SQL Server to understand what encryption activities must be carried out before the query is sent to be executed.
Return Encryption Metadata
SQL Server parses the query text and identifies if there are any columns targeted by parameters that are the subject of column encryption using Always Encrypted. This information is sent back to the client, along with the encrypted values of any Column Encryption Keys (CEKs) used and in each case the location of the Column Master Key (CMK). This encryption metadata is cached locally on the client machine so that repeated calls to get the metadata do not need to be made for the same query.
Request CMK
Using the details provided in the encryption metadata, the client driver makes calls to access the certificates and keys for the CMKs it requires. Usually these are stored in the certificate store on the application server. Where there are multiple application servers, then they need to be stored on each. You can also store your CMKs in an external store such as Azure Key Vault, we’ll cover that in a later post.
Return CMK
The CMKs are received by the client driver.
Encrypt Parameters
Any parameters that target encrypted columns need to be encrypted. The CEK that was returned by SQL Server for a given column is decrypted using the associated CMK; the parameter value can then be encrypted using the underlying key value. Only parameters will be encrypted, so if you have literal values in your query, then these will be a problem if they target encrypted columns. Update and insert queries must specify the values using parameters for them to function when targeting tables with Always Encrypted columns.
Issue Query
The query with its parameters encrypted by the last step is sent to SQL Server to be executed.
Return Encrypted Results
Where the query has results, such as with a select query, the results are returned. Where these include encrypted columns, then it is the encrypted values that get passed back. Encryption metadata is sent back alongside the result set which supplies the encrypted CEKs and CMK locations for any encrypted columns in the results.
Decrypt Data
Where we have results containing encrypted columns, these are decrypted by the client driver before being returned to the application. This is done using the encryption metadata supplied alongside the results. As with the parameters, the CEK is decrypted using the associated CMK – sometimes this has already retrieved from the certificate store, and if not then there is an additional step to retrieve the CMKs. The unencrypted value of the CEK can then be used to decrypt the data in the column or columns that it is related to.
Return Results
Finally, the results are returned to the application with values that were stored encrypted returned as the plain text version.

Summary

That can feel like a lot of steps to get to your data, but it all happens in the background and generally with little overhead. Each step is itself quite simple. It’s good to understand the overall flow so you can understand what will happen in different querying scenarios.

We’ll go over some of this again in a bit more detail when we have Always Encrypted set up and start looking at how you execute queries. Having seen the process though, we can now start to understand some of the key things about Always Encrypted:

Data only exists unencrypted after it hits the client.
SQL Server does not encrypt or decrypt data. That is all handled in the client driver. As such there is no way for SQL to know what the unencrypted values of your data are – it just sees an encrypted string.

SQL Server Backup Encryption and Compression

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

In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.

Compressing an encrypted backup is the same as compressing a regular backup; you just need to specify WITH COMPRESSION as shown in the following SQL:

BACKUP DATABASE TestBackupEncryption TO DISK = 'C:\Test\TestBackupEncryption_EncryptedAndCompressed.bak' WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert), COMPRESSION;

We can run a quick test to see how compression performs against the same functionality with an unencrypted backup taken by executing this backup command:

BACKUP DATABASE TestBackupEncryption TO DISK = 'C:\Test\TestBackupEncryption_UnencryptedAndCompressed.bak' WITH COMPRESSION;

In this image we can see both backups:

Both backups are a fairly similar size. The encrypted one seems to be very slightly bigger, and I’d say this is the pattern I usually see, not enough that we are likely to be bothered with it. Compression is usually just as effective with encrypted backups as with unencrypted.

Backup Encryption Performance

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

Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with TDE the data is already encrypted. Backup times however are unlikely to be affected significantly as the bottleneck is usually going to be the time it takes to physically write the data to disk. The CPU processing should take a fraction of that time.

We can run a quick test with our database to show how backup performs with and without encryption. If you’re running this test yourself having followed the examples in Backup Encryption in SQL Server, then make sure the previous backup files are removed before executing the below script.

BACKUP DATABASE TestBackupEncryption TO DISK = 'C:\Test\TestBackupEncryption_Unencrypted.bak';

BACKUP DATABASE TestBackupEncryption TO DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak' WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert);

Here is the output for the unencrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1. Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1. BACKUP DATABASE successfully processed 1205417 pages in 17.428 seconds (540.355 MB/sec).

And here is the output for the encrypted backup:

You can see the backup with encryption did take a bit longer, about 2 seconds, a little over a 10% increase which seems not too bad.

With backup performance, it’s also worth considering how long it takes to restore a database from a backup. Let’s take a quick look at that. We’ll drop the database, restore the unencrypted backup, then drop it again, and restore from the encrypted backup. Then we can compare the performance. We do all that with the following code:

DROP DATABASE TestBackupEncryption; GO

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

DROP DATABASE TestBackupEncryption; GO

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

Here is the output for restoring from the unencrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1. Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1. RESTORE DATABASE successfully processed 1205417 pages in 17.979 seconds (523.795 MB/sec).

And here is the output with the encrypted backup:

Processed 1205416 pages for database 'TestBackupEncryption', file 'TestBackupEncryption' on file 1. Processed 1 pages for database 'TestBackupEncryption', file 'TestBackupEncryption_log' on file 1. RESTORE DATABASE successfully processed 1205417 pages in 20.794 seconds (452.886 MB/sec).

You can see that like the backup itself, the restore from the encrypted backup took slightly longer – in this case about 3 seconds or 15%. It’s not too bad, but it’s good to be aware that you may see some impact.

Backup Encryption in SQL Server

May 18, 2023May 18, 2023 ~ Matthew McGiffen ~ 2 Comments

We’ve seen in the previous posts that TDE is a simple way of protecting your at-rest data. There may however be times where you can’t or don’t want to use TDE. The main scenario for this is where you are on a version of SQL Server before 2019 (when TDE was made available in standard edition) and you don’t want to pay for the enterprise version which has a high price tag associated with it.

When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.

From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.

Backup Encryption has a lot in common with TDE in terms of the objects required. The encryption hierarchy is the same; you require a DMK and a certificate with a public/private key pair. In theory you can use an asymmetric key instead of a certificate, but this has the disadvantage that you can’t export the asymmetric key – which means you will struggle to restore your database backup to a different server. As such, for the sake of the examples that follow, we’ll just look at the certificate option.

You may however choose to use an asymmetric key if you wish to use Extensible Key Management (EKM) and store the key externally to your SQL Server. We’ll look at EKM in a later post.

Setting Up Backup Encryption

As mentioned, the prerequisites for Backup Encryption are the same as for TDE. We’ll go over creating them again here, but a little more briefly this time. Refer to Setting up TDE for more information.

Creating a Test Database

We’ll start with creating a sample database that we want to backup. You can skip this step if you just want to work with an existing database. We’ll use basically the same database we used for the TDE examples, just with a different name. The following SQL creates the database and populates it with test data:

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

Create the Database Master Key (DMK)

You must have a DMK, which resides in the master database, and you can create it with the following code:

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

You should also backup the DMK using the following command:

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

Creating the Certificate

You also require a certificate in the master database which has an associated public/private key pair. Unlike TDE, in the case of Backup Encryption, this key pair will be used to directly encrypt the backup using asymmetric encryption. There is no separate Database Encryption Key required. You create the certificate with this SQL:

USE master; CREATE CERTIFICATE BackupEncryptionCert WITH SUBJECT = 'Certificate used for backup encryption';

You should take backups of the certificate and private key and keep them safe if you ever want to be able to restore your backups to another server. Here is the SQL to backup these objects:

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

Permissions

It’s possible that the account you generally use for taking backups doesn’t have sysadmin permissions on the server. If that is the case, then there are some additional permissions required. The account needs the db_backupoperator role in each database being backed up, but that should already be in place. The only additional permission required is that the account must have the VIEW DEFINITION permission on the certificate. You can assign that permission with this SQL:

USE master; GRANT VIEW DEFINITION ON CERTIFICATE::BackupEncryptionCert TO [MyBackupAccount];

That’s all we need to do before we are ready to start encrypting our backups.

Working with Encrypted Backups

Now that we have all the objects in place to encrypt our backups, we can look at how you take a backup with encryption enabled and how you restore an encrypted backup.

Taking an Encrypted Backup

It is possible to encrypt any of the backup types – FULL, DIFFERENTIAL, or LOG. In practice if you are using Backup Encryption, you are likely to want to make sure all are encrypted. The syntax is the same in each case though, so we’ll just look at FULL backups. This is the backup command with encryption specified:

BACKUP DATABASE TestBackupEncryption TO DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak' WITH ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptionCert);

You can see we specify the algorithm. As with TDE, AES_256 is recommended (Advanced Encryption Standard with a 256-bit key). We also specify which certificate to use.

We can view data about the backup using the RESTORE HEADERONLY command, which will include information about encryption. Here is the code for that:

RESTORE HEADERONLY FROM DISK = 'C:\Test\TestBackupEncryption_Encrypted.bak';

This returns us a lot of information, so I won’t include the full set of columns. Relevant to encryption though, we will see the following:

KeyAlgorithm – aes_256

EncryptorThumbprint – 0xA2E4A2A29182054B2F97FCD9954FA9349B4351EC

EncryptorType – CERTIFICATE

You can use this if you need to be able to check whether a particular backup is encrypted or not.

Restoring an Encrypted Backup

Restoring an encrypted backup is the same as restoring any other backup – as long as the certificate used to encrypt the backup exists on the server.

If you are restoring to a different server, you will need to restore a copy of the certificate and private key from the backup taken before you can restore the encrypted database (the server must also have a DMK before you can do this). This is the same command we covered in Migrating or Recovering a TDE Protected Database:

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

Then you can simply restore the database as normal with the following command:

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

If you get an error, it is likely to be because the certificate doesn’t exist – for instance, if you have restored the wrong one:

Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint '0xA2E4A2A29182054B2F97FCD9954FA9349B4351EC'. Msg 3013, Level 16, State 1, Line 25

RESTORE DATABASE is terminating abnormally.

TDE: Database Backups and High Availability

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

Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.

Backup Performance

Following on from general questions of TDE performance, it’s sensible that you might also be concerned whether TDE has an impact on backup times. You may also have read of people complaining about long backup times with TDE.

It’s not necessarily true that TDE has much of an impact on backup performance. The reason is that when a backup is performed, SQL Server does not have to encrypt the data. The data already sits encrypted on disk in the data and log files, and those copies of the data are what are used for performing the backup. In practice there may be some data in memory that has yet to be encrypted and written to disk, but in general, that is not going to be large enough to cause significant overhead.

When people talk about issues with backup performance and TDE, they are likely to be talking about the case involving backup compression.

Backup Compression

Many people use backup compression with database backups in SQL Server. It is simple functionality to use as shown in this code example:

BACKUP DATABASE [TestTDE] TO DISK = 'C:\Test\TestTDE_Compressed.bak' WITH COMPRESSION;

The benefit of backup compression isn’t just about having smaller backup files but also in the time taken to perform a backup. The biggest bottleneck involved in taking a backup is usually the time it takes to write it to disk. By taking compressed backups you can significantly reduce backup takes. This comes at the cost of some extra CPU overhead to perform the compression, but unless your CPU is under pressure, it’s often worthwhile.

Up until the 2016 version, SQL Server did not support backup compression on TDE enabled databases. One reason for this may be that most compression algorithms work best where there is some repetition in the data to be compressed, but encrypted data looks pretty much random. What this meant in practice was that you might specify the WITH COMPRESSION option when backing up your TDE-protected databases but you wouldn’t see much difference in the file size or backup times. This changed from SQL 2016 and was a welcome improvement.

To use backup compression with TDE, however, you needed to specify an additional parameter MAXTRANSFERSIZE. This parameter specifies the largest unit of transfer in bytes used between SQL Server and the backup media. If you’re interested in fine-tuning your backup performance, this is one value you can play with. Backup compression with TDE doesn’t kick in unless your MAXTRANSFERSIZE is greater than 64kb (65536). As long as the value you specify is at least one greater than 64k, then an optimized algorithm for compression of TDE encrypted databases is enabled. Commonly people use the value of 128kb. The command looks like this:

BACKUP DATABASE TestTDE TO DISK = 'C:\Test\TestTDE_Compressed.bak' WITH COMPRESSION, MAXTRANSFERSIZE = 131072;

This extra parameter becomes unnecessary if you are on SQL Server 2019 Cumulative Update 5 or higher. With that release, if you specify WITH COMPRESSION for a backup taken for a TDE-protected database and you don’t specify MAXTRANSFERSIZE, then MAXTRANSFERSIZE will automatically be increased to 128kb, and your backup will be compressed.

Backup Compression Issues

The introduction of backup compression for TDE-protected databases has however not been without problems, and this is something you really need to be aware of. There have been a number of bugs discovered where a compressed backup of a TDE database was found to be unrecoverable. Some people have also reported that restore times were massively increased in some cases.

If you’re on a version of SQL Server higher than 2016 CU 7 or 2016 SP1 CU4, then you should be fine, but I would stress the importance of regularly testing your backups by restoring them. A few days before writing this, I came across the term Schrodinger’s Backup – the condition of any backup is unknown until a restore is attempted. When it comes to TDE and backup compression, you should consider that as a very true statement.

TDE and High Availability

In general, TDE plays nicely with any of the built-in features that SQL Server has for high availability (HA). That includes:

Availability Groups
Log Shipping
Database Mirroring

In theory, in all cases, the actions you need to take to support TDE are the same. You just need to ensure that the secondary server has a Database Master Key (DMK). Then you need to ensure that copies of your certificate and private key have been restored to the secondary before you turn encryption on. This is the same step you would take if you were attempting to restore a TDE-protected database to a different server. We covered that a previous post Migrating or Recovering a TDE Protected Database.

As long as that is done, then whichever HA tool you use should take care of the rest.

In practice, we DBAs are cautious folk, and you don’t want to risk anything going wrong when you are dealing with a live system. As such you may want to take the following steps:

Remove the database from HA.
Set up TDE for the database and turn on.
Set up the keys and certificate on the secondary.
Add the database back into HA.

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.

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 ~ 1 Comment

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!

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.