dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted February 19, 2018

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


Data Encryption Using Always Encrypted in SQL Server

By Anoop Kumar

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM