SQL Server 2016新特性: Always Encrypted (始终加密)

原创 2016年06月02日 08:43:36


    数据,尤其是机密数据的安全性,是我们设计和开发系统所要考虑的。SQL Server 2016引入了加密数据列的新方式,即始终加密(Always Encrypted)。有了始终加密,数据就可以通过ADO.NET在应用层进行加密,这意味着,在数据通过网络发送到SQL Server之前,你可以通过.NET应用程序来加密你的机密数据。这个过程中,网络传输的是密文,存储在DB里的数据也是密文,对我们的数据起到了一定程度的保护作用。下面看看如何使用这一功能(Framework4.6及其以上版本支持该功能)


1新建列主密钥(Column Master Key


2 新建列加密密钥(Column Encryption Key


3新建测试表,注意这里指定了机密类型、算法及加密密钥。


CREATE TABLE [dbo].[Patients](
[PatientId] [int] IDENTITY(1,1), 
 [SSN] [nvarchar](11) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL, 
 [MiddleName] [nvarchar](50) NULL,
[StreetAddress] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [char](5) NULL,
[State] [char](2) NULL,
[BirthDate] [date] 
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )

4新建SP,用于Insert数据

CREATE PROCEDURE Insert_Always_Encrypted (
@SSN NVARCHAR(11),
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate date 
)
AS 
INSERT INTO dbo.Patients
   (SSN, FirstName,LastName, BirthDate)
VALUES (@SSN,@FirstName,@LastName,@BirthDate);

5导出服务器端的证书




6在客户端导入该证书

7. 测试SP,直接在SSMS里执行是失败的


8 准备测试的应用程式(注意这里的连接字符串里增加了选项:Column Encryption Setting = Enabled;


最后附上C#测试源码:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

// Demo of using Always Encrypted Columns
class AlwaysEncryptedDemo
{
    SqlConnection conn;
    public AlwaysEncryptedDemo()
    {
        // Instantiate the connection
        conn = new SqlConnection(
          "data source=172.19.121.150;initial catalog=imoltp;integrated security = False; Column Encryption Setting = Enabled; User ID = sa; Password = sa9; ");
    }

    // call methods that demo Always Encrypted
    static void Main()
    {
        AlwaysEncryptedDemo scd = new AlwaysEncryptedDemo();
        scd.Insertdata();
        scd.Selectdata();
    }

    public void Insertdata()
    {
        try
        {
            // Open the connection for Insertion 
            conn.Open();

            // Constructed command to execute stored proceudre
            string insertString = @"dbo.Insert_Always_Encrypted";

            // Declare variable tho hold insdert command
            SqlCommand icmd = new SqlCommand(insertString, conn);

            //set command type to stored procedure
            icmd.CommandType = CommandType.StoredProcedure;

            // Set value of SSN
            SqlParameter
            paramSSN = icmd.CreateParameter();
            paramSSN.ParameterName = @"@SSN";
            paramSSN.DbType = DbType.String;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Value = "555-55-5555";
            paramSSN.Size = 11;
            icmd.Parameters.Add(paramSSN);

            // Set value of FirstName
            SqlParameter paramFirstName = icmd.CreateParameter();
            paramFirstName.ParameterName = @"@FirstName";
            paramFirstName.DbType = DbType.AnsiStringFixedLength; ;
            paramFirstName.Direction = ParameterDirection.Input;
            paramFirstName.Value = "Greg";
            icmd.Parameters.Add(paramFirstName);

            // Set value of LastName
            SqlParameter paramLastName = icmd.CreateParameter();
            paramLastName.ParameterName = @"@LastName";
            paramLastName.DbType = DbType.AnsiStringFixedLength; ;
            paramLastName.Direction = ParameterDirection.Input;
            paramLastName.Value = "Larsen";
            icmd.Parameters.Add(paramLastName);

            // Set value of Birth Date
            SqlParameter
            paramBirthdate = icmd.CreateParameter();
            paramBirthdate.ParameterName = @"@BirthDate";
            paramBirthdate.SqlDbType = SqlDbType.Date;
            paramBirthdate.Direction = ParameterDirection.Input;
            paramBirthdate.Value = "2015-01-02";
            icmd.Parameters.Add(paramBirthdate);

            // Exexute Insert 
            icmd.ExecuteNonQuery();
            MessageBox.Show("Inserted Demo Record With BirthDate=" + paramBirthdate.Value + "SSN=" + paramSSN.Value);

        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
    public void Selectdata()
    {
        try
        {
            // Open the connection for Selection 
            conn.Open();

            // Read Encrypted data 
            string selectString = @"SELECT PatientId, LastName, FirstName, BirthDate, SSN FROM [dbo].[Patients] ";
            SqlCommand scmd = new SqlCommand(selectString, conn);
            SqlDataReader dataRead = scmd.ExecuteReader();
            while (dataRead.Read())
            {
                MessageBox.Show("Selected Data with ID=" + dataRead["PatientId"].ToString() +
                                " LastName=" + dataRead["LastName"] +
                                " FirstName=" + dataRead["FirstName"] +
                                " BirthDate =" + dataRead["BirthDate"].ToString() +
                                " SSN=" + dataRead["SSN"].ToString());
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}


相关文章推荐

SQLServer 数据加密解密:常用的加密解密(一)

都是基本示例,更多参考官方文档: 1. Transact-SQL 函数 2. 数据库密钥 3. 证书 4. 非对称密钥 5. 对称密钥 [sql] view plain copy...

SQL Server 连接加密 -- SQL Server connection encyption

转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/03/22/sql-server-1-sql-server-connection-encyption.a...
  • dsjlzh
  • dsjlzh
  • 2012年02月07日 10:46
  • 1318

SQL Server 2016新特性: In-Memory OLTP

内存中OLTP有助于OLTP工作负荷实现显著的性能改进,并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务,并且可以使用Transact-SQL进行访...

SQL Server 2016新特性: tempdb增强(Multiple TempDB Files)

在数据处理的过程中,我们习惯使用SELECT INTO #Table这样的语句,用临时表来存放中间数据。大量使用临时表,会造成资源争夺,降低性能,因为在SQL Server 2012及之前的版本,t...

SQL Server 2016新特性:动态数据屏蔽(Dynamic Data Masking)

动态数据屏蔽是SQL Server 2016引入的一项新的特性,通过数据屏蔽,你可以对非授权用户限制敏感数据的曝光。动态数据屏蔽会在查询结果集里隐藏指定栏位的敏感数据,而数据库中的实际数据并没有任何...

SQL Server 2012 新特性 培训资料

  • 2012年03月09日 09:42
  • 17.97MB
  • 下载

SQL SERVER2008新特性

  • 2008年08月18日 21:05
  • 2.05MB
  • 下载

SQL Server 2012新特性_列存储索引(2)

在上一篇Blog(SQL Server 2012新特性_列存储索引(1) )中,对列存储及列存储索引的概念及特征等做了一些介绍,接下来,主要对列存储索引的创建与使用做一些演练: 创建COLUMNST...

SQL Server 2005 開發新特性介紹

  • 2008年10月08日 10:03
  • 11.12MB
  • 下载

SQL Server 2005新特性

  • 2008年12月16日 10:45
  • 295KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server 2016新特性: Always Encrypted (始终加密)
举报原因:
原因补充:

(最多只允许输入30个字)