Introduction

In this article, we will see how to encrypt the SQL Server Table Column using the Always Encrypted option which was introduced in SQL Server 2016. Unlike Transparent Data Encryption, Always Encrypted helps us to encrypt certain columns of the table rather than the whole database. There are multiple options to safeguard the data at rest within the database However to give a solution to the problem of encrypting the data when it is read or written to the database, Always Encrypted feature has been introduced.

Getting Started

With Always Encrypted we can encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine. The encryption technique uses two Keys:

Column Master Key

Column Master Key is stored on an application machine, which is used to protect the Column Encryption key.  Database Engine only stores information about the location of column master keys which are stored in external trusted key stores. Thus, SQL Server cannot decrypt the Always Encrypted data by itself.

Column Encryption Key

Column Encryption Key, is stored on the SQL Server.   It is used to encrypt/decrypt the Always Encrypted columns

Always Encrypted in Action

Let’s see how to implement Always Encrypted in a single server installation. In a production environment, we would be exporting the Master Key to the application server other than the SQL Server and the Encryption Key will be present in the SQL Server.

As the first step lets create the Master key. If we head over to the database, we can see the ‘Always Encrypted Keys’ section from where we can create the Master and Encryption Keys.

Create Column Master Key

To create the Column Master Keys, right click the below selection and select ‘New Column Master Key’

This will open the window where we can specify the certificate store and the key name.

Click on OK which will create the Column Master Key.

Next we will create the Column Encryption Key.

Create Column Encryption Key

Specify the name and associate the Master Key which we had created in the previous step.

Create Table with Always Encrypted Column

Once we have created the Master Key and Encrypted Key, we will create the table with Always Encrypted column. Here we have created a table that will hold Car Sales data with an Always Encrypted column by the name ‘Total Revenue’. There

  • Deterministic -  Encrypts the column value to the same cipher text every time.
  • Randomized -  Encrypts the column value to a different randomized cipher text every time.

-

Insert data from SSMS

The table has been created and if we try to execute an Insert or Update command against the table from  any SSMS which is less than version 17.0, we will ger the operand type clash error.

INSERT dbo.CarSalesRevenue(Brand,Car,Quarter1,Quarter2,Quarter3,Quarter4,TotalRevenue) SELECT'Ford','Figo',3000,4000,3500,4500,15000;

If we try to edit the table directly, we will still not be able to update the Always Encrypted Column. This is because Parameterization for Always Encrypted enables updating and filtering encrypted columns from SSMS which is absent in versions SSMS 16.0.It has been made available in SSMS 17.0 which helps us to try Always Encrypted end-to-end using SSMS.

The only way to update the row is to keep the Encrypted Column as Null and enter value for other columns.

Note :

This is by design in SSMS versions less than 17.0 . However, in SSMS 17.0 we have a work around to Insert/Update values to the Always Encrypted Column from SSMS which we will explore towards the end of the article.

Add Data to Always Encrypted Column

Let’s see how we can use the Client Application and update Always Encrypted Column with values. To demo that we will create a stored procedure that accepts the values as parameters and use ADO.Net to connect to update the table using the Stored procedure.

CREATE PROCEDURE dbo.CarTotalRevenue
 
  @Brand VARCHAR(25), 
  @Car   VARCHAR(25), 
  @Quarter1 int, 
  @Quarter2 int, 
  @Quarter3 int, 
  @Quarter4 int, 
  @TotalRevenue int, 
AS 
BEGIN 
  INSERT dbo.CarSalesRevenue(Brand , Car ,Quarter1, Quarter2 , Quarter3 , Quarter4,TotalRevenue) SELECT @Brand, @Car,@Quarter1,@Quarter2,@Quarter3,@Quarter4,@TotalRevenue;
 END 
GO

Once we have created the stored procedure we will create a client console application in Visual Studio and use parameterized SQL Command to call the SQL Stored Procedure and update the table. Ensure that we add the attribute ‘Column Encryption Setting = Enabled’ in the connection attribute.

    

