SQL Server 2016 Always Encrypted: Easy to Implement, Tough to Crack
Data is a key asset of any company, particularly transactional data which holds business secrets such as financial or health records. Data is most vulnerable in transit between the server that stores it and that client that requests it.
The standard approach to ensuring security is to encrypt data on the server and use the SSL-enabled HTTPS protocol to secure data in transport. However, what if we could increase the level of security even further, by using HTTPS and sending data in an encrypted format over the communication line, only to decrypt data on clients who have valid certificates? That approach would make a traditional man-in-the-middle (MITM) attack much more difficult.
Microsoft’s solution to this problem is Always Encrypted, a way to send encrypted data over the pipeline and decrypt it only by users with access to valid certificates. So, even if the attacker gets the data, without a proper certificate stored on the client machine, the data would be useless.
This article describes how to set up and use Always Encrypted, and it’s recommended reading for anyone who is sending important data over the public communication lines, even if they are secured with SSL.
The Concept Behind Always Encrypted
Always Encrypted is a client-side encryption technology that Microsoft introduced with SQL Server 2016. Always Encrypted keeps data automatically encrypted, not only when it is written, but also when it is read by an approved application. Unlike Transparent Data Encryption, which encrypts the data and log files on disk in real time but allows the data to be read by any application that queries the data, Always Encrypted requires your client application to use an Always Encrypted-enabled driver to communicate with the database. By using this driver, the application securely transfers encrypted data to the database that can then be decrypted later only by an application that has access to the encryption key. Any other application querying the data can also retrieve the encrypted values, but that application cannot use the data without the encryption key, thereby rendering the data useless. Because of this encryption architecture, the SQL Server instance never sees the unencrypted version of the data.
At this time, the only Always Encrypted-enabled drivers are the .NET Framework Data Provider for SQL Server, which requires installation of .NET Framework version 4.6 on the client computer, and the JDBC 6.0 driver. That will probably change in time, but these are the official Always Encrypted requirements as of April 2017.
But why do we need this technology? There are a couple of good reasons why Always Encrypted should be used:
- Security — Data always needed to be secure. Now that SSL is being compromised, Always Encrypted fills the gap with another layer of transport pipeline protection.
- Regulatory support — Data needs to be encrypted and kept from prying DBA eyes by more and more industry regulations, primarily in the finance and telecom industries. This is described in PII standard (“Personally Identifiable Information”) which states that things like credit card numbers, social security numbers, names, and addresses must be protected, or the data owner can be severely penalized.
How to Use Always Encrypted
Using Always Encrypted requires a small amount of preparation within the database server storing the encrypted tables. The preparation is a two-step process:
- Create the column master key definition
- Create the column encryption key
Column Master Key
So what’s a column master key?
.png)
The column master key is a certificate that is stored within a Windows certificate store (which is used the demo as a certificate storage option), a third-party hardware security module (a generic name for third-party solutions for installing, managing and using certificates ), or the Azure Key Vault (Microsoft’s cloud-based solution for certificate management).
The application that is encrypting the data uses the column master key to protect various column encryption keys that handle the encryption of data within the columns of a database table. Using certificate stores from SQL Server, which are sometimes referred to as Enterprise Key Manager, requires the use of SQL Server Enterprise Edition.
In this article, we describe the use of a self-signed certificate that you store in the Microsoft Certificate Store of the Windows operating system. While this is approach is not the optimal configuration, it demonstrates the concept of Always Encrypted—but it also needs to be stated that this approach is not acceptable for production environments, where certificate management must be done with separate, secured user accounts and, preferably, on separate servers.
You can create a column master key definition by using the graphical interface within SQL Server Management Studio (SSMS) or by using T-SQL. In SSMS, connect to the SQL Server 2016 database instance in which you want to use Always Encrypted to protect a database table.
CREATING AND USING COLUMN MASTER KEYS
In Object Explorer, navigate first to the database, then to Security, and then expand the Always Encrypted Keys folder to display its two subfolders, as shown in the following figures:
Open a new Column Master Key dialog box.
Check the key's existence in the Windows Certificate Store.
To create the column master key, right-click the Column Master Keys folder and select New Column Master Key. In the New Column Master Key dialog box, type a name for the column master key, specify whether to store the key in the current user’s or local machine’s certificate store or the Azure Key Vault, and then select a certificate in the list. If there are no certificates, or if you want to use a new self-signed certificate, click the Generate Certificate button, and then click OK. This step creates a self-signed certificate and loads it into the certificate store of the current user account running SSMS.
Note: You should perform these steps on a trusted machine, but not on the computer hosting your SQL Server instance. That way, the data remains protected in SQL Server even if the host computer is compromised.
So, after creating the certificate and configuring it as a column master key, you must export and distribute it to all computers hosting clients requiring access to the data. If a client application is web-based, you must load the certificate on the web server. If it is an application installed on users’ computers, then you must deploy the certificate to each user’s computer individually.
You can find applicable instructions for exporting and importing certificates for your operating system at the following URLs:
- Exporting certificates
- Importing certificates
When you import certificates into the certificate store on computers with an application that encrypts and decrypts the data, you must import the certificates into either the machine certificate store or the certificate store of the domain account running the application.
Column Encryption Key
After creating a column master key, you are ready to create encryption keys for specific columns. The SQL Server 2016 ADO.NET driver uses column encryption keys to encrypt the data before sending it to the SQL Server, and to decrypt the data after retrieving it from the SQL Server 2016 instance. As with the column master key, you can create column encryption keys by using T-SQL or SSMS. While column master keys are easier to create using T-SQL, column encryption keys are easier to create using SSMS.
To create a column encryption key, use Object Explorer to connect to the database instance, navigate to the database, then to Security, and expand the Always Encrypted Keys folder. Right-click Column Encryption Keys, and then select New Column Encryption Key. In the New Column Encryption Key dialog box, type a name for the new encryption key, select a Column Master Key Definition in the drop-down list and then click OK. You can now use the column encryption key in the definition of a new table.
Creating a Table with Encrypted Values
After creating the column master key definition and column encryption keys, you can create a table to hold the encrypted values.
Before you do this, you must decide what type of encryption to use, which columns to encrypt, and whether you can index these columns. With the Always Encrypted feature, you define column sizes normally, and SQL Server adjusts the storage size of the column based on the encryption settings. After you create your table, you might need to change your application to execute commands on this table using Always Encrypted.
SQL Server 2016 Encryption Types
Before creating a table to contain encrypted values, you must decide whether or not each column should be encrypted.
First, will this column be used for looking up values or just returning those values?
If the column is going to be used for lookups, the column must use a deterministic encryption type, which allows for equality operations. However, there are limitations on searching for data that has been encrypted by using the Always Encrypted feature. SQL Server 2016 supports only equality operations, which include equal to, not equal to, joins (which use equality), and using the value in the GROUP BY clause. Any search using LIKE is not supported. Additionally, sorting data that is encrypted using Always Encrypted must be done at the application level, as SQL Server will sort based on the encrypted value rather than the decrypted value.
If the column is not going to be used for locating records, then the column should use the randomized encryption type. This type of encryption is more secure, but it does not support searches, joins, or grouping operations.
Creating a Table with Encrypted Columns
When creating tables, you use the normal CREATE TABLE syntax with some additional parameters within the column definition. Three parameters are used within the ENCRYPTED WITH syntax for the CREATE TABLE statement.
The first of these is the ENCRYPTION_TYPE parameter, which accepts a value of RANDOMIZED or DETERMINISTIC. The second is the ALGORITHM parameter, which only accepts a value of RAEAD_AES_256_CBC_HMAC_SHA_256. The third parameter is the COLUMN_ENCRYPTION_KEY, which is the encryption key you use to encrypt the value.
CREATE TABLE [dbo].[Customers]
(
[CustomerId] [int] IDENTITY(1,1),
[TaxId] [varchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[Address1] [nvarchar](50) NULL,
[Address2] [nvarchar](50) NULL,
[Address3] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[PostalCode] [nvarchar](10) NULL,
[State] [char](2) NULL,
[BirthDate] [date]
ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL
PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] );
GO
Indexing with Always Encrypted
Columns containing encrypted data can be used as key columns within indexes, provided that those columns are encrypted by using the DETERMINISTIC encryption type. Columns encrypted by using the RANDOMIZED encryption type return an error message when you try to create an index on those columns. Columns encrypted by using either encryption type can be used as INCLUDE columns within nonclustered indexes.
Because encrypted values can be indexes, no additional performance-tuning measures are required for values encrypted with Always Encrypted beyond the indexing and tuning that you normally perform. Additional network bandwidth and greater I/O are the only side effects that result from the increased size of the values being returned.
Always Encrypted Performance
Performance is always a key factor, especially in this case, when we are adding overhead of encryption to usual database traffic. The best site to test performance is SQL Performance, which tested query execution and disk usage under various scenarios:
As there is CPU and hard drive work that needs to be performed with encryption and decryption processes, there is an obvious impact on the amount of storage space used and duration of queries. As this is influenced on your environment—CPU, RAM and disk features—you should test whether this will present a problem in production.
Note: In case you’d like to learn more about Microsoft SQL Sever performance optimization, please check out one of our previous articles, How to Tune Microsoft SQL Server for Performance.
Application Changes
What do you have to do to properly implement Always Encrypted in legacy code?
One of the nice things about the Always Encrypted feature of SQL Server 2016 is that applications already using stored procedures, ORMs, or parameterized T-SQL commands should require no application changes to use Always Encrypted, unless non-equality operations are already being used. Applications that build SQL statements as dynamic SQL within the application and execute those commands against the database directly need to be modified to use parameterization of their queries, a recommended security best practice for all applications, before they can take advantage of the Always Encrypted feature.
Another change required to make Always Encrypted work is the addition of a connection string attribute to the connection string of the application connecting to the database: Column Encryption Setting=enabled.
With this setting added to the connection string, the ADO.NET driver asks the SQL Server if the executing command includes any encrypted columns, and if so, which columns are encrypted. For high-load applications, the use of this setting may not be the best practice, especially if a large percentage of executing commands do not include encrypted values.
Consequently, the .NET Framework provides a new method on the SqlConnection object called SqlCommandColumnEncryptionSetting, which has three possible values:
Disabled— There are no Always Encrypted columns or parameters to use for the queries that are executed by using this connection object.Enabled— There are Always Encrypted columns and/or parameters in use for the queries that are executed by using this connection object.ResultSet— There are no Always Encrypted parameters. However, executing queries using this connection object return columns encrypted by using Always Encrypted.
Note: Be aware that the use of this method can potentially require a significant amount of change to your application code. An alternative approach is to refactor your application to use different connections.
For the best performance of SQL Server, it is wise to request only the metadata about Always Encrypted for those queries that use Always Encrypted. This means that in applications for which a large percentage of queries use Always Encrypted, the connection string should be enabled and the specific queries within the application should specify SqlCommandColumnEncryptionSetting as Disabled. For applications for which most queries are not using Always Encrypted values, the connection string should not be enabled, and SqlCommandColumnEncryptionSetting should be set for Enabled or ResultSet as needed for those queries that are using Always Encrypted columns. In most cases, applications are able to simply enable the connection string attribute, and application performance will remain unchanged while using the encrypted data.
Is Always Encrypted Worth the Effort?
Short answer? Yes, definitely!
Not only does it helps prevent a lot of potential security concerns and provides SQL developers with added security features, but it also makes your system more compliant, which is vital in multiple industries, from telecoms to banking and insurance. It is also important to note that given technical prerequisites mentioned in the article, Always Encrypted can be implemented with minimal application changes to existing systems.
Although you might use custom solutions to get the same effect, this technology is bundled with new version of SQL Server, and can be used out-of-the-box. It is also important to note, as this is a new technology, there are still some limitations on its use, and it adds some extra hardware demenads.
However, unless they are a deal-breaker for your environment, and you have an application that is distributed outside your company’s Intranet, there is virtually no reason not to use Always Encrypted.
Add comment