Enable Always Encrypted for Your MSSQL Databases

In this tutorial, you will learn how to enable Always Encrypted to ensure that the data stored in your SQL databases on the cloud is continually encrypted.

By Feng Yi.

Nowadays, with the ubiquity of cloud computing, having a wide application across several major industries. It's important that we are able to find a way to ensure that the data stored in the cloud is continually and constantly encrypted. Moreover, it's also important that data is kept private, and even the cloud service providers cannot see the plaintext data stored in the database, thus guarantying the absolute security and privacy of customer data in the cloud database. Well, one way to go about achieving both of these things is through the Always Encrypted feature introduced in Microsoft's SQL Server 2016.

Always Encrypted in SQL Server functions to keep data constantly and continually encrypted. Only applications that call SQL Server can read/write and/or operate encrypted data. Always Encrypted can prevent your database or OS administrators from accessing the sensitive data of customer applications. Always Encrypted in SQL Server 2016 controls client applications by verifying encryption keys, which are not transmitted over the network to a remote SQL Server. Therefore, Always Encrypted provides the maximum level of customer data security for cloud databases. With Always Encrypted on and running, even your cloud service providers cannot obtain the plaintext of customer data.

Procedure

You can follow the steps below to enable the Always Encrypted feature.

Create a Test Database

For this tutorial, to keep things simple, let's create a test database called AlwaysEncrypted first for demonstration purposes. You can do so with the following code script:

--Step 1 - Create MSSQL sample database
USE master
GO
IF DB_ID('AlwaysEncrypted') IS NULL
    CREATE DATABASE [AlwaysEncrypted];
GO

-- Not 100% require, but option adviced.
ALTER DATABASE [AlwaysEncrypted] COLLATE Latin1_General_BIN2;

Create a Column Master Key

Next, you'll want to create a column master key (CMK) in the AlwaysEncrypted database.

-- Step 2 - Create a column master key
USE [AlwaysEncrypted]
GO
CREATE COLUMN MASTER KEY [AE_ColumnMasterKey]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/C3C1AFCDA7F2486A9BBB16232A052A6A1431ACB0'
)

GO

Create a Column Encryption Key

Next, create a column encryption key (CEK).

-- Step 3 - Create a column encryption key
USE [AlwaysEncrypted]
GO

CREATE COLUMN ENCRYPTION KEY [AE_ColumnEncryptionKey]
WITH VALUES
(
    COLUMN_MASTER_KEY = [AE_ColumnMasterKey],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006300330063003100610066006300640061003700660032003400380036006100390062006200620031003600320033003200610030003500320061003600610031003400330031006100630062003000956D4610BE7DAEFC2E1B08D557BFF9E33FF23896BD76BB33A84560F5E4BE174D8798D86CC963BA57867404945B166D756CE87AFC9EB29EEB9E26B08115724C1724DCD449D0D14D4D5C4601A631899C733C7646EB845A816A17DB1D400B7C341C2EF5838731583B1C51A457E14692532FD7059B7F0AFF3D89BDF86FB3BB18880F6B49CD2EA6F346BA5EE130FCFCA69A71523722F824CD14B3CE2C29C9E46074F2FE36265450A0424F390C2BC32B724FAB674E2B58DB16347B842597AFEBE983C7F4F51BCC088292219BD6F6E1F092BD77C5AD80331770E0B0B8BF6428D2719560AF56780ECE8805F7B425818F31CF54C84FF11114DB693B6CB7D499B1490B8E155749329C9A7AF4417E2A17D0EACA92CBB59A4EE314C54BCD83F80E8D6363F9CF66D8608772DCEB5D3FF4C8A131E21984C2370AB0788E38CB330C1D6190A7513BE1179432705C0C38B9430FC7A8D10BBDBDBA4AC7A7E24D2E257A0B8B79AC2B6D7E0C2F2056F58579E96009C488F2C1C691B3DC9E2F5D538D2E96BB4E8DB280F3C0461B18ADE30A3A5C5279C6861E3109C8EEFE4BC8192338137BBF7D5BFD64A689689B40B5E1FB7A157D06F6674C807515255C0F124ED866D9C0E5294759FECFF37AEEA672EF5C3A7649CAA8B55288526DF6EF8EB2D7485601E9A72CFA53D046E200320BAAD32AD559C644018964058BBE9BE5A2BAFB28E2FF7B37C85B49680F
)

GO

Check the CMK and CEK

Check the column master key (CMK) and column encryption key (CEK) that we have just created:

