Always Encrypted in SQL Server 2016

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jul 21, 2016

Always Encrypted in SQL Server 2016



Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. US social security numbers), stored in SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server.


Introduction:

SQL Server 2016 introduces three new principal security features – Always encrypted, Dynamic Data Masking and Row Level Security. While all these features are security related, each one provides a different level of data protection. Through this blog we will explore the “ALWAYS ENCRYPTED” feature, how it works and when it should be used to protect data in SQL server.

Always Encrypted Architecture

The architecture for Always Encrypted has the application performing the column level encryption prior to the confidential columns being sent to the SQL Server. The actual encryption is done by the ADO.NET drivers on an application, or client machine. When a .NET application sends plain text data to ADO.NET it is encrypted prior to sending it to SQL Server. The only change to store encrypted data that the application needs to make is to change the connection string to indicate column encryption is enabled. When column encryption is enabled ADO.NET will encrypt Always Encrypted columns prior to sending the data to SQL Server, and will decrypted Always Encrypted columns when they are read from SQL Server. The following diagram shows this architecture:

sql server data protection

Img src: http://www.databasejournal.com/features/mssql/exploration-of-sql-server-2016-always-encrypted-part-1.html

SQL Server 2016 Always Encrypted Concepts: There are several core concepts used in Always Encrypted. 

Column Master Key - This is an encryption key that protects the column encryption key(s). You must have at least one master key before encrypting any columns.

Column-level encryption setting - A column must be set to encrypted, with a specific column encryption key, the algorithm (currently only one algorithm is supported), and the type of encryption to be used.

Deterministic - Always encrypts to the same cipher text, so can be used for certain operations (point lookups, distinct, group by) and can be indexed.

Randomized - More secure, but cannot be evaluated or used in any operations (write/display only) and cannot be indexed.

Connection string - In order for the client driver to understand that column encryption is in use, the connection string must have the following attribute:

Column Encryption Setting = enabled;

Getting Started with Always Encrypted

Use the Always Encrypted Wizard to quickly start using Always Encrypted. The following example demonstrates the process for encrypting a column.

  • Create a new database, and create one or more tables with columns that you wish to encrypt.
  • Connect to your database using the Object Explorer of Management Studio.
  • Right-click your database, point to Tasks, and then click Encrypt Columns to open the Always Encrypted Wizard.
  • Review the Introduction page, and then click Next.
  • On the Column Selection page, expand the tables, and select the columns that you want to encrypt.
  • For each column selected for encryption, set the Encryption Type to either Deterministic or Randomized.
  • For each column selected for encryption, select an Encryption Key. If you have not previously created an encryption keys for this database, select the default choice of a new auto-generated key, and then click Next.
  • On the Master Key Configuration page, select a location to store the new key and select a master key source, and then click Next.
  • On the Validation page, choose whether to run the script immediately or create a PowerShell script, and then click Next.
  • On the Summary page, review the options you have selected, and then click Finish. Close the wizard when completed.

Permissions: There are four permissions for Always Encrypted. 

  • ALTER ANY COLUMN MASTER KEY
    Required to create and delete a column master key.
  • ALTER ANY COLUMN ENCRYPTION KEY
    Required to create and delete a column encryption key.
  • VIEW ANY COLUMN MASTER KEY DEFINITION
    Required to access and read the metadata of the column master keys to manage keys or query encrypted columns.
  • VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
Required to access and read the metadata of the column encryption key to manage keys or query encrypted columns.

A Few Things to Consider:

  1. Data is encrypted at all times.
  2. Encryption keys are not stored on the server.
  3. You’ll need to upgrade your client software to .NET 4.6
  4. This is not TDE.
  5. Encrypted columns take significantly more space.

Limitations:

Encryption Algorithm 

Only one algorithm option is currently supported: AEAD_AES_256_CBC_HMAC_SHA_256.

Columns/Data Types

String-based columns that are encrypted with deterministic encryption must use a _BIN2 collation (e.g. Latin1_General_BIN2). 

The following data types are *NOT* supported as encrypted columns, per the documentation:

  • text/ntext/image
  • XML/hierarchyid/geography/geometry
  • alias types/user-defined data types
  • SQL_VARIANT
  • rowversion (timestamp)

Tools/Features:

  • Always Encrypted in not supported in SQL Server Data Tools (SSDT).
  • Transactional or merge replication
  • Distributed queries (linked servers)

Typical Scenarios

Client and Data On-Premises

A customer has a client application and SQL Server both running on-premises, at their business location. The customer wants to hire an external vendor to administer SQL Server. In order to protect sensitive data stored in SQL Server, the customer uses Always Encrypted to ensure the separation of duties between database administrators and application administrators. The customer stores plaintext values of Always Encrypted keys in a trusted key store which the client application can access. SQL Server administrators have no access to the keys and, therefore, are unable to decrypt sensitive data stored in SQL Server.

Client On-Premises with Data in Azure:

A customer has an on-premises client application at their business location. The application operates on sensitive data stored in a database hosted in Azure (SQL Database or SQL Server running in a virtual machine on Microsoft Azure). The customer uses Always Encrypted and stores Always Encrypted keys in a trusted key store hosted on-premises, to ensure Microsoft cloud administrators have no access to sensitive data.

Client and Data in Azure:

A customer has a client application, hosted in Microsoft Azure (e.g. in a worker role or a web role), which operates on sensitive data stored also stored in Microsoft Azure. The customer uses Always Encrypted to reduce security attack surface area (the data is always encrypted in the database and on the machine hosting the database). 

Conclusion:

“Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. US social security numbers), stored in SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server. As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access).”  In my next blog we will discuss about “Dynamic Data Masking” which is another new security feature. For any questions on this feature click below. You can also leave a comment in the field below:

Ask Vishal