在云计算大行其道的如今，有没有一种方法保证存储在云端的数据库中数据永远保持加密状态，即便是云服务提供商也看不到数据库中的明文数据，以此来保证客户云数据库中数据的绝对安全呢？答案是肯定的，就是我们今天将要谈到的SQL Server 2016引入的始终加密技术(Always Encrypted)。

使用SQL Server Always Encrypted，始终保持数据处于加密状态，只有调用SQL Server的应用才能读写和操作加密数据，如此您可以避免数据库或者操作系统管理员接触到客户应用程序敏感数据。SQL Server 2016 Always Encrypted通过验证加密密钥来实现了对客户端应用的控制，该加密密钥永远不会通过网络传递给远程的SQL Server服务端。因此，最大限度保证了云数据库客户数据安全，即使是云服务提供商也无法准确获知用户数据明文。

SQL Server 2016引入的新特性Always Encrypted让用户数据在应用端加密、解密，因此在云端始终处于加密状态存储和读写，最大限制保证用户数据安全，彻底解决客户对云服务提供商的信任问题。以下是SQL Server 2016 Always Encrypted技术的详细实现步骤。

为了测试方便，我们首先创建了测试数据库AlwaysEncrypted。

创建列主密钥

其次，在AlwaysEncrypted数据库中，我们创建列主密钥（Column Master Key，简写为CMK）。

  2. USE [AlwaysEncrypted]
  3. GO
  4. CREATE COLUMN MASTER KEY [AE_ColumnMasterKey]
  5. WITH
  6. (
  7.     KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
  8.     KEY_PATH = N'CurrentUser/My/C3C1AFCDA7F2486A9BBB16232A052A6A1431ACB0'
  9. )
  11. GO

然后，我们创建列加密密钥（Column Encryption Key，简写为CEK）。

  1. -- Step 3 - Create a column encryption key
  2. USE [AlwaysEncrypted]
  3. GO
  5. CREATE COLUMN ENCRYPTION KEY [AE_ColumnEncryptionKey]
  6. WITH VALUES
  7. (
  8.     COLUMN_MASTER_KEY = [AE_ColumnMasterKey],
  9.     ALGORITHM = 'RSA_OAEP',
  10.     ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006300330063003100610066006300640061003700660032003400380036006100390062006200620031003600320033003200610030003500320061003600610031003400330031006100630062003000956D4610BE7DAEFC2E1B08D557BFF9E33FF23896BD76BB33A84560F5E4BE174D8798D86CC963BA57867404945B166D756CE87AFC9EB29EEB9E26B08115724C1724DCD449D0D14D4D5C4601A631899C733C7646EB845A816A17DB1D400B7C341C2EF5838731583B1C51A457E14692532FD7059B7F0AFF3D89BDF86FB3BB18880F6B49CD2EA6F346BA5EE130FCFCA69A71523722F824CD14B3CE2C29C9E46074F2FE36265450A0424F390C2BC32B724FAB674E2B58DB16347B842597AFEBE983C7F4F51BCC088292219BD6F6E1F092BD77C5AD80331770E0B0B8BF6428D2719560AF56780ECE8805F7B425818F31CF54C84FF11114DB693B6CB7D499B1490B8E155749329C9A7AF4417E2A17D0EACA92CBB59A4EE314C54BCD83F80E8D6363F9CF66D8608772DCEB5D3FF4C8A131E21984C2370AB0788E38CB330C1D6190A7513BE1179432705C0C38B9430FC7A8D10BBDBDBA4AC7A7E24D2E257A0B8B79AC2B6D7E0C2F2056F58579E96009C488F2C1C691B3DC9E2F5D538D2E96BB4E8DB280F3C0461B18ADE30A3A5C5279C6861E3109C8EEFE4BC8192338137BBF7D5BFD64A689689B40B5E1FB7A157D06F6674C807515255C0F124ED866D9C0E5294759FECFF37AEEA672EF5C3A7649CAA8B55288526DF6EF8EB2D7485601E9A72CFA53D046E200320BAAD32AD559C644018964058BBE9BE5A2BAFB28E2FF7B37C85B49680F
  11. )
  13. GO

检查CMK和CEK

接下来，我们检查下刚才创建的列主密钥和列加密密钥，方法如下：

一切正常，如下截图所示：

当然，您也可以使用SSMS的IDE来查看Column Master Key和Column Encryption Key，方法是： 展开需要检查的数据库 -> Security -> Always Encrypted Keys -> 展开Column Master Keys和 Column Encryption Keys。如下图所示：

