What's new in SQL Server 2016: Always encrypted

 
 
  • Gérald Barré

This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!

The final version of SQL Server 2016 has been released recently. So it's time to take a look at the new features provided by this version. Today we will discover the Always Encrypted feature.

Encrypting sensitive data such as some personal information is a plus for security. Currently, SQL Server offers encryption features with TSQL ENCRYPTBY... functions to encrypt a value. Insertion or selection requests must, therefore, use these functions each time. The other encryption solution proposed is Transparent Data Encryption (TDE). This feature allows you to encrypt database data and log files transparently for the user. However, a user with sufficient permission can access the data in clear.

Always Encrypted helps meet this need. We encrypt the data directly (like ENCRYPTBY...) and the data encryption key is no longer on the server hosting the database but on the side of the application. Thus only the application has the key to decrypt the data and this decryption occurs transparently at the application level and not at the database level. This way a DBA will not have a way to view sensitive data.

Data can be encrypted in 2 ways:

  • Deterministically: the encryption of the same data always gives the same result (a bit like a hash)
  • Randomly: the encryption of the same data can give different results

The first method will be used when there is a need to research these data. In this case, only the operators = and <> can be used. The second method does not allow searching but offers better security.

#Encryption keys

There are 2 types of encryption key:

  • Column encryption key: The key to encrypt the contents of a column (stored on SQL side)
  • Column master key: The key protecting Column Encryption Keys (stored on application-side)

First, the ADO.NET client makes a request to retrieve the encryption keys from the columns and decrypts them with the master key. Then, when executing a request, it starts by encrypting the parameters that must be encrypted and sends the request to SQL server for processing. Finally, the client decrypts the values returned by the server. Data security is therefore assured.

#Setting up the server

We start by declaring the Master Key. For this we indicate its location on the machine hosting the application and its footprint:

It is important to note that this is a simple statement. The key is not stored on the SQL server but on the application server.

Now we create the encryption key of the columns:

Then we create the table with the columns by specifying the key of encryption as well as the type of encryption for the columns which one wishes to encrypt:

SQL
CREATE TABLE Patient (
    Id [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [FullName] [nvarchar](50) NULL,

    [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = SampleCEK) NOT NULL,

    PhoneNumber nchar(10)
    ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
    COLUMN_ENCRYPTION_KEY = SampleCEK) NOT NULL
)

#Setting up the client

The first thing is to indicate in the connection string that we want to use Always Encrypted:

C#
string connectionString = "Data Source=(local);Database=SampleAlwaysEncrypted; Integrated Security=true;Column Encryption Setting=enabled";

Then everything happens normally. The only constraint is to use parameterized queries but that everyone does it already, is not it? For example to insert a line we can do:

C#
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
    connection.Open();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO Patient(FullName, SSN, PhoneNumber) VALUES (@FullName, @SSN, @PhoneNumber)";
        command.CommandType = System.Data.CommandType.Text;

        var fullNameParameter = command.CreateParameter();
        fullNameParameter.ParameterName = "@FullName";
        fullNameParameter.Value = "John Doe";
        command.Parameters.Add(fullNameParameter);

        var ssnParameter = command.CreateParameter();
        ssnParameter.ParameterName = "@SSN";
        ssnParameter.Value = "1234";
        command.Parameters.Add(ssnParameter);

        var phoneNumberParameter = command.CreateParameter();
        phoneNumberParameter.ParameterName = "@PhoneNumber";
        phoneNumberParameter.Value = "0123456789";
        command.Parameters.Add(phoneNumberParameter);

        int result = command.ExecuteNonQuery();
    }
}

Since the SSN column uses deterministic encryption, you can search using this parameter:

C#
using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM dbo.Customer WHERE SSN = @SSN";
    command.CommandType = System.Data.CommandType.Text;
    var parameter = command.CreateParameter();
    parameter.ParameterName = "@SSN";
    parameter.Value = "1234";
    command.Parameters.Add(parameter);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(reader.GetOrdinal("SSN")));
        }
    }
}

If we look at the contents of the table we see that the data are encrypted:

And if we use SQL Server Profiler we can check that the data is encrypted during the insertion:

#Conclusion

Always Encrypted provides a data encryption solution transparently and ensures that only the application can decrypt the data. This greatly reduces the risk of access to sensitive data by unauthorized persons (including sysadmin).

Do you have a question or a suggestion about this post? Contact me!

Follow me:
Enjoy this blog?Buy Me A Coffee💖 Sponsor on GitHub