con.ConnectionString = "Data Source=.;Initial Catalog=CarSales;Integrated Security = True; Column Encryption Setting = Enabled";
                    con.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.CarTotalRevenue", con))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter Brand = new SqlParameter("@Brand", System.Data.SqlDbType.VarChar, 25);
                        Brand.Value = "Ford";
                        SqlParameter Car = new SqlParameter("@Car", System.Data.SqlDbType.VarChar, 25);
                        Car.Value = "Ford";
                        SqlParameter Quarter1 = new SqlParameter("@Quarter1", System.Data.SqlDbType.Int);
                        Quarter1.Value = "3000";
                        SqlParameter Quarter2 = new SqlParameter("@Quarter2", System.Data.SqlDbType.Int);
                        Quarter2.Value = "4000";
                        SqlParameter Quarter3 = new SqlParameter("@Quarter3", System.Data.SqlDbType.Int);
                        Quarter3.Value = "5000";
                        SqlParameter Quarter4 = new SqlParameter("@Quarter4", System.Data.SqlDbType.Int);
                        Quarter4.Value = "4000";
                        SqlParameter TotalRevenue = new SqlParameter("@TotalRevenue", System.Data.SqlDbType.Int);
                        TotalRevenue.Value = "16000";
 
                        cmd.Parameters.Add(Brand);
                        cmd.Parameters.Add(Car);
                        cmd.Parameters.Add(Quarter1);
                        cmd.Parameters.Add(Quarter2);
                        cmd.Parameters.Add(Quarter3);
                        cmd.Parameters.Add(Quarter4);
                        cmd.Parameters.Add(TotalRevenue);
                        cmd.ExecuteNonQuery();
                    }

Once we run the console application it will add the new values to the SQL Server table and update the Always Encrypted Column as well. If we go to the SQL Server, we can see that the row has been added and the Always Encrypted column has its value in an encrypted state.

View Always Encrypted data from SSMS

We can see the Always Encrypted data from SSMS by modifying the connection. To do this head over to the Query tab and select Change Connection option.

Click on the Options button.

Add ‘Column Encryption Setting = Enabled’ to Additional Connection Parameters tab and click on Connect.

We can now see the Encrypted Data in Total Revenue column.

Update Always Encrypted Column using SSMS 17.0

SSMS 17.0 and above has the option to edit the Always Encrypted Column. You can get SSMS 17.1 from here.

Once we have installed it, From the Query Tab, select Change Connections options. Add ‘Column Encryption Setting = Enabled’ to Additional Connection Parameters tab and click on Connect.

Once again from the Query Tab, select Query Options.

From Advanced -> Check the box ‘Enable Parameterization for Always Encrypted’

Now if we try to update the Always Encrypted Column it will successfully update the column.

Declare @TotalRevenue int = 18000
Update dbo.CarSalesRevenue
Set TotalRevenue = @TotalRevenue
where Car = 'Baleno'

Thus, with SSMS 17.0 and above we can make updates to the Always Encrypted Column from SQL Server Management Studio.

Limitations

We saw how to use Always Encrypted in SQL Server 2016. But it comes with some limitations as well. As per the official Microsoft TechNet documentation, Always Encrypted is not supported for the below columns [docs.microsoft.com]:

  • Columns using one of the following datatypes: xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types.
  • FILESTREAM columns
  • Columns with the IDENTITY property
  • Columns with ROWGUIDCOL property
  • String (varchar, char, etc.) columns with non-bin2 collations
  • Columns that are keys for nonclustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  • Columns that are keys for clustered indices using a randomized encrypted column as a key column (deterministic encrypted columns are fine)
  • Columns that are keys for fulltext indices containing encrypted columns both randomized and deterministic
  • Columns referenced by computed columns (when the expression does unsupported operations for Always Encrypted)
  • Sparse column set
  • Columns that are referenced by statistics
  • Columns using alias type
  • Partitioning columns
  • Columns with default constraints
  • Columns referenced by unique constraints when using randomized encryption (deterministic encryption is supported)
  • Primary key columns when using randomized encryption (deterministic encryption is supported)
  • Referencing columns in foreign key constraints when using randomized encryption or when using deterministic encryption, if the referenced and referencing columns use different keys or algorithms
  • Columns referenced by check constraints
  • Columns in tables that use change data capture
  • Primary key columns on tables that have change tracking
  • Columns that are masked (using Dynamic Data Masking)
  • Columns in Stretch Database tables. (Tables with columns encrypted with Always Encrypted can be enabled for Stretch.)
  • Columns in external (PolyBase) tables (note: using external tables and tables with encrypted columns in the same query is supported)
  • Table-valued parameters targeting encrypted columns are not supported.

Summary

Thus we saw how to get started with Always Encrypted option in SQL Server 2016.