Interacting with Encrypted Columns

Hello and welcome. In this segment, we’re going to interact with some columns that have been encrypted using Always Encrypted. And we’re going to use SSMS to do that. Some of the features we’re using require at least SSMS 17. I’ll try to mention that along the way as we see it. Let’s get started.

We last left off looking at the people table with a series of encrypted values being shown to us. If it looks like those encrypted values have changed, you have a very good eye as I’ve changed venues for this demo. For the balance of the demos for this video, I’m going to use this machine which has SSMS installed. And we’re going to connect with a SQL Server that I’ve called secure SQL. Having this on two separate machines will allow us to more accurately depict a production environment.

The first thing we’ll want to see is how to turn this cipher text into plain text. There are two steps required to make that happen. The first is that we’ll need access to the column master key. Let’s import that now. If it feels like I raced through that demo, I did. And that is because I have an entire separate video dedicated to managing all of the keys and certificates that are required to do all the different types of encryption SQL Server offers. I would encourage you to watch that video if you wish to learn more about this.

Now that we have the column master key available to us, we need to tell our connection that we intend to use it. And we do that by adding this text to our connection string. In SSMS, we go to the options, the additional parameters, and we paste it in. In an application, we would simply add this text as an element to our connection string. By adding that text, we’ve told the connection we would like to attempt to decrypt any encrypted values. And we know that the key we’ll ask for is available, which should change our values from cipher text to plain text. This is all that is required to read plain text values from encrypted columns.

What if we want to search through an encrypted column. We might run a query that looks like this. And we would get an error that looks like this. The error is pretty clear. It tells us that this varchar that we’re trying to compare is incompatible with the encrypted varchar.

If we remember from an earlier segment, we have to parameterize this. Our parameterize statement might look something like this, where we’ve simply replaced our text with a variable name. In order to use this in SMSS, and this is the feature that the documentation tells us is only available starting an SMSS 17, is that we need to do tools, options, query execution, SQL Server Advanced. We have to come all the way to the bottom and enable parametrization for Always Encrypted. Our execution plan tells us that this did a table scan.

We also know that it learned about this by calling the sp describe parameter procedure. We’re actually allowed to run that ourselves, which we see here. This was run invisible to us by SMSS in order to determine how to encrypt the log on variable. The procedure is simple. You give it the statement you’re going to run, and you’ll define the variable it sends.

We’ll execute this statement. And we see that it returns that the log on parameter in fact needs to be encrypted. If it didn’t need to be encrypted, these would be both zero. And it tells us to use encryption number one. Well, there’s only one, so that was easy enough to see. It’s this one.

And what it provides to us, that’s the column encryption key. And we can see that it’s been encrypted. And it tells us that we can decrypt that if we have access to the MS SQL certificate store and in it we have a key with this path. Our client has behind the scenes invisibly looked up that certificate, decrypted the column encryption key and encrypted KLW to a real encrypted value, and then searched for that value in the table. We’re able to search that column because of the use of deterministic encryption, which also means that we should be able to create an index on the same. And in fact, we can. As a reminder, if we attempt to create an index on a column encrypted with randomized encryption, we’ll be told no. That is not valid for use as a key column.

If we go back and rerun our select statement, what had been a table scan now shows an index seek. This search was for an exact match. If we attempt to do something other than an exact match, perhaps a like statement, even though the column has been encrypted with deterministic encryption and it’s indexed, attempting to execute this query, even parameterized, gives us a pretty nasty error. And in fact, from running the described parameter on it, we see that that’s where it went wrong. It didn’t even attempt to execute it. As soon as it ran describe parameter encryption, it was told no. This is not something we’re able to do.

If we attempt to run a where clause against a column that’s been encrypted with randomized encryption, again, it fails. Index or not, and of course not because we can’t make one, you cannot put a randomized encrypted column in a where clause. And again, this is one that doesn’t attempt to execute. As soon as the driver attempts to validate the query, it fails before it even starts.

What if we want to change an encrypted value? We might run something like this, updating the people table, setting a log on name to me for person one. Attempting to execute this query, gives us a similar error to earlier where this varchar value is not compatible with this encrypted value. This needs to be encrypted on the client side before it gets sent to the server.

We might attempt to make that query look something like this. We’re going to set application.people’s log on name equal to this variable where the person ID equals this variable. Before we execute it, let’s jump down and see what describe parameter encryption looks like for this query. We see that there are two parameters.

The person ID does not need to be encrypted. That makes sense. It’s not an encrypted column. You can just send the number one. The log on name does need to be encrypted. And it needs to use column encryption key one. And again, there is the encrypted value, and there’s the instructions on how to decrypt that value. Knowing that that’s going to work, we should be able to run this and update row one. Fantastic. Moving back up to our first query, and we should see that row one now belongs to me.

The example we just completed was an update statement. This would work the same for an insert, an update, or potentially a delete if you were trying to put something in a where clause. Now that we’re able to interact with these columns using SMSS, in our next segment, we’ll look at some of the requirements our applications not using SMSS might need to follow in order to also interact with these columns.