02.png

下一步，我们创建Always Encrypted测试表，代码如下：

  1. -- Step 5 -  Create a table with an encrypted column
  3. USE [AlwaysEncrypted]
  4. GO
  5. IF OBJECT_ID('dbo.CustomerInfo', 'U') IS NOT NULL
  6.     DROP TABLE dbo.CustomerInfo
  7. GO
  8. CREATE TABLE dbo.CustomerInfo
  9. (
  10. CustomerId        INT IDENTITY(10000,1)    NOT NULL PRIMARY KEY,
  11. CustomerName    NVARCHAR(100) COLLATE Latin1_General_BIN2 
  12.     ENCRYPTED WITH (
  13.         ENCRYPTION_TYPE = DETERMINISTIC, 
  14.         ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
  15.         COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
  16.     ) NOT NULL,
  17. CustomerPhone    NVARCHAR(11)  COLLATE Latin1_General_BIN2
  18.     ENCRYPTED WITH (
  19.     ENCRYPTION_TYPE = RANDOMIZED, 
  20.     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
  21.     COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey
  22.     ) NOT NULL
  23.  )
  24. ;
  25. GO

在创建Always Encrypted测试表过程中，对于加密字段，我们指定了：

 加密类型：DETERMINISTIC和RANDOMIZED。

 加密密钥：创建的加密密钥名字。

导出服务器端证书

最后，我们将服务端的证书导出成文件，方法如下： Control Panel –> Internet Options -> Content -> Certificates -> Export。如下图所示：

导出向导中输入私钥保护密码。

04.png

选择存放路径。

最后导出成功。

SQL Server服务端配置完毕后，我们需要在测试应用程序端导入证书，然后测试应用程序。

客户端导入证书

客户端导入证书方法与服务端证书导出方法入口是一致的，方法是：Control Panel –> Internet Options -> Content -> Certificates -> Import。如下截图所示：

06.png

然后输入私钥文件加密密码，导入成功。

测试应用程序

