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.

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.

TDE in Standard Edition on SQL 2019

November 5, 2019May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

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

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.

Instantaneous Transaction Rollback with SQL 2019

September 25, 2019May 10, 2023 ~ Matthew McGiffen ~ 1 Comment

If you’ve read about the Accelerated Database Recovery feature in SQL Server 2019 you could be forgiven for thinking it’s just about speeding up database recovery time in case of a server failure.

In fact, enabling it also means that where you have a long running transaction that fails or is cancelled the rollback is almost instantaneous. This is great news for DBAs who have to sometimes kill a long-running blocking transaction but worry that it may take a long time to rollback – continuing to block all that time.

This is achieved by the fact that Accelerated Database Recovery maintains a version store in the database, and where a row is updated, the old version of the row is kept until after the transaction is complete. That makes it quick and easy to revert to the old version in case of failure.

Let’s look at a quick example.

I have a table with about 10 million rows – all containing the same text value:

CREATE DATABASE TestADR;
USE TestADR;
CREATE TABLE dbo.TestADR(Id int IDENTITY, SomeText varchar(50));
INSERT INTO dbo.TestADR (SomeText)
SELECT TOP 10000000 'FrangipanDeluxe' 
FROM sys.objects a, sys.objects b, sys.objects c, sys.objects d;

I update all the rows in the table to a new value:

UPDATE dbo.TestADR SET SomeText = 'FrangipanDeluxA';

This took about a minute.

I then execute a query to change them back and cancel the query in SSMS after about 30 seconds.

UPDATE dbo.TestADR SET SomeText = 'FrangipanDeluxe';

It took about 30 seconds more to cancel – which is SQL rolling back the changes.

Then I enabled Accelerated Database Recovery, you do this at the database level:

ALTER  DATABASE TestADR 
SET ACCELERATED_DATABASE_RECOVERY = ON;

Now I re-run that last update, again cancelling after 30 seconds.

This time the cancel was instantaneous, it took SQL no noticeable amount of time to roll back the changes.

This is great but we’ll probably want to be careful before we enable it on all our databases – when we get them onto SQL 2019 anyway. There will be an additional overhead in managing the version store and that could have an impact in terms of time taken to complete write queries, as well as storage requirements.

Still, it seems like a good feature – something to look forward to playing with more.

Check Query Progress with Live Query Stats

September 18, 2019May 10, 2023 ~ Matthew McGiffen ~ 2 Comments

This is something I touched on back in 2017 a little after the Live Query Statistics feature was introduced with SQL 2016, but I was using the functionality this morning and felt like it was worth a reminder.

https://matthewmcgiffen.com/2017/02/23/livequerystats/

You can use Live Query Stats to check on the progress of an executing query – and you can do it through the GUI in SSMS.

I created a long running query for a demo, and after 15 minutes I was still waiting for it to finish. That was a bit longer than I intended. Should I kill it – or did I just need to wait a few more minutes for it to complete.

You can check this quickly via the Activity Monitor:

Find the query you are interested in in the processes list:

Right-click and select “Show Live Execution Plan”. That will show you something like this:

I can see from this that my query is about 83% complete, so maybe I’ll just wait a little longer. Note that this is a live view, so the numbers keep updating. If I want I can watch the progress.

This is against a SQL 2019 instance and is the out of the box behaviour. Before SQL 2019 you had to enable trace flag 7412 if you wanted this to work:

DBCC TRACEON(7412,-1);

SQL Server Wishlist

September 11, 2019May 10, 2023 ~ Matthew McGiffen ~ 3 Comments

This month for T-SQL Tuesday Kevin Chant asks us what our fantasy SQL feature would be.

https://www.kevinrchant.com/2019/09/03/t-sql-tuesday-118-your-fantasy-sql-feature/

I think it’s appropriate to give a shout-out to Microsoft at this point, because over the last few releases they’ve given us some of the items that are top of my list.

Recommending and setting MAXDOP during setup (coming with SQL 2019) will hopefully mean I no longer have to have arguments about why the out-of the-box setting isn’t appropriate.

The same with setting max memory in the setup (also with SQL 2019).

A more verbose error where string or binary data might be truncated – we got that in SQL 2017.

It’s the little things like these that make me happy – and make my job easier.

A few other little things I’d like – though I accept that something small to describe isn’t always small in its execution…

A change to the default cost threshold for parallelism – be it 20, be it 30, be it 50. I’d prefer any of those to 5.

I think it would also be great to have a cardinality optimizer hint, e.g. OPTIMIZE FOR 5 ROWS. Oracle has this, and it’s not good having to feel jealous of those working on the dark side 😉 You can do the equivalent but it’s convoluted and not clear what’s going on when the uninitiated see the code:

https://dba.stackexchange.com/questions/168726/sql-server-cardinality-hint

There is one big thing I’d like – but it’s never going to happen. Get rid of Enterprise Edition – or rather, make Enterprise Edition the standard. Enterprise is comparatively so expensive, it’s rare I’m going to recommend it. It’s interesting to see that in Azure SQLDB we only have one edition to work with – I’d love to see that in the box product. I understand that change would be a massive revenue loss so can’t see it happening.

