Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.
The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.
At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.
The calling application (including SSMS) must also have an extra parameter in the connection string: column encryption setting=enabled.
To edit the data you must use a parameterised query, you cannot update it using a “normal” update query. Additionally you can’t update encrypted tables using SSMS, it must be done using one of the supported drivers such as PowerShell or .NET application using ADO.NET 4.6.
Overall, Always Encrypted is seen as a major step forward in protecting your data and has received an enthusiastic response since it was launched.
The easiest way is to implement Always Encrypted is to right click on the table you wish to encrypt and select “Always Encrypted” although it can be done using T-SQL.
Create a Table
First let’s create a table with some data
Encrypting the data using the Wizard
We will use the wizard to encrypt the data.
There are two ways to start the wizard, either right click on the table and select “Encrypt Columns” or Right Click the database and select Tasks -> Encrypt
Select the columns that you wish to encrypt.
There are two types of encryption available; deterministic and randomised. Deterministic encryption always produces the same encrypted value for a particular text string when you run a query. This means that same string in a column will always return the same encrypted value, if you have a column with many repeated values it might be possible for a hacker to deduce what the real values are.
Randomised produces a different value each time the query is run. This makes randomised the more secure of the two options since it will always return a different encrypted value. But the drawback of randomised is that the columns can’t be used in equality or search operations, grouping, indexing, or be used for joining tables.
The master encryption key can be stored in your local Windows certificate store or in an Azure key vault. I haven’t tried the Azure key vault so in this example I will use the local Windows certificate store and place it in Current User’s store.
The encryption process can take a long time to run and it is not something that you would do on a live production system where you could corrupt records by encrypting them as they are being updated. This page allows you to create a PowerShell script that you can run when you have downtime.
In this case we will continue and run the encryption straight away.
If everything is correct select finish to start the encryption process.
Even with only eight records in this example it can take a few minutes to complete.
Viewing the Encrypted Data
Once the process has completed select the data and you will see that the encrypted columns now display encrypted values.
If you try and insert or update the table you will get an error
Managing your Certificate
Your certificate is held in your certificate store.
To view your certificate store type “mmc” into the Windows Run box on the Start menu.
In the management console window select File -> Add / Remove Snap-ins and select Certificate from the list and then select current user
Click OK and you will now see your certificates on your PC.
Drill down Personal and you will see the Always Encrypted Certificate. Export this and distribute it to all those who need to view the data. And keep it somewhere safe, without this certificate your data will not be readable, ever.
Viewing Unencrypted Data
In SSMS reconnect your query but add the parameter “column encryption setting=enabled” to your connection parameters. Run your select statement again and you will now see the data in clear text.
But if you try and insert or update data you will still get an error
As I said previously, you cannot update encrypted data using a standard query, even in SSMS.
Updating Data using Stored Procedure
To be able to update your table you must use a parameterised query. Below is an example
Running the procedure from SSMS will not work. SSMS does not use one of the supported connection types such as ADO to connect to the database.
To be able to insert data you must connect using one of the supported connections currently ADO.NET 4.6, JDBC 6.0 and ODBC 13.1
Using PowerShell to update Data
Start PowerShell IDE . The following command will read the data from your encrypted table.
The following will run the parametrised stored procedure we created earlier. You will notice that the two encrypted values require a full set of parameters values to work correctly, it is not enough just to pass the parameter and the new value like the non encrypted columns.
Microsoft have implied that converting your application to use Always Encrypted is simply a case of encrypting your data and recompiling your .NET application to use ADO.NET 4.6 but as you will have seen from the examples above it may be a bit more complicated than that. All of your queries will need to be checked to ensure they are parameterised and even if they are the parameters being passed may need altering to add the extra options required.
Despite this SQL Server 2016 Always Encrypted is brilliant new addition to keeping your data secure either on premises or in the cloud and is well worth investigating.
If you try to view data using “column encryption setting=enabled” without a certificate installed you will get an error
You can naturally create encrypted columns using t-sql as shown below. Note the binary collation.
The certificate can be placed in an Azure key vault rather than local certificate store, more information can be found here