对于想了解MSSQL-最佳实践-AlwaysEncrypted的读者，本文将是一篇不可错过的文章，并且为您提供关于alteruserxxwithencryptedpassword''123''与alt

对于想了解MSSQL-最佳实践-Always Encrypted的读者，本文将是一篇不可错过的文章，并且为您提供关于alter user xx with encrypted password ''123'' 与 alter user xx with password ''123'' 区别如何看得出来、Always Encrypted/SS 2017 和 ASP Classic，除了读取操作之外的任何问题、asp.net – 将Always Encrypted Certificate放在IIS 7.5 Web服务器上的位置？、avformat_open_input() always return -13的有价值信息。

本文目录一览：

• MSSQL-最佳实践-Always Encrypted
• alter user xx with encrypted password ''123'' 与 alter user xx with password ''123'' 区别如何看得出来
• Always Encrypted/SS 2017 和 ASP Classic，除了读取操作之外的任何问题
• asp.net – 将Always Encrypted Certificate放在IIS 7.5 Web服务器上的位置？
• avformat_open_input() always return -13

MSSQL-最佳实践-Always Encrypted

摘要

在SQL Server安全系列专题月报分享中，往期我们已经陆续分享了：如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥实现SQL Server列加密、使用混合密钥实现SQL Server列加密技术、列加密技术带来的查询性能问题以及相应解决方案、行级别安全解决方案、SQL Server 2016 dynamic data masking实现隐私数据列打码技术和使用证书做数据库备份加密这七篇文章，直接点击以上文章前往查看详情。本期月报我们分享SQL Server 2016新特性Always Encrypted技术。

问题引入

在云计算大行其道的如今，有没有一种方法保证存储在云端的数据库中数据永远保持加密状态，即便是云服务提供商也看不到数据库中的明文数据，以此来保证客户云数据库中数据的绝对安全呢？答案是肯定的，就是我们今天将要谈到的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。

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

创建列主密钥

其次，在AlwaysEncrypted数据库中，我们创建列主密钥（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

创建列加密密钥

然后，我们创建列加密密钥（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

检查CMK和CEK

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

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

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

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

创建Always Encryped测试表

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

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

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

• 加密类型：DETERMINISTIC和RANDOMIZED。
• 算法：AEAD_AES_256_CBC_HMAC_SHA_256是Always Encrypted专有算法。
• 加密密钥：创建的加密密钥名字。

导出服务器端证书

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

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

选择存放路径。

最后导出成功。

应用程序端测试

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

客户端导入证书

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

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

测试应用程序

我们使用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。

为了简单方便，我们直接在SQL Server服务端测试应用程序，因此您看到的连接字符串是连接本地SQL Server服务。如果您需要测试远程SQL Server，修改连接字符串即可。整个测试应用程序代码如下：

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

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

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

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

测试SSMS

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

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

展示结果如下截图：

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

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

会报告如下错误：

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

如下截图：

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

测试结果分析

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

测试应用源代码

如果您需要本文的测试应用程序源代码，请点击下载。

最后总结

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

---

本文作者：风移

阅读原文

本文为云栖社区原创内容，未经允许不得转载。

alter user xx with encrypted password ''123'' 与 alter user xx with password ''123'' 区别如何看得出来

我有一个困惑，我在修改用户密码时，
我使用下面两句都没有问题，不知道他们有什么区别？
alter user xx with password ‘123’
alter user xx with encrypted password ‘123’

Always Encrypted/SS 2017 和 ASP Classic，除了读取操作之外的任何问题

如何解决Always Encrypted/SS 2017 和 ASP Classic，除了读取操作之外的任何问题

始终加密/SS 2017 和 ASP Classic。对于那些仍在管理 ASP/VBscript 应用程序的少数人来说，这篇文章让我们了解了我现在所处的位置...... How to read SQL Always-encrypted column from classic ASP

我的 ODBC Driver 17 在我的 ASP 的指示下成功解密了我的 AE 加密列！

但是，到目前为止，我在尝试执行以下操作时遇到了以下错误（显然是相当常见的错误）：

1. 使用我现有的 vbScript/sql 或
“验证”用户
2. 使用我现有的 vbScript/sql“添加记录”或
3. 使用 SSMS“更改加密列的大小”。

Microsoft OLE DB Provider for ODBC Drivers 错误“80040e07” [Microsoft][ODBC Driver 17 for sql Server][sql Server]操作数类型冲突：varchar 与使用 (encryption_type = ''DETERMINISTIC'',encryption_algorithm_name = ''AEAD_AES_256_CBC_HMAC_SHA_256'',column_encryption_key_name_key_name_key_data_encryption_key_name_加密的 varchar(8000) 不兼容''XXXX_db'') collat​​ion_name = ''sql_latin1_General_CP1_CI_AS''

在确认 AE 应该通过多种资源支持我的需求后（“使用确定性加密允许相等搜索”），

我认为这与以下内容有关：

1. AE 将加密列的“排序规则”更改为 latin1_General_BIN2...

2. 我的加密列“nvarchar(15)”可能在我声明时被解释为 varchar(8000) (15)...

3. 一些基本的东西，当我的 ASP 试图呈现原始数据时，用我的驱动程序加密，进行相等比较或插入到加密列......

我在 AE 上花了很多时间，非常感谢任何指导或支持？总之，帮助。

解决方法

asp.net – 将Always Encrypted Certificate放在IIS 7.5 Web服务器上的位置？

avformat_open_input() always return -13

我在调用avformat_open_input的时候，一直返回－13错误，如下代码：

const char *url = "/mnt/sdcard/xpg.mp4";
    av_register_all();

    AVFormatContext *pFormatCtx = NULL;
    int ret = avformat_open_input(&pFormatCtx, url, NULL, NULL);

    LOGE("ret:%d", ret);

如 http://stackoverflow.com/questions/21549829/ffmpeg-avformat-open-input-returned-error 中的解释，－13是权限相关的错误，于是在AndroidManifest.xml中加入以下权限即可：

<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

关于MSSQL-最佳实践-Always Encrypted的问题我们已经讲解完毕，感谢您的阅读，如果还想了解更多关于alter user xx with encrypted password ''123'' 与 alter user xx with password ''123'' 区别如何看得出来、Always Encrypted/SS 2017 和 ASP Classic，除了读取操作之外的任何问题、asp.net – 将Always Encrypted Certificate放在IIS 7.5 Web服务器上的位置？、avformat_open_input() always return -13等相关内容，可以在本站寻找。

本文标签：

• MSSQL
• Always_Encrypted
• 数据库安全
• 加密实践