Transparent Data Encryption and Always Encrypted

If you need to store confidential data in your database, you can use data encryption. SQL Server supports encryption with symmetric keys, asymmetric keys, certificates, and password phrases. I assume that you, the reader, are already familiar with these terms. In this article, I will focus on two out of many encryption options provided by SQL Server:

• Transparent Data Encryption (TDE)
• Always Encrypted (AE)

Transparent Data Encryption

The Transparent Data Encryption (TDE) protects the data at rest when it is not used. When the data is used, SQL Server decrypts it automatically. You can use the TDE for real-time encryption and decryption of the data and log files. You encrypt the data with the database encryption key (DEK), which is a symmetric key. It is stored in the database boot record and is therefore available already during the database recovery process. You protect the DEK with a certificate in the master database. You can also use an asymmetric key instead of the certificate; however, the asymmetric key must be stored in an EKM module. TDE uses the AES and Triple DES encryptions only. TDE was first implemented in SQL Server with version 2012.

You can use TDE on user databases only. You cannot export the database encryption key. This key is used by the SQL Server Database Engine only. End users never use it. Even if you change the database owner, you don’t need to regenerate the DEK.

TDE encrypts data on the page level. In addition, it encrypts also the transaction log. You should backup the certificate used to protect the DEK and the private key used to protect the certificate immediately after you enable TDE. If you need to restore or attach the encrypted database to another SQL Server instance, you need to restore both, the certificate and the private key, or you are not able to open the database. Note again that you don’t export the DEK, as it is a part of the database itself. You need to keep and maintain the certificate used to protect the DEK even after you disable the TDE on the database. This is because parts of the transaction log might still be encrypted. The certificate is needed until you perform the full database backup.

Always Encrypted

SQL Server 2016 Enterprise Edition introduces a new level of encryption, namely the Always Encrypted (AE) feature. This feature enables the same level of data protection as encrypting the data in the client application. Actually, although this is a SQL Server feature, the data is encrypted and decrypted on the client side. The encryption keys are never revealed to the SQL Server Database Engine. This way, also a DBA can’t see sensitive data without the encryption keys, just by having sysadmin permissions on the SQL Server instance with the encrypted data. This way, AE makes a separation between the administrators that manage the data and the users that own the data.

AE Keys

You need two keys for Always Encrypted. First, you create the column master key (CMK). Then you create the column encryption key (CEK) and protect it with the CMK. An application uses the CEK to encrypt the data. SQL Server stores only encrypted data, and can’t decrypt it. This is possible because the column master keys aren’t really stored in a SQL Server database. In the database, SQL Server stores only the link to those keys. The column master keys are stored outside of SQL Server, in one of the following possible places:

• Windows Certificate Store for the current user
• Windows Certificate Store for the local machine
• Azure Key Vault service
• A hardware security module (HSM), that supports Microsoft CryptoAPI or Cryptography API: Next Generation

The column encryption keys are stored in the database. Inside a SQL Server database, only the encrypted part of the values of column encryption keys are stored, together with the information about the location of column master keys. CEKs are never stored as a plain text in a database. CMKs are, as mentioned, actually stored in external trusted key stores.

Using the AE Keys

An application can use the AE keys and encryption by using an AE enabled driver, like .NET Framework Data Provider for SQL Server version 4.6 or higher, Microsoft JDBC Driver for SQL Server 6.0 or higher, or Windows ODBC driver for SQL Server version 13.1 or higher. The application must send parameterized queries to SQL Server. The AE enabled driver works together with the SQL Server Database Engine to determine which parameters should be encrypted or decrypted. For each parameter that needs to be encrypted or decrypted, the driver obtains the metadata needed for the encryption from the Database Engine, including the encryption algorithm, the location of the corresponding CMK, and the encrypted value for the corresponding CEK. Then the driver contacts the CMK store, retrieves the CMK, decrypts the CEK, and uses the CEK to encrypt or decrypt the parameter. Then the driver caches the CEK, in order to speed up the next usage of the same CEK. The following figure shows the process graphically.

