Creating a New Table

Hello and welcome. In this segment, we’re going to make a new table that has sensitive data in it that needs to be encrypted. We’ll have to make smart decisions with regards to our key structure and encryption types. Let’s get started. We first see our table is going to have patient data in it, and that there are three columns that will need to be encrypted– the name, the government ID, and the date of birth.

The first step to encrypting this data will be to create the necessary keys. We do that in SQL Server Management Studio by browsing to the database in the Object Explorer. We enter the database, Security, Always Encrypted Keys, and there we see the two layers to the encryption hierarchy, the column master keys and the column encryption keys.

Since the column encryption keys need to be encrypted by a column master key, we have to start by creating the column master key. In our example, let’s assume that anyone that is permitted to decrypt any of those three columns will be allowed to decrypt all of those columns. Because of this, we’ll be able to make a single column master key that we can give to anyone that’s going to decrypt any patient data.

We’ll give this column master key a name. I’ve called mine PatientData. And I’m going to store it in the Windows Certificate Store. Remember that this is the certificate that lives outside of SQL Server that we’ll later give to our users. It’s important not to click OK until first clicking Generate Certificate. Our certificate exists, and we can click OK.

Next, we must make at least one column encryption key. We could technically create as few as one, and share it among all three columns, since they’re going to share a single column master key. Or we could make two, or we could even make three. In this example, we’re going to make a separate key for each column– three total column encryption keys.

We do that by right clicking and generating a new column encryption key. We must first give it a name, PatientName. And we have to tell it which column master key we want to use to encrypt this key in the hierarchy. We only have the one, so that’s what we’ll choose. We’ll repeat this for the other two keys.

Remember that these column encryption keys will forever reside among the metadata inside this database. But column master key will exist on the SQL Server long enough to encrypt the column encryption keys, after which it can be removed from the SQL Server, and should be removed from the SQL Server.

Now that we have all the necessary keys in place, let’s go back to our table. The first step here on the table is to force the collation level of any text-based column to a binary collation, as that is required for column encryption. We’ll do this twice. Next, we’re going to tell you that this column will be encrypted with– and the first thing it wants is the column encryption key.

And we’ll give it the name, PatientName. Next, it wants an encryption type. Here we must choose from randomized and deterministic. In this example, we’re going to say that people will not be searching for names, and we’re going to choose randomized encryption.

Lastly, it wants an algorithm. The algorithm is that, which is an AES 256-bit encryption. By typing the end parenthesis, we have completed the encryption of this column. We’ve chosen a column encryption key and a type, and we’ve typed in the algorithm. Our next column, we’ll do the same.

Our government ID, as said in an earlier segment, as a textbook example for when deterministic encryption might be accurate. In this example, I think it’s reasonable to think that people might want to search for someone by their Social Security or other government ID, so we’re going to choose deterministic encryption. Then lastly, we paste in our algorithm one more time.

Moving on to the date of birth. For date of birth, I don’t think we’ll be searching on that. So it makes sense that we would again choose randomized. And we’ll paste in our algorithm one last time. With this, we are ready to execute and create our patient table. And our table has been created.

Let’s explore it a little bit. Let’s start by running a simple help command. We can see that our table looks quite normal. We see that our data types are everything we expected them to be. Our collection is non-standard, but necessary for encryption. Running the helpIndex command, we see, as expected, our single clustered index that was created with the table.

Let’s now try to create some new indexes. We’ll start by trying to create one on the PatientName column, which from above, we see was created with randomized encryption. Attempting to execute this command gives us an error. It’s a nice clean error. It tells us that this column was encrypted using randomized encryption, and is therefore not valid for use as a key column in a constraint, index, or statistic.

On the other hand, the government ID was done with deterministic encryption, and creating the index happens very easily. Rerunning our helpIndex command, we see that both indexes now exist. We now have a table with five columns– three of them encrypted, and two of them, one encrypted and one not encrypted, that have been indexed.