-- Step 4 - CMK & CEK Checking
select * from sys.column_master_keys
select * from sys.column_encryption_keys
select * from sys.column_encryption_key_values

As shown in the following screenshot, everything is normal.

Of course, you can also use the SSMS IDE to check the CMK and CEK.

To do that, expand the database to be checked > Security > Always Encrypted Keys > Column Master Keys and Column Encryption Keys. See the following figure for reference.

Create an Always Encrypted Test Table

Follow the provided code to create an Always Encrypted test table:

-- Step 5 -  Create a table with an encrypted column

USE [AlwaysEncrypted]
GO
IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
    DROP TABLE dbo.CustomerInfo
GO
CREATE TABLE dbo.CustomerInfo
(
CustomerId        INT IDENTITY(10000,1)    NOT NULL PRIMARY KEY,
CustomerName    NVARCHAR(100) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (
        ENCRYPTION_TYPE = DETERMINISTIC, 
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
        COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
    ) NOT NULL,
CustomerPhone    NVARCHAR(11)  COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (
    ENCRYPTION_TYPE = RANDOMIZED, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
    COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
    ) NOT NULL
 )
;
GO

When creating the Always Encrypted test table, specify the following parameters for the encryption field:

Encryption type: DETERMINISTIC and RANDOMIZED
Algorithm: AEAD_AES_256_CBC_HMAC_SHA_256 (a special algorithm for Always Encrypted)
Encryption key: The name of the created encryption key

Export the Server Certificate

Export the server-side certificate as a file:

Control Panel > Internet Options > Content > Certificates > Export¡. See the following figure for reference.

Enter a password to protect your private key in the Certificate Export Wizard.

Select a storage path.

The certificate is finally exported.

Application-side Testing

After configuring the SQL Server on the server side, import the certificate for the application to be tested and start to test the application.

Import the Certificate on Client

The steps to import the certificate on client are similar to exporting the server-side certificate are: Control Panel > Internet Options > Content > Certificates -> Import¡. See the following figure.

Then, enter the encryption password for the private key file. The file should be successfully exported now.

Test the Application

We can use Visual Studio to create a C# Console Application as the test Application and use the NuGet Package feature to install Dapper as the tool for performing SQL Server database operations.

Note: The SQL Server driver for Always Encrypted is supported only in .NET Framework 4.6 or later, so you'll need to make sure that the Target framework of your project is at least .NET Framework 4.6 by clicking your project > Properties > Application. If the Target framework shows a .NET Framework version older than for 4.6, change it to .NET Framework 4.6 or later.

Next, in this tutorial, for the sake of simplicity and convenience, we will test the application directly on the server side of the SQL Server. Therefore, the connection string that you will see will connect to the local SQL Server service. If you want to test a remote SQL Server, modify the connection string. The complete test application code is as follows:

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

namespace AlwaysEncryptedExample
{
    public class AlwaysEncrypted
    {
        public static readonly string CONN_STRING = "Column Encryption Setting = Enabled;Server=.,1433;Initial Catalog=AlwaysEncrypted;Trusted_Connection=Yes;MultipleActiveResultSets=True;";
        public static void Main(string[] args)
        {
            List<Customer> Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");

            // there is no record
            if(Customers.Count == 0)
            {
                Console.WriteLine("************There is no record.************");
                string execSql = @"INSERT INTO dbo.CustomerInfo VALUES (@customerName, @cellPhone);";

                Console.WriteLine("************Insert some records.************");

                DynamicParameters dp = new DynamicParameters();
                dp.Add("@customerName", "CustomerA", dbType: DbType.String, direction: ParameterDirection.Input, size: 100);
                dp.Add("@cellPhone", "13402871524", dbType: DbType.String, direction: ParameterDirection.Input, size: 11);

                DoExecuteSql(execSql, dp);

                Console.WriteLine("************re-generate records.************");
                Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
            }
            else
            {
                Console.WriteLine("************There are a couple of records.************");
            }

            foreach(Customer cus in Customers)
            {
                Console.WriteLine(string.Format("Customer name is {0} and cell phone is {1}.", cus.CustomerName, cus.CustomerPhone));
            }

            Console.ReadKey();
        }

        public static List<T> QueryCustomerList<T>(string queryText)
        {
            // input variable checking
            if (queryText == null || queryText == "")
            {
                return new List<T>();
            }
            try
            {
                using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
                {
                    // if connection is closed, open it
                    if (dbConn.State == ConnectionState.Closed)
                    {
                        dbConn.Open();
                    }

                    // return the query result data set to list.
                    return dbConn.Query<T>(queryText, commandTimeout: 120).ToList();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", queryText, ex.Message, ex.StackTrace);
                // return empty list
                return new List<T>();
            }
        }

        public static bool DoExecuteSql(String execSql, object parms)
        {
            bool rt = false;

            // input parameters checking
            if (string.IsNullOrEmpty(execSql))
            {
                return rt;
            }

            if (!string.IsNullOrEmpty(CONN_STRING))
            {
                // try to add event file target
                try
                {
                    using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
                    {
                        // if connection is closed, open it
                        if (dbConn.State == ConnectionState.Closed)
                        {
                            dbConn.Open();
                        }

                        var affectedRows = dbConn.Execute(execSql, parms);

                        rt = (affectedRows > 0);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", execSql, ex.Message, ex.StackTrace);
                }
            }

            return rt;
        }

        public class Customer
        {
            private int customerId;
            private string customerName;
            private string customerPhone;

            public Customer(int customerId, string customerName, string customerPhone)
            {
                this.customerId = customerId;
                this.customerName = customerName;
                this.customerPhone = customerPhone;
            }

            public int CustomerId
            {
                get
                {
                    return customerId;
                }

                set
                {
                    customerId = value;
                }
            }

            public string CustomerName
            {
                get
                {
                    return customerName;
                }

                set
                {
                    customerName = value;
                }
            }

            public string CustomerPhone
            {
                get
                {
                    return customerPhone;
                }

                set
                {
                    customerPhone = value;
                }
            }
        }
    }
}

Next, to support the Always Encrypted feature in SQL Server 2016, simply add the property configuration Column Encryption Setting = Enabled; to the application code. To make this clear, I put this property configuration at the beginning of the connection string:

Then, after doing this, run the test application, and the following result will be returned:

The application test result shows that the Always Encrypted test table can be read and written normally and that the test application works well. But the question remains: What if we use another method to read/write data from/to the test table instead of using the application?

Test SSMS

Suppose that we use Microsoft's SQL Server Management Studio (SSMS) as the test tool, for example. Then, first, look over the data in the Always Encrypted test table:

-- try to read Always Encrypted table and it'll show us encrypted data instead of the plaintext.
USE [AlwaysEncrypted]
GO
SELECT * FROM dbo.CustomerInfo WITH(NOLOCK)

The result would be as follows:

Then, you can use SSMS to insert data directly into the test table:

-- try to insert records to encrypted table, will be fail.
USE [AlwaysEncrypted]
GO 
INSERT INTO dbo.CustomerInfo 
VALUES ('CustomerA','13402872514'),('CustomerB','13880674722')
GO

The following error is reported:

Msg 206, Level 16, State 2, Line 74
Operand type clash: varchar is incompatible with varchar(8000) 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 = 'AlwaysEncrypted') collation_name = 'Chinese_PRC_CI_AS'

Now consider the following screenshot.

This indicates that we cannot use any method other than a test application to read and operate the plaintext data in an Always Encrypted table.

Analyze the Test Results

After comparing the test results of using the application and using SSMS for direct access to the Always Encrypted test table, we know that the former method allows normal table writes/reads. The latter cannot read the plaintext data in the test table and can only be used to view the ciphertext data after the test table is encrypted. When the latter is used, a write operation fails with an error.

Source Code of the Test Application

To obtain the source code of the test application used in this article, click here to download.

Summary

In this tutorial, you have seen the principle behind and implementation of the new feature in SQL Server 2016 - Always Encrypted. This feature ensures that the data stored in databases on the cloud is always encrypted and that even cloud service providers cannot see the plaintext data in databases. Therefore, Always Encrypted ensures absolute security of customer data in cloud databases and alleviates suspicion towards the credibility of cloud service providers, which is a critical issue in cloud database scenarios.

Community

Enable Always Encrypted for Your MSSQL Databases

Procedure

Create a Test Database

Create a Column Master Key

Create a Column Encryption Key

Check the CMK and CEK

Create an Always Encrypted Test Table

Export the Server Certificate

Application-side Testing

Import the Certificate on Client

Test the Application

Test SSMS

Analyze the Test Results

Source Code of the Test Application

Summary

Read previous post:

Read next post:

Cherish Wang

You may also like

Comments

Cherish Wang

Related Products

Database Overview

ApsaraDB RDS for SQL Server

Database Security Solutions

Web Hosting Solution