The figure represents the whole process in steps:

1. Client application creates a parameterized query
2. Client application sends the parameterized query to the AE enabled driver
3. The AE enabled driver contacts SQL Server to determine which parameters need encryption or decryption, the location of the CMK, and the encrypted value of the CEK
4. The AE enabled driver retrieves the CMK and decrypts the CEK
5. The AE enabled driver encrypts the parameter(s)
6. The driver sends the query to the Database Engine
7. The Database Engine retrieves the data and sends the result set to the driver
8. The driver performs decryption, if needed, and sends the result set to the client application

AE Encryption Types

The Database Engine never operates on the plain text data stored in the encrypted columns. However, some queries on the encrypted data are possible, depending on the encryption type. There are two types of the encryption:

• Deterministic encryption, which always generates the same encrypted value for the same input value. With this encryption, you can index the encrypted column and use point lookups, equality joins, and grouping expressions on the encrypted column. However, a malicious user could try to guess the values by analyzing the patterns of the encrypted values. This is especially dangerous when the set of possible values for a column is discrete, with a small number of distinct values.
• Randomized encryption, which encrypts data in an unpredictable manner.

AE Demo: Creating the Objects

It is time to show how AE works through some demo code. First, let’s create and use a demo database.

USE master;
IF DB_ID(N'AEDemo') IS NULL
   CREATE DATABASE AEDemo;
GO
USE AEDemo;
GO

Next, create the CMK in SSMS GUI. In Object Explorer, refresh the Databases folder to see the AEDemo database. Expnad this database folder, expand the Security subfolder, then the Always Encrypted Keys subfolder, and right-click the Column Master Key subfolder and select the New Column Master Key option from the pop-up menu. In the Name text box, write AE_ColumnMasterKey, and make sure you select the Windows Certificate Store – Local Machine option in the Key Store drop-down list, like the following figure shows.

You can check if the CMK was created successfully with the following query.

SELECT * 
FROM sys.column_master_keys;

Next, you create the CEK. In SSMS, in Object Explorer, right-click the Column Encryption Keys subfolder right under the Column Master Key subfolder and select the New Column Encryption Key option from the pop-up menu. Name the CEK AE_ColumnEncryptionKey and use the AE_ColumnMasterKey CMK to encrypt it. You can check whether the CEK creation was successful with the following query.

SELECT * 
FROM sys.column_encryption_keys;

Currently, only the new binary collations, the collations with the BIN2 suffix, are supported for AE. Therefore, let’s create a table with appropriate collations for the character columns.

CREATE TABLE dbo.Table1
(id INT,
 SecretDeterministic NVARCHAR(10) COLLATE Latin1_General_BIN2 
  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey,
   ENCRYPTION_TYPE = DETERMINISTIC,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
 SecretRandomized NVARCHAR(10) COLLATE Latin1_General_BIN2
  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey,
   ENCRYPTION_TYPE = RANDOMIZED,
   ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);

AE Demo: Inserting the Data

Now you can try to insert a row of data with the following statement.

INSERT INTO dbo.Table1
 (id, SecretDeterministic, SecretRandomized)
VALUES (1, N'DeterSec01', N'RandomSec1');

You get the error 206, error text: “Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘AE_ColumnEncryptionKey’, column_encryption_key_database_name = ‘AEDemo’)”. SQL Server cannot encrypt or decrypt the data. You need to modify the data from a client application. You can do a limited set of operations on the table from SQL Server. For example, you can use the TRUNCATE TABLE statement on a table with AE columns.

