Data Encryption Using Always Encrypted in SQL Server

Introduction

Data security is always a matter of concern for organizations and application users. The key challenge for any developer or architect is to understand all the concerns and build a foolproof system to ensure data security.  This is not only more intense, but more complex these days, as many organizations are planning to move their applications to the cloud. There’s a lot of room for small and mid-size organizations to take maximum advantage in this time of technology transformation. All of these small and mid-size organizations want to explore the benefits of the cloud. However, the biggest threat, Data Security, needs to be dealt with. Questions to be answered are:

  • How data will be secured at rest as well as in-transit?
  • Will the existing application be able to handle all data security needs or does it require a change in the application?
  • Etc.

There is a need for any organization to find answers to these questions before they plan to migrate their applications to the cloud and continue to support their business.

SQL Server is a database, which provides all types of data security. SQL Server has the capability to secure data at rest and in-transit as well. There are multiple methods that SQL Server provides to secure the data:

  1. TDE to secure data at rest
  2. Encryption based data security to secure cell level values
  3. Dynamic Data Masking – Mask/Unmask data access to unprivileged/privileged users
  4. Role based security to limit the data access
  5. Always Encrypted to secure data at rest and in-transit

Always Encrypted

Always Encrypted – is a feature available in both versions of SQL Server, on cloud (Azure SQL Database) as well as on-premises. It is a robust feature that should eliminate your fears about securing your data at rest and in-transit.

The other data security methods, TDE and cell level encryption are good to keep your data secure at rest. However, these methods do not secure in-transit data. Always Encrypt covers both; it secures data at rest and in-transit completely. Using Always Encrypted, data will be encrypted by the application before it reaches SQL Server and stores encrypted data in SQL table.

The key concept behind the Always Encrypted is, an application will encrypt the data prior to the data reaching SQL Server. To understand the concept of Always Encrypted let’s look at the key terminologies first:

  1. Application driver or client driver is an Always Encrypted-enabled driver, available on the client machine, that is responsible for communicating with the database engine to encrypt and decrypt any column value transparently and make plaintext available to the application.
  2. Column Master Key (CMK), is a key-protecting key. This key is used to encrypt one or more column encryption keys. This key can be stored outside of SQL Server in any trusted external key store.
  3. Column Encryption Key (CEK), is the encryption key that encrypts the data in an encrypted column.
  4. External Key Store (EKS), is the external trusted place to store the column master key. This can be an Azure key vault, or Windows Certificate Store, or a hardware security module (HSM).
  5. Type of Encryption, Always Encrypted supports two types of encryption. Though both types of encryption come with their own characteristics, we can choose any one of the options based on the encryption need
    1. Deterministic Encryption – this method always generates the same encrypted value for any given plain value. Also, this encryption method allows lookups, table joins, grouping and indexing on encrypted columns.
    2. Randomized Encryption – this method always generates a new encrypted value for any given value. The value encrypted with this method is less predictable and most secure. However, this prevents grouping, searching and indexing operations.

How Always Encrypted Works?

To make it work, you need to install the Always Encrypt-enabled driver on the client or application machine. Once the driver is in place you are able to send encrypted column values to SQL Server. The reverse process will take place automatically and is handled by the driver when SQL Server returned the result set to the application. The complete encryption/decryption process is transparent to the application and SQL Server will exchange the encrypted data instead of plaintext.

Always Encrypted Setup at SQL Server

To configure Always Encrypted for database columns, you need to specify the information about the encryption algorithm and cryptographic keys to encrypt the data in the column. SQL Server engine uses the column encryption key to encrypt the column data and column master key to protect the column encryption key.

Once you define column properties for encryption, like encryption type and column encryption key, SQL Server stores this encryption configuration for all encrypted columns in the database metadata. Also, SQL Server is intelligent enough that it stores encrypted values of the column encryption key (encrypted by column master key) instead of plaintext and only the location information of the column master key.

Always Encrypted Setup and Process at Client Machine

The application communicates with SQL Server via the Always Encrypted-enabled driver installed on the client/application machine. Once the driver receives a parameterized query, the driver fetches the information from SQL Server to identify which column needs to be encrypted and other metadata, such as  column encryption key, algorithm type, and column master key location.

Before the driver encrypts the identified column, it goes to external key store to get the column master key. Once driver has all the information, including column master key, first it decrypts the column encryption key and thereafter uses this plaintext column encryption key to encrypt the parameter received from application and sends the query with encrypted column values to SQL Server for processing.

SQL Server processes the query and returns the result set to the application (driver) with metadata for any encrypted column. The driver tries to find the plaintext value of the column encryption key in the local cache and only makes a round to the external key store if it doesn’t find it in the local cache. Afterwards, the driver decrypts the returned result set and returns the plaintext values to the application.      

In the below diagram, I’ve explained the overall process flow between Always Encrypted-enable driver to SQL Server and SQL Server to Always Encrypted-enable driver

Application to SQL Server Communication

SQL Server to Application Communication

Application to SQL Server

SQL Server to Application

 

Limitation with Always Encrypted

Although this feature is a great way to maintain data security at rest and in-transit, however, its imposes  the following limitations:

  1. Can not implement on columns which have data type – XML, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias, user defined-types, including FILESTREAM , SPARSE columns
  2. Can not implement on columns that have properties like IDENTITY or ROWGUIDCOL
  3. Limitation on columns with Default constraint and columns referenced by check constraints 
  4. Columns that are masked using Dynamic Data Masking
  5. Columns in tables that use change data capture and change tracking

There are other limitations based on type of encryption (Deterministic or Randomized) chosen on the table columns. You can refer to a complete list of limitations here.

Summary

Overall, Always Encrypted is one of the important features introduced in SQL Server 2016 and available in higher versions, including Azure SQL Database. This gives you the ability to secure sensitive data at rest as well as in-transit. In this process, the Always Encrypted-enabled driver takes care of data encryption and decryption tasks. As the driver takes care of all data encryption and decryption at application end, the data is completely safe and secure over the network. Thus, Always Encrypted is the feature to secure your sensitive data at in-transit and at rest.

See all articles by Anoop Kumar

Anoop Kumar
Anoop Kumar
Anoop has 15+ years of IT experience, mostly in design and development of Enterprise Data warehouse and Business Intelligence solutions. Currently, Anoop is working on various Big Data and NoSQL based solution implementations. Anoop has written many online technical articles on Big Data, Hadoop, SQL Server and SSIS. On an education front, he has a Post Graduate degree in Computer Science from Birla Institute of Technology, Mesra, India. Disclaimer : I help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles