abstract

In the sharing of SQL Server Security Series monthly bulletins, we have shared one after another in the past: How to Use Symmetric Key to Implement SQL Server Column Encryption Technology,Using Asymmetric Key to Encrypt SQL Server Columns,Using Hybrid Key to Encrypt SQL Server Columns,Query Performance Problems Caused by Column Encryption Technology and Relevant Solutions,Row-level security solutions,Implementation of Privacy Data Column Coding Technology by SQL Server 2016 dynamic data masking and Using Certificate to Backup and Encrypt Database For these seven articles, click directly on the above articles to see the details. In this monthly report, we share the new features of SQL Server 2016, Always Encrypted technology.

Problem introduction

With the popularity of cloud computing, is there a way to ensure that the data stored in the cloud database will always be encrypted, even if the cloud service provider can not see the plaintext data in the database, in order to ensure the absolute security of data in the customer cloud database? The answer is yes, Always Encrypted, introduced by SQL Server 2016, which we will talk about today.
With SQL Server Always Encrypted, data is always encrypted. Only applications calling SQL Server can read, write and operate encrypted data, so that you can avoid database or operating system administrators from touching sensitive data of client applications. SQL Server 2016 Always Encrypted controls the client application by verifying the encryption key, which will never be transmitted to the remote SQL Server server server through the network. Therefore, the security of cloud database customer data is guaranteed to the greatest extent, even cloud service providers can not accurately know user data plaintext.

Concrete realization

Always Encrypted, a new feature introduced by SQL Server 2016, enables user data to be encrypted and decrypted on the application side. Therefore, it is always stored and read and written in the cloud side in an encrypted state, which maximizes user data security and completely solves the trust problem of customers to cloud service providers. Following are the detailed implementation steps of SQL Server 2016 Always Encrypted technology.

Create a test database

For testing convenience, we first created the test database Always Encrypted.

--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 column master key

Secondly, in the Always Encrypted database, we create the Column Master Key (CMK).

-- 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 column encryption keys

Then, we create the Column Encryption Key (abbreviated as 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 CMK and CEK

Next, we examine the column master key and the column encryption key that we just created, as follows:

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

Everything is normal, as shown in the following screenshot:

Of course, you can also use SSMS IDE to view Column Master Key and Column Encryption Key by:
Expand the database that needs to be checked - > Security - > Always Encrypted Keys - > Expand Column Master Keys and Column Encryption Keys. As shown in the following figure:

Create Always Encryped Test Table

Next, we create the Always Encrypted test table, which is coded as follows:

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

In the process of creating the Always Encrypted test table, for the encrypted fields, we specify:
Encryption types: DETERMINISTIC and RANDOMIZED.
Algorithms: AEAD_AES_256_CBC_HMAC_SHA_256 is a proprietary algorithm of Always Encrypted.
Encryption key: The name of the encryption key created.

Export server-side certificates

Finally, we export the certificate of the server to a file, as follows:
Control Panel -> Internet Options - > Content - > Certificates - > Export. As shown in the following figure:

Enter the private key protection password in the export wizard.

Select the storage path.

Finally, the derivation is successful.

Application-side testing

After the SQL Server server is configured, we need to import certificates at the test application side and then test the application.

Client Import Certificate

The client-side import certificate method is consistent with the server-side certificate export method entry by: Control Panel -> Internet Options -> Content -> Certificates -> Import. As shown in the following screenshot:

Then enter the encryption password of the private key file and import successfully.

Test application

We use VS to create a C
Note: Only. NET 4.6 and above support the SQL Server driver with Always Encrypted features, so make sure that your project Target framework is at least version. NET 4.6 by right-clicking on your project - > Properties - > in Application and switching your Target framework to. NET Framework 4.6.

For simplicity and convenience, we test the application directly on the SQL Server server side, so the connection string you see is the connection to the local SQL Server service. If you need to test remote SQL Server, modify the connection string. The entire 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;
                }
            }
        }
    }
}

In our application code, we only need to add Column Encryption Setting = Enabled to the connection string; attribute configuration, which supports the new feature Always Encrypted of SQL Server 2016, is very simple. For your convenience, I put this property configuration in the first place of the connection string, as shown in the following figure:

Run our test application and show the results as follows:

From the test results of the application, we can read and write Always Encrypted test tables normally, and the application works well. So, if we leave aside the application and use other ways to read and write the test table, what kind of data results can we see?

Test SSMS

Suppose we use SSMS as a test tool. First read 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 results are shown in the following screenshots:

Then, 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 errors will be 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'

The following screenshot:

Thus, we can't read and manipulate the plaintext data of the Always Encrypted table using methods other than the test application.

Analysis of test results

According to the test results of application reading and writing test and using SSMS to read and write Always Encrypted table directly, users can use the former to read and write the test table normally and work well, while the latter can not read the test manifestation text, only can see the encrypted ciphertext data of the test table, plus write operation to report errors directly.

Test application source code

If you need the source code for the test application in this article, please Click to download.

Final conclusion

In this monthly report, we share the principle and implementation of Always Encrypted, a new feature of SQL Server 2016, in order to ensure that the data stored in the cloud database will always be encrypted. Even cloud service providers can not see the plaintext data in the database, so as to ensure the absolute security of data in the client cloud database and solve the most important users in the cloud database scenario. Trust in cloud service providers.

Original link
This article is the original content of Yunqi Community, which can not be reproduced without permission.