Thursday, July 9, 2015

Using Always Encrypted with NHibernate

SQL Server 2016 has introduced a new security feature called Always Encrypted. The feature ensures that no sensitive data is stored in plaintext on the SQL server. The ADO.NET data provider makes the encryption process transparent to applications by automatically encrypting and decrypting sensitive data on the client computer. The data provider encrypts the data before sending it to the SQL server. In a similar way, the data provider decrypts the data before serving it to the client application.

Always Encrypted enables encryption of confidential data for storage in the database and prevents database administrators or other third parties from accessing the data. Thanks to the built-in Always Encrypted feature, a usage scenario of an on-premises application accessing data in the cloud becomes more appealing. The burden of purchasing and maintaining hardware is moved to cloud providers without losing the control over the data privacy.

In this blog post, I would like to show the Always Encrypted feature in action. I will create a NHibernate based application that stores sensitive data on the SQL Server.

Setting up the database

Before we start creating the sample application, we need to create encryption keys and a table to store the confidential data.

1. Create a master key

You can use makecert.exe (certificate creation tool) to create a master key.

makecert -r -pe -n "CN=AlwaysEncryptedMasterKey" -b 01/01/2015
-e 01/01/2016 -sky exchange -ss my

This would create a self-signed certificate with an exportable private key and place the certificate into the personal certificate store of the current user.

The master key is only available to the client application. The ADO.NET data provider uses a master key to encrypt column encryption keys.

2. Create a master key definition and column encryption key

Next, you need to tell the SQL server where the master key is located. This is done by creating a column master key definition.

In the SQL Server Management Studio, select your database and go to Security -> Always Encrypted Keys -> Column Master Key Definitions -> New Column Master Key Definition. Choose the previously created certificate and click OK.

It is worth mentioning that the SQL server neither accesses nor stores the master key. The created master key definition just holds a path to the master key. The SQL server sends the specified master key path to the client application when accessing an Always Encrypted table. In this way, the client knows where the master key should be extracted from.

Now, you should create a column encryption key. Go to Security -> Always Encrypted Keys -> Column Encryption Keys -> New Column Encryption Key. Select the created column master key definition and click OK.

It is important that the SQL Server Management Studio has access to the private key associated with the certificate created in the first step. The Management Studio uses the private key to sign the encrypted column encryption key. If no private key can be accessed, you would get the following error message.

The certificate associated with the specified column master key definition is invalid for encrypting a column encryption key, or you do not have permission to access it. Certificate specified in the key path 'xx' does not have a private key to encrypt a column encryption key.

3. Create a table with Always Encrypted columns and stored procedure

The sample application uses a simple table that stores sensitive customer data. The table holds encrypted names and personal ids of customers. Use the following SQL code to create the table.

CREATE TABLE Customers (
  Id INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(60) 
       ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyEncryptionKey,
                       ENCRYPTION_TYPE = RANDOMIZED,
                       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'), 
  PersonalId VARCHAR(8) 
             COLLATE Latin1_General_BIN2 
             ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyEncryptionKey,
                             ENCRYPTION_TYPE = DETERMINISTIC,
                             ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);

Please note that two different encryption types are applied to the table columns. Column PersonalId utilizes the deterministic encryption method which always produces the same encrypted value for any given plain text and thus allows filtering by equality on encrypted values. Column Name uses the randomized encryption method which is less predictable in its nature and does not allow equality filtering.

Next, you would need to create a stored procedure that is responsible for creating new customers.

CREATE PROCEDURE sp_add_customer @Name NVARCHAR(60), @PersonalId VARCHAR(8) 
AS 
INSERT INTO Customers (Name, PersonalId)
VALUES(@Name, @PersonalId);

Creating the sample application

After the database has been set up, you can start creating a sample application that creates and accesses customer data.

First, you should create a customer entity class and provide mapping to the corresponding database table.

public class Customer
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string PersonalId { get; set; }
}
public class CustomerMap : ClassMapping<Customer>
{
    public CustomerMap()
    {
        Table("Customers");
        Id(x => x.Id);
        Property(m => m.Name, m => m.Type(NHibernateUtil.String));
        Property(m => m.PersonalId, m => m.Type(NHibernateUtil.AnsiString));
    }
}

Next, extend the application with functionality for creating new customers. To do so, you would need to call the stored procedure that we have created before.

using (var session = SessionFactory.OpenSession())
{
    session.CreateSQLQuery("exec sp_add_customer :name, :personalId")
           .SetParameter("name", txtName.Text, TypeFactory.GetStringType(60))
           .SetParameter("personalId", txtPersonalId.Text, TypeFactory.GetAnsiStringType(8))
           .ExecuteUpdate();
}

Please note that the name and personal id of a new customer are provided in the plain text form. No encryption is performed in the code. The ADO.NET data provider is responsible for encrypting the customer related data. Let us check this.

Make sure that your connection string contains the Column Encryption Setting option and start the application.

"Data Source=MyDbServer; Initial Catalog=MyDatabase;
Integrated Security=true; Column Encryption Setting=enabled"

If you add a new customer and inspect the generated SQL statement with SQL Server Profiler, you would see the name and personal id have been encrypted before reaching the SQL server. The ADO.NET framework has encrypted the customer related data before sending it out to the database.

The data of the newly created customer is stored encrypted in the database.

For accessing customer names and personal ids, you would need to build a usual query with QueryOver. No decryption is required in the application itself. Similar to the previous case, the ADO.NET data provider decrypts the data before passing it over to the application.

using (var session = SessionFactory.OpenSession())
{
    using (session.BeginTransaction())
    {
        var customers = session.QueryOver<Customer>()
                               .List<Customer>();
 
        gridCustomers.DataSource = customers;
    }
}

If you need to filter customers by a personal id, just use an additional where condition. As already mentioned, only columns of the deterministic encryption are supported by the equality filtering.

var customers = session.QueryOver<Customer>()
                       .Where(c => c.PersonalId == txtLoadByPersonalId.Text)
                       .List<Customer>();

You can find the full source code of the application in GitHub. Have fun with trying out the Always Encrypted feature!