If not that though, if we could just have at-rest encryption (i.e. TDE) in the Standard Edition that would make me very happy. In these days of security and privacy consciousness it seems that should be a core functionality.

UPDATE: TDE is going to be available on Standard Edition from SQL Server 2019. I get my wish!

Finally, I’d just like to upvote Brent’s idea that it would be great to be able to restore just a single table.

That all said, I’d like to go back to my first point. I love what MS is doing with SQL Server, and the continual improvements that are being made. I particularly love that we sometimes get them for free in service packs and cumulative updates – without having to upgrade to a new version.

Keep it coming!

Table Variable Performance “Fixed” in SQL 2019

September 25, 2018May 10, 2023 ~ Matthew McGiffen ~ 3 Comments

Reading the new features for SQL 2019 I spotted this: TableVariables2019_1

One of the most popular posts on my blog last year was where I pretty much suggested that people not use table variables:

Think twice before using table variables

This wasn’t new information when I wrote it, but bad performance due to the use of table variables remained such a common anti-pattern that I thought it was worth stressing again.

So, when I saw the above 2019 feature I thought I’d better investigate and update what I’m telling people.

TL;DR It looks like table variables are no longer a problem.

I’m going to re-use my examples from the previous post (as well as some of the images captured). One thing to note though it that I’m running these tests on a different, much less performant box than I did last time, so execution times will be longer.

My first test last time was just to show how cardinality estimation was better for a temp table vs. a table variable. This time I’m just going to compare a table variable running under compatibility mode 140 (SQL 2017) with one running under compatibility mode 150 (SQL 2019).

I set the compatibility mode as follows:

ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 140;

Then I run the first query:

DECLARE @a TABLE(i INT);
 
--Create a million rows
WITH Nums(i) AS
(
       SELECT 1
       FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(i)
)
INSERT INTO @a(i)
SELECT n1.i
FROM Nums n1 
CROSS JOIN Nums n2 
CROSS JOIN Nums n3 
CROSS JOIN Nums n4 
CROSS JOIN Nums n5 
CROSS JOIN Nums n6;
 
SELECT i FROM @a;

The execution plan for that final select looks like this:
Variables1

And when I hover over the Table Scan operator I can see the properties:

Variables2

You can see that the estimated number of rows is calculated as 1 – but the actual number of rows was 1 million. This is (was) the problem with table variables.

Now I’ll change the compatibility level:

ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL = 150;

And run the query again.

I get the same execution plan, so let’s just look at the properties of the Table Scan operator this time:
TableVariables2019_2
Here you can see that the estimation is correct, 1 million. This is looking good so far for the future of table variables.

In the last post I then looked at a case where the bad estimation caused a poor selection of execution plan so let’s repeat that test. First, I set my compatibility level back to 140, then I run the following (if you want to repeat, you need the AdventureWorks2012 database):

DECLARE @BusinessEntityId TABLE (BusinessEntityID INT NOT NULL PRIMARY KEY CLUSTERED);
 
INSERT INTO @BusinessEntityId
SELECT BusinessEntityID FROM Person.Person;
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
SELECT COUNT(*)
FROM @BusinessEntityId b
INNER JOIN Person.Person p
       ON b.BusinessEntityID = p.BusinessEntityID;
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Let’s look at the execution plan for the final query:
Variables5
Now let’s look at the properties for the clustered index scan:
Variables6
We can see that estimated number of rows is just 1, but that the actual number of rows is 19,972.

If we then look at the properties for the Index seek operator:
Variables7
We can see that the estimated number of executions was 1, but the actual number of executions was 19,972. That’s 19,972 seeks into a table with 19,972 rows. As previously discussed – there’s got to be a better way. A Nested Loops join is generally best when there’s a small number of rows from the top table, and a larger amount of rows from the bottom. In this place they both have 19,972 so it’s not optimal.

Here’s the output of the statistics commands for that query:

Table ‘Person’. Scan count 0, logical reads 59916, physical reads 3810, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#B5D4D4C8’. Scan count 1, logical reads 35, physical reads 29, read-ahead reads 28, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 30951 ms.

Now we’ll change the compatibility level to 150 and try again. Here’s the execution plan we get this time:
TableVariables2019_3
You can see the join operator is now a Hash Match which is generally a much better choice for evenly sized tables. Let’s look at the properties of the Clustered Index Scan in this new plan:
TableVariables2019_4
This time the estimated and actual values match, which has enabled SQL to make the better choice of plan. We can see that if we look at the statistics output for this one:

Table ‘Workfile’. Scan count 4, logical reads 64, physical reads 0, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Person’. Scan count 1, logical reads 67, physical reads 1, read-ahead reads 65, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#B9A565AC’. Scan count 1, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 25220 ms.

You can see the CPU and reads are massively reduced.

So, it seems table variables aren’t so bad anymore. At least they won’t be once you are running SQL Server 2019.

I should probably add some caveats at this point. This has been a limited test, so you yourselves should make sure you test your own performance if you start using table variables instead of temp tables. There are also going to be deferred compilations when you do this in stored procedures. I haven’t dug down into exactly how that will work with table variables, but I guess it’s going to be similar to the way temp tables work in existing SQL versions.