Always Encrypted

SQL Server 2016 and 2017 introduce a new level of encryption, 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, a DBA can't also 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 who manage the data and the users who own the data.

You need two keys for AE. 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 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 the column encryption keys are stored, together with the information about the location of the column master keys. CEKs are never stored as plain text in a database. CMKs are, as mentioned, actually stored in external trusted key stores.

An application can use AE keys and encryption by using an AE-enabled driver, such as .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. Next the driver caches the CEK in order to speed up the next usage of the same CEK. The following figure shows the process graphically:

Always Encrypted process

The preceding figure represents the whole process in these steps:

  1. The client application creates a parameterized query.
  2. The 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.

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 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.

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 the SSMS GUI. In Object Explorer, refresh the database folder to see the AEDemo database. Expand this database folder, expand the Security subfolder and the Always Encrypted Keys subfolder, right-click on 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, as shown in the following screenshot. Then click OK:

Creating a CMK

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 on 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; 
GO

Now try to create a table with one deterministic encryption column and one randomized encryption column. My database used the default SQL_Latin1_General_CP1_CI_AS collation:

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

The previous statement produced an error number 33289, which tells me that I cannot create an encrypted column for character strings that use a non-BIN2 collation. Currently, only new binary collations (that is, collations with the BIN2 suffix) are supported by AE.

So let's try to create the table again, this time with correct collations for 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 
); 
GO

This time, table creation succeeds. 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 error 206 with the following 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 it from a client application. You can perform 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 that was created with the previous code. Here is the C# code. The first part of the code just defines the namespaces used in the application or added by default in a new project in Visual Studio 2015:

using System; 
using System.Collections.Generic; 
using System.Data; 
using System.Data.SqlClient; 
using System.Linq; 
using System.Text; 
using System.Threading.Tasks;

The next part of the code defines the connection string to my local SQL Server. Please note the new connection string property in .NET 4.6 and exceeding ones, the Column Encryption Setting=enabled property. Then the application opens the connection:

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();

The next part is just a simple check to see whether the three arguments were passed. Please note that in a real application you should use a try-catch block when parsing the first argument to an integral number. The following code is used to check whether the three arguments were passed correctly:

if (args.Length != 3) 
    { 
       Console.WriteLine("Please enter a numeric " +  
         "and two string arguments."); 
       return; 
    } 
int id = Int32.Parse(args[0]);

The next part of the code defines the parameterized INSERT statement and executes it, as shown in the following code:

{ 
     using (SqlCommand cmd = onnection.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 = "DeterSec01"; 
  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(); 
} 
}

Finally, the code closes the connection and informs you that a row was inserted successfully:

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

If you don't have Visual Studio installed, you can just run the AEDemo.exe application provided with the code examples associated with this book. As mentioned, the application inserts a single row into a previously created table with two AE-enabled columns. Please run the application from SSMS in SQLCMD mode, as the following example shows; there is no prompting for values in the application:

!!C:SQL2017DevGuideAEDemo 1 DeterSec01 RandomSec1 
!!C:SQL2017DevGuideAEDemo 2 DeterSec02 RandomSec2

Now try to read the data from the same session in SSMS that you used to create the table, using the following code:

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 in AEDemo for the database name and then click the Additional Connection Parameters tab. In the text box, enter Column Encryption Setting=enabled (without the double quotes). Then click on Connect.

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

INSERT INTO dbo.Table1
 (id, SecretDeterministic, SecretRandomized)
VALUES (3, N'DeterSec03', N'RandomSec3');

You get error message 206 again. You need to use a parameterized query. SSMS from version 17.0 adds support for inserting, updating, and filtering by values stored in columns that use Always Encrypted from a Query Editor window. However, parameterization is disabled by default. You need to enable it for your current session:

  • From the Query menu, select Query Options…
  • Navigate to Execution | Advanced
  • Select the Enable Parameterization for Always Encrypted check box and click OK.

In addition, you need to declare and initialize the variables in the same statement (with inline initialization). The initialization value must be a single literal. The following code shows an example:

DECLARE @p1 NVARCHAR(10) = N'DeterSec03';
DECLARE @p2 NVARCHAR(10) = N'RandomSec3';
INSERT INTO dbo.Table1
 (id, SecretDeterministic, SecretRandomized)
VALUES (3, @p1, @p2);

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   DeterSec01           RandomSec1
2   DeterSec02           RandomSec2
3   DeterSec03           RandomSec3

You can now close this query window and continue in the first one. Try to index the column with deterministic encryption. The following code creates a nonclustered index on the dbo.Table1 with the SecretDeterministic column used as the key:

CREATE NONCLUSTERED INDEX NCI_Table1_SecretDeterministic 
ON dbo.Table1(SecretDeterministic); 
GO

The creation succeeds. Now try to also create an index on the column with randomized encryption, as shown in the following code:

CREATE NONCLUSTERED INDEX NCI_Table1_SecretRandomized 
ON dbo.Table1(SecretRandomized); 
GO

This time you get an error message telling you that you cannot index a column with randomized encryption. Finally, execute the following code to clean up your SQL Server instance:

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

You have already seen some of the limitations of AE, including:

  • Only BIN2 collations are supported for strings
  • You can only index 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

Refer to Books Online for a more detailed list of AE's 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 for connection strings. Data is encrypted end-to-end, from client memory, through network-to-database storage. Even DBAs can't view the data within SQL Server only; they 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 to select the appropriate method for the business problem you are solving.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.31.22