I’m going to present a very nice feature of SQL Server — actually one of my favourite new features in SQL Server. This is SQL Server Always Encrypted Data.
This is the first time when you can separate the data owner from the administrator in SQL Server. In this way the person or the group of the people that can read the data is separated from those who are administrating the database server itself.
Advantages of SQL Server Always Encrypted Data
Basically, there are three different approaches or reasons for doing this, but let’s focus on the details and how it works.
First, there is a possibility of enhanced client driver that is making the encryption for you, that is transparent for the application. The most important part is when we are separating it from the environment, from the database server. So, enhanced client driver is getting the plain text and encrypting it, using the key that is encrypted inside the database, but by the certificate that is actually on the client’s side. That’s why it’s impossible to decrypt the data because the key that is needed to decrypt or encrypt the data is stored in the database in encrypted form. The encryption is performed by the certificate that is never released from the client’s side.
The benefits of “always encrypted” is client-side encryption, SQL Server doesn’t know actually the keys to the certificate because the keys are in SQL Server but in an encrypted form. Next, the queries on encrypted data are possible with:
- equality comparison
- join
- group by
- distinct operators
This is great and if you are currently performing the encryption on the client’s side with, for example, C# code, those queries are not possible. And the most important fact is that this is almost totally application transparent (almost because there are some limitations on it but let’s see it on a real example).
Always Encrypted Overview
We will be doing something very similar. I will be using the application that will be performing some select or update with the parameters. Without this enhanced driver the administrator can use, for example, SQL Server Profiler to see the actual comments that are executed and the volumes of the parameters. So, the driver will encrypt the parameters and the encrypted ciphertext will be presented to the SQL Server Profiler, to the SQL Server, and we actually cannot extract this information as we are not the administrator, only the data owner can see it. That means the results are also encrypted. They are passing through the same driver on the same client, so it is decrypting it automatically and presenting the results.
Always Encrypted Data on practice
Now, let’s see it in a practical approach. I will be using two different machines. They are named very similarly, both are SQL: one is “SQL01” and the second one is “SQL02.” But these are only the names and I actually used this SQL02 as my web server and this is the client’s side. Please remember it’s just a name and nothing related to SQL itself. This is from where I’m the data owner.
If I go to “SQL01,” this is actually the database server and on this database server I have a database which is called “CyberCrime”. And I’m using the SQL Server Profiler on this one to trace what is happening. Let’s go just with the “Replay” template and in “Events Selection” let’s give only one database name, “CyberCrime”, so I will exclude any other queries. Let’s see what is happening if I’m using my current application.
I’m switching back to my “SQL02”, I have my web browser here and I’m going to edit the first employee and I will change the social security number (SSN) from version zero (733235593-0) to version one (733235593-1). Click “save.” This is the new version of it.
Let’s see what is on the “SQL01”. You can see that there was some connection, I was selecting something, then there was an additional information “SELECT”. Now I have the update and here you can see all the parameters. Even the new and old social numbers are there. It’s not the best way to hide those data from the admin, even if the SQL Server enforcing the connection to be encrypted but in this case we are tracing it from the server side as an admin.
Enabling Always Encrypted database
Let’s do this and let’s enable the “always encrypted” database. From the perspective of the application, first of all, we need to use SQL Server Management Studio or PowerShell to encrypt the data. Remember, I’m connecting to “SQL01” from “SQL02,” so this is from my server on which I’m running IIS Server. Let’s do this. Right click on the name of the database, “Tasks” > “Encrypt Columns.” This is starting the wizard. Click next. I’m choosing that I’m going to encrypt SSN, Gender and Marital Status.
What type of encryption do I want to use? First of all, “Deterministic” is very good if we have different data inside this column. If we have, for example, Gender, or only two of those, then “Randomized” is a better option. But I will show you what is the difference because “Deterministic” will present the same values in encrypted form if I’m using the same values to be encrypted and “Randomized” will present always different.
As an encryption key for all of those comments I will use a single encryption key. It will be called “CEK_Auto1.” Of course, I can create a new key before using it so choose the proper name for that. This key will be actually stored on the SQL Server itself but it will be encrypted with the private key that is the part of the certificate that will be generated. This will be called “auto generate column master key.” This column master key is used to decrypt column encryption keys. I want to store it in the certificate store. Currently I have only “Current User” because I’m not running the SQL Server Management Studio as an admin, but with elevated privileges.
I’m going to do this right away, not generate the script, but right now to encrypt everything and to generate the certificates.
Generating certificate for Windows Certificate Store
I can show you right now that I have no certificates in the local computer and in the personal users store. This is the auto-generated certificate for the computer but on the right side, on the user, I do not have it. It is right now generating the certificate for Windows Certificate Store for “Current User.” So, in a couple of seconds I will see a new certificate on the right side. So I can refresh and you can see it generated a new “Always Encrypted Auto Certificate 1.” And now it’s performing the encryption of the columns and encryption of the data. And after that the data are not accessible through the application because it’s going to change the data type from, for example social security number was “varchar,” now it’s going to be “varbinary.” In local computer store I still do not have anything on the personal certificates that is similar to Always Encrypted.
Okay, it’s already encrypted. Let’s go and see what we have in our application. I’m going to click on the “Employees List” and now I have a problem because the “SSN” property on “Employee” is “System.Byte[]” but we were expecting “System.String.” This is because we currently have it in an encrypted form.
Let me go and see with the script here, Open > File… > Always Encrypted (it’s always nice to have it). So, if I go and select “Employees,” let’s see what will happen. Right now, everything is decrypted for me. This is because in the “Connection” options, if I “Disconnect” and “Connect again,” I already had one parameter, from previous attempts in this one. Additionally, I will exclude this and connect again. The same query right now, remember to connect with “CyberCrime.” Now it will be presenting the data in encrypted form because it is without additional parameter in the connection stream to decrypt the data automatically.
Using different algorithms
As you can see that for the Gender that was a “Deterministic” encryption, and it’s not allowing me to see a “Randomized” version of it. So, if that was male, male, male probably this is female, female, and again male, female, male, female. I can only guess it, but probably based on the names I can get who is who. So, on the “Marital Status” everything is different because I used a different type of algorithm for it.
Now let’s reconnect again. “Connection” > “Disconnect,” “Connection” > “Connect,” then go to > “Options,” > “Additional Connection Parameters.” These are the parameters for the connections in “Column Encryption Setting=Enabled.” Connect. Use again the same database, and let’s see if now we can do it. I’m trying to do it and everything is automatically decrypted.
If I’m going to do the same query from different server (from the perspective of my administrator this is “SQL01”), I’m trying to do this > “New Query,” paste it and I’m executing this. Close. It’s going to say that it cannot decrypt the data because there is lack of the certificate. Right now, I’m an administrator and I cannot see the data in it because I’m trying to decrypt it but there is no certificate with this thumbprint.
Let’s go back to my application. In my application on “SQL02” (remember this is my web server also), in PowerShell I’m going to show you that I have the certificate with exactly the same thumbprint. I’m going to use the possibility of PowerShell to go directly to the Certificate Store, so this is like a Drive, and here I have actually the possibility to print the certificate and this is exactly the same thumbprint of the certificate. I will copy it because I will reuse it in just a couple of seconds in a different place. So I’m copying it, pasting it, and then I will delete the old one.
Changing the connection stream in IIS Server
Now I need to change the connection stream in my IIS Server in my application. Let’s do this with the ISE. In the “connectionStrings,” which I’ve already prepared there are exactly the same words. “Column Encryption Setting=Enabled,” – this is the new part of the connection string which I’m adding right now.
After this it’s going to try and decrypt the data exactly the same way. It will fail the same way but to allow to do this I need to have a certificate. If you think about the IIS Server, the certificate must be placed not in the current user certificate store, but for the local computer.
So, let me export the certificate: All Tasks > Export… > Yes, export the private key > Next > Next > Group or user names > Next > c:\hw.pfx > Finish. I’m importing it into local computer. I have it. The last part to do is to allow application pool identity under which this application is running to get to the private key of the certificate. “Manage Private Key…” and “Add…” from the local SQL, “IIS APPPOOL\DefaultAppPool,” > Check Names, and allow only “read” the private key, do not allow full control.
Creating new Column Master Key (CMK)
Okay, we are almost there. The thing to do is actually that inside the SQL Server if you go to the “Always Encrypted Keys”, if we go to definition “”create new query editor,” of the “CMK_Auto1,” you will see that the Column Master Key is pointing to the “CurrentUser/my…” and this is the certificate thumbprint. What I want to do is actually create CMK key for IIS Server, or maybe not IIS but local machine (CMK_LM), and say that this is not actually the Current User but local machine. Create a new Column Master Key which will be pointing to the LM store, not to the Current User store. Execute this one.
The second part is that I need to see the definition of the column encryption key. The column encryption key is actually the key that is encrypted and it’s pointing that this is encrypted with the master key of CMK_Auto1. Now I want to have it with two different values. One will be with the Column Master Key which is pointing to the user personal store and another one for the LM. I already prepared that. So, CEK_Auto1 will be encrypted with CMK_LM. The value here is actually the key that was generated and encrypted with the certificate. And let’s go – add it.
If you now generate the script for the column encryption key (so the real key that is actually encrypting the data), we can see that it can be decrypted with this (CMK_Auto1) certificate, or with this one (CMK_LM). The CMK_Auto1 is actually pointing to the CurrentUser local certificate store and this one is pointing to the LocalMachine certificate store. The values are exactly the same because this is exactly the same certificate. Column encryption key can have up to two different column master keys. If you want to change it you need to remove one and create again additional one.
Now let’s see if it’s possible to use the application again. Let’s try to refresh, (fingers crossed) and now we can see it. Okay, let’s see if it’s really encrypted. I’m going back to my “SQL01” on which the admin is going to sneak what we are going to do. Let’s see it, I’m clearing the profiler and going to the IIS Server to see if actually my application is working, refreshing the employee list, and let’s go and edit some values, for example for “Catha.” Let’s modify it, save it, and okay, it was modified, the shorter version of the SSN. Now let’s go to the “SQL01” to see what we can see as an admin. Scrolling a little bit lower to see the update and now it’s the update. Remember, previously we’ve seen the values, now we see only the encrypted data. And, as you can see, it is transparent for the application.
So if I want to see the decrypted data I need to actually get the certificate and put it into local computer or into the user certificate store. This is a great feature and I strongly recommend to look into that and have fun with it.
Want more insights from Mike Jankowski-Lorek? Join advanced training with demos on arp spoofing, capturing credentials, and cross database chaining.