Encrypting an Existing Table

Hello and welcome. In this segment, we’re going to encrypt some columns on an existing table using Always Encrypted. The process for an existing table is different than creating a new table with encrypted columns. In order to encrypt a column on an existing table, we must use either an SSMS Wizard or Powershell. We can not use T-SQL to complete this task. The SSMS Wizard has an option to generate the PowerShell needed to replicate the operation. We’ll be using the SSMS Wizard to do power work, but we’ll take a look at the PowerShell to familiarize ourselves with how that works. Let’s get started.

We’re here now in SQL Server Management Studio and we’re looking inside the WideWorldImporters database at the people table. Let’s take a look at some data inside this table. I’ve selected four columns that I think we might want to encrypt. The log on name and email address, which are mostly the same values, but we’re going to say that we want to make sure they’re both private. And then, two different phone numbers. One the phone number and another a fax number. We’ll notice that there are a lot of repeated values in the phone and fax number, and that’s going to be important for us in a moment.

As we browse to the people table inside the Object Explorer, you’ll notice that it is not a system versions table. If you download Wide World Importers from Microsoft, you might notice that it is. And that is because I turned that feature off for this demo so that it would be more simple to do. We’ll right click on the table and choose the option encrypt columns. That brings up this wizard that shows us the name of our table, and if we expand this plus sign, a list of the columns within the table.

We are interested in encrypting the log on name, phone number, fax number, and email address. We have a few decisions now to make. For each table, we must choose randomized or deterministic encryption. For the log on name, I want to choose deterministic because I think people might search for themselves by log on name. For a phone number, I would like to choose deterministic. We see that there are a lot of repeated values, and now we can take a look at how deterministic encryption will look in a scenario where there’s low cardinality.

For a fax number, which also has a lot of repeated values, we’ll choose the preferred randomized and the same for email address. We’ve gotten some yellow warning boxes. We see that they are changing the column collation to a binary collation, which is a requirement to run encryption. And that is the same warning box for all four columns.

Lastly, we’re asked to choose a column encryption key for each column. And we see that we can either choose an existing column encryption key, which we made in a previous demo, or we can make a new column encryption key on the fly. You can only make one column encryption key in this window for all the columns. So if you wish to make a separate one for each column, they’ll have to be created in advance.

We also have this apply one key to all columns, which we’re going to do for this demo. And we’re going to generate one new column encryption key and apply it to all of our columns. Clicking next, it’s time to generate a column master key. We can either create a new one in the Windows Certificate store or the Azure Key Vault, or we can pick the existing from, again, a previous demo. We’re going to allow it to generate for us a new one.

Clicking next, we have two choices. We can either finish right now, or we can generate the PowerShell script. I’ve already generated the PowerShell script, so we’re going to skip that, but we can review the one I previously saved in a moment. Here’s a review and we’re ready to go. This step is going to generate each of the keys and perform the actual encryption operations.

There was a warning on the previous page telling us we should try to leave this table alone, especially writing to it during this operation. I like to consider this a completely blocking operation that should be done during an outage when there are no users in the system. OK. The operation has completed. This warning across the bottom has come up every time I’ve ever run this, and I’ve never experienced the problem. So I don’t pay it any attention.

If we come back to our select statement, which just a moment ago delivered us two email addresses and two phone numbers and execute it one more time, we see some very different information. Our log on name has been encrypted, our email address has been encrypted, and the same for our phone number and fax number. What’s interesting is that our deterministic phone number encryption has brought us the exact same ciphertext for every value. We can see that even though it’s difficult to tell what value this is, we can tell they’re all the same. While that allows for faster searching, it can also present a real problem related to the security of the data within the column. The fax number column also had repeated data, but each piece of ciphertext is very different meaning we have all the security we need, but we have lost the ability to search.

We jump now to PowerShell where I have opened up the script that that same wizard had generated it just moments ago. We see that it starts by importing a module to support SQL Server. It then creates a connection during the points to our Wide World Importers database, and it creates an object that points to the database. We create a simple array, and we put our four columns into that array, each one calling the new SQL column encryption setting commandlet. We tell it the column we want to talk to, in this case application people log on name. We give it an encryption type, deterministic or randomized, and we give it the name of an encryption key.

Lastly we call the set SQL column encryption commandlet, which makes those changes that we just put in the array and runs them against the object created with our connection string. Conspicuously absent is any mention of the column encryption keys or column master key. That is because the wizard created those for us before creating the PowerShell. If you wish to do this without using the wizard, you’ll have to make your own column encryption keys and your own column master keys in advance so that you can reference the column encryption key in the PowerShell. Now that we’ve encrypted some existing data, how do we add new rows to this table, and how do we also decrypt this data on the client side? I’m glad you asked. Those answers are coming up in the next few segments.