I created a very simple client Windows Console application in Visual C#. The application actually just retrieves the keys and inserts a single row into the table created with the code above. Here is the C# code.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AEDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Data Source=localhost; “ +
              “Initial Catalog=AEDemo; Integrated Security=true; ” +
              “Column Encryption Setting=enabled";
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            if (args.Length != 3)
            {
                Console.WriteLine("Please enter a numeric “ + 
                                  “and two string arguments.");
                return;
            }
            int id = Int32.Parse(args[0]);
            {
                using (SqlCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = @"INSERT INTO dbo.Table1 “ +
                        “(id, SecretDeterministic, SecretRandomized)" +
                        " VALUES (@id, @SecretDeterministic, @SecretRandomized);";

                    SqlParameter paramid= cmd.CreateParameter();
                    paramid.ParameterName = @"@id";
                    paramid.DbType = DbType.Int32;
                    paramid.Direction = ParameterDirection.Input;
                    paramid.Value = id;
                    cmd.Parameters.Add(paramid);

                    SqlParameter paramSecretDeterministic = cmd.CreateParameter();
                    paramSecretDeterministic.ParameterName = 
                      @"@SecretDeterministic";
                    paramSecretDeterministic.DbType = DbType.String;
                    paramSecretDeterministic.Direction = ParameterDirection.Input;
                    paramSecretDeterministic.Value = "DeterSec1";
                    paramSecretDeterministic.Size = 10;
                    cmd.Parameters.Add(paramSecretDeterministic);

                    SqlParameter paramSecretRandomized = cmd.CreateParameter();
                    paramSecretRandomized.ParameterName =
                      @"@SecretRandomized";
                    paramSecretRandomized.DbType = DbType.String;
                    paramSecretRandomized.Direction = ParameterDirection.Input;
                    paramSecretRandomized.Value = "RandomSec1";
                    paramSecretRandomized.Size = 10;
                    cmd.Parameters.Add(paramSecretRandomized);

                    cmd.ExecuteNonQuery();
                }
            }
            connection.Close();
            Console.WriteLine("Row inserted successfully");            
        }
    }
}

You can run the C# code from Visual Studio in the debug mode or build the application. Then you can run AEDemo.exe application from the command prompt, or from SSMS in SQMCMD mode.

AE Demo: Reading the Data

After you run the application, you should try reading the data from the same session in SSMS you used to create the table.

SELECT *
FROM dbo.Table1;

You can see only encrypted data. Now open a second query window in SSMS. Right-click in this window and choose Connection, then Change Connection. In the Connection dialog, click the Options button at the bottom. Type AEDemo for the database name and then click the Additional Connection Parameters tab. In the text box, enter “Column Encryption Setting=enabled” (without double quotes). Then click Connect.

Try again to insert a row from SSMS. Use the following query.

INSERT INTO dbo.Table1
 (id, SecretDeterministic, SecretRandomized)
VALUES (2, N'DeterSec2', N'RandomSec2');

I got an error again. This SSMS version I am using still can’t parametrize ad-hoc inserts. However, let’s try to read the data with the following query.

SELECT * 
FROM dbo.Table1;

This time, the query works, and you get the following result:

Id  SecretDeterministic  SecretRandomized

— ——————– —————-

1   DeterSec1            RandomSec1

2   DeterSec2            RandomSec2

AE Limitations

You have already seen some limitations of the Always Encrypted, including:

• Only BIN2 collations are supported for strings
• You can index only columns with deterministic encryption, and use a limited set of T-SQL operations on those columns
• You cannot index columns with randomized encryption
• AE is limited to the Enterprise and Developer editions only
• Working with AE in SSMS can be painful

Please refer to Books Online for more detailed list of AE limitations. However, please also note the strengths of AE. It is simple to implement because it does not need modifications in an application, except the modification of connection strings. Data is encrypted end-to-end, from client memory through the network to database storage. Even DBAs can’t view the data within SQL Server only; even DBAs need access to the key storage outside SQL Server to read the CMK. AE and other encryption options in SQL Server provide a complete set of possibilities, and it is up to you and the business problem you are solving to select the appropriate method.

Conclusion

Transparent Data Encryption is extremely simple to use. However, the data protection is very limited. TDE protects the data at rest only. However, Always Encrypted is really a powerful feature. It is still not too complex to implement, and the data is completely protected. Not even a DBA can see it without having an access to the encryption keys. Hopefully, the limitations for AE, especially the collation limitation, will be removed in the future versions of SQL Server.