我们使用VS创建一个C#的Console Application做为测试应用程序，使用NuGet Package功能安装Dapper，做为我们SQL Server数据库操作的工具。 注意：仅.NET 4.6及以上版本支持Always Encrypted特性的SQL Server driver，因此，请确保您的项目Target framework至少是.NET 4.6版本，方法如下：右键点击您的项目 -> Properties -> 在Application中，切换你的Target framework为.NET Framework 4.6。

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. using Dapper;
  7. using System.Data;
  8. using System.Data.SqlClient;
  10. namespace AlwaysEncryptedExample
  11. {
  12.     public class AlwaysEncrypted
  13.     {
  14.         public static readonly string CONN_STRING = "Column Encryption Setting = Enabled;Server=.,1433;Initial Catalog=AlwaysEncrypted;Trusted_Connection=Yes;MultipleActiveResultSets=True;";
  15.         public static void Main(string[] args)
  16.             List<Customer> Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
  18.             // there is no record
  19.             if(Customers.Count == 0)
  20.             {
  21.                 Console.WriteLine("************There is no record.************");
  22.                 string execSql = @"INSERT INTO dbo.CustomerInfo VALUES (@customerName, @cellPhone);";
  24.                 Console.WriteLine("************Insert some records.************");
  27.                 dp.Add("@customerName", "CustomerA", dbType: DbType.String, direction: ParameterDirection.Input, size: 100);
  28.                 dp.Add("@cellPhone", "13402871524", dbType: DbType.String, direction: ParameterDirection.Input, size: 11);
  30.                 DoExecuteSql(execSql, dp);
  32.                 Console.WriteLine("************re-generate records.************");
  33.                 Customers = QueryCustomerList<Customer>(@"SELECT TOP 3 * FROM dbo.CustomerInfo WITH(NOLOCK)");
  34.             }
  35.             else
  36.             {
  37.                 Console.WriteLine("************There are a couple of records.************");
  38.             }
  40.             foreach(Customer cus in Customers)
  41.             {
  42.                 Console.WriteLine(string.Format("Customer name is {0} and cell phone is {1}.", cus.CustomerName, cus.CustomerPhone));
  43.             }
  45.             Console.ReadKey();
  46.         }
  48.         public static List<T> QueryCustomerList<T>(string queryText)
  49.         {
  50.             // input variable checking
  51.             if (queryText == null || queryText == "")
  52.             {
  53.                 return new List<T>();
  54.             }
  55.             try
  56.             {
  57.                 using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
  58.                 {
  59.                     // if connection is closed, open it
  60.                     if (dbConn.State == ConnectionState.Closed)
  61.                     {
  62.                         dbConn.Open();
  63.                     }
  65.                     // return the query result data set to list.
  66.                     return dbConn.Query<T>(queryText, commandTimeout: 120).ToList();
  67.                 }
  68.             }
  69.             catch (Exception ex)
  70.             {
  71.                 Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", queryText, ex.Message, ex.StackTrace);
  72.                 // return empty list
  73.                 return new List<T>();
  74.             }
  75.         }
  77.         public static bool DoExecuteSql(String execSql, object parms)
  78.         {
  79.             bool rt = false;
  81.             // input parameters checking
  82.             if (string.IsNullOrEmpty(execSql))
  83.             {
  84.                 return rt;
  85.             }
  87.             if (!string.IsNullOrEmpty(CONN_STRING))
  88.             {
  89.                 // try to add event file target
  90.                 try
  91.                 {
  92.                     using (IDbConnection dbConn = new SqlConnection(CONN_STRING))
  93.                     {
  94.                         // if connection is closed, open it
  95.                         {
  96.                             dbConn.Open();
  97.                         }
  99.                         var affectedRows = dbConn.Execute(execSql, parms);
  101.                         rt = (affectedRows > 0);
  102.                     }
  103.                 }
  104.                 catch (Exception ex)
  105.                 {
  106.                     Console.WriteLine("Failed to execute {0} with error message : {1}, StackTrace: {2}.", execSql, ex.Message, ex.StackTrace);
  107.                 }
  108.             }
  110.             return rt;
  111.         }
  113.         public class Customer
  114.         {
  115.             private int customerId;
  116.             private string customerName;
  117.             private string customerPhone;
  119.             public Customer(int customerId, string customerName, string customerPhone)
  120.             {
  121.                 this.customerId = customerId;
  122.                 this.customerName = customerName;
  123.                 this.customerPhone = customerPhone;
  124.             }
  126.             public int CustomerId
  127.             {
  128.                 get
  129.                 {
  130.                     return customerId;
  131.                 }
  133.                 set
  134.                 {
  135.                     customerId = value;
  136.                 }
  137.             }
  139.             public string CustomerName
  140.             {
  141.                 get
  142.                 {
  143.                     return customerName;
  144.                 }
  146.                 set
  147.                 {
  148.                     customerName = value;
  149.                 }
  150.             }
  152.             public string CustomerPhone
  153.             {
  154.                 get
  155.                 {
  156.                     return customerPhone;
  157.                 }
  159.                 set
  160.                 {
  161.                     customerPhone = value;
  162.                 }
  163.             }
  164.         }
  165.     }
  166. }

我们在应用程序代码中，仅需要在连接字符串中添加Column Encryption Setting = Enabled;属性配置，即可支持SQL Server 2016新特性Always Encrypted，非常简单。为了方便大家观察，我把这个属性配置放到了连接字符串的第一个位置，如下图所示：

08.png

运行我们的测试应用程序，展示结果如下图所示：

从应用程序的测试结果来看，我们可以正常读、写Always Encrypted测试表，应用程序工作良好。那么，假如我们抛开应用程序使用其它方式能否读写该测试表，看到又是什么样的数据结果呢？

测试SSMS

假设，我们使用SSMS做为测试工具。首先读取Always Encrypted测试表中的数据：

展示结果如下截图：

10.png

然后，使用SSMS直接往测试表中插入数据：

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

会报告如下错误：

  1. Msg 206, Level 16, State 2, Line 74

如下截图：

由此可见，我们无法使用测试应用程序以外的方法读取和操作Always Encrypted表的明文数据。

测试结果分析

从应用程序读写测试和使用SSMS直接读写Always Encrypted表的测试结果来看，用户可以使用前者正常读写测试表，工作良好；而后者无法读取测试表明文，仅可查看测试表的加密后的密文数据，加之写入操作直接报错。

测试应用源代码

本期月报，我们分享了SQL Server 2016新特性Always Encrypted的原理及实现方法，以此来保证存储在云端的数据库中数据永远保持加密状态，即便是云服务提供商也看不到数据库中的明文数据，以此来保证客户云数据库的数据绝对安全，解决了云数据库场景中最重要的用户对云服务提供商信任问题。