65.9K
CodeProject 正在变化。 阅读更多。
Home

SQL Server 2016 中的 Always Encrypted 功能

Manjuke Fernando
starIconstarIconstarIconstarIconstarIcon

5.00/5 (3投票s)

2016年10月19日

CPOL

7分钟阅读

viewsIcon

16406

SQL Server 2016 中的 Always Encrypted 功能

随着 SQL Server 2016 于六月（正式最终版）的发布，微软为 SQL Server 引入了一些新的且非常有用的功能。其中一项功能就是“Always Encrypted”。

“Always Encrypted” 能够在数据保持加密状态的“同时”，对数据执行 SQL 操作（存在一些限制），就像它们是普通（未加密）数据一样。这意味着 SQL Server 始终获取加密数据来存储到表中。这为您的数据增加了一个额外的保护层，确保即使是本地的 DBA 或开发人员也无法通过其访问级别查看加密数据背后的明文值。（没有密钥的用户即使拥有“SysAdmin”访问权限也无法查看这些详细信息）。因此，“Always Encryption”实现了数据所有者（可以查看数据）和数据管理者（但应无权访问）的分离。

为什么需要 Always Encrypted？

使用 Always Encrypted 功能有很多好处

  • 它实现了数据所有者和管理数据的人员之间的清晰分离
  • 除非通过加密密钥提供适当的访问权限，否则即使是 DBA 或 SysAdmin 用户也无法以明文形式访问数据

最终，上述几点将提供无与伦比的数据泄露防护，并有助于保护敏感信息，如信用卡号、个人详细信息等。此外，这将拓宽存储此类敏感信息的范围。

Always Encrypted 如何工作？

这是一种客户端加密技术，SQL Server 客户端驱动程序起着关键作用。

image

  • 数据在客户端驱动程序内部被透明地加密。
  • 客户端管理加密密钥。SQL Server 对加密密钥没有任何信息。

SQL Server 可以查询和对加密数据执行某些计算，例如相等比较、相等连接、分组依据等。

Always Encrypted 演示

我们将演示如何实现和使用 Always Encrypted。为了说明这一点，我们将使用一个包含 `employee`（员工）信息的表。

CREATE TABLE Employee(
	Id			INT
	,FirstName	VARCHAR(100)
	,LastName	VARCHAR(100)
	,DOB		DATE
	,SSN		INT
	,[Address]	VARCHAR(255)
	,PostalCode	INT
)

INSERT INTO Employee (
	[Id],[FirstName],[LastName],[DOB],[SSN],[Address],[PostalCode])
VALUES
	(1,'James','Rubin','20-Jul-1986',173456858,'10585 N 600 E',46310)
	,(2,'Austin','Pyatt','24-Dec-1985',138868248,'100 BENTBROOK CT',27519)
	,(3,'Stacey','Munoz','23-Dec-1988',185682639,'1 WOODSIDE DR',4976)
	,(4,'James','Tweed','03-Jan-1987',133890886,'1 AUNNEK CT',95023)
	,(5,'James','Robles','11-Sep-1989',154135505,'101 FISHTRAP RD',35504)
	,(6,'Ebony','Lewis','17-Jul-1988',120488337,'101 N OAKS DR',35180)
	,(7,'Marian','Caro','20-Nov-1985',115281829,'1017 FISK ST SE',49507)
	,(8,'Lynne','Martinez','22-Apr-1985',157900240,'103 UNITY CT',78214)
	,(9,'Elsa','Cole','25-Apr-1990',150631885,'1001 E FERN AVE APT 201',78501)
	,(10,'Kiley','Caldwell','03-Jan-1988',131368172,'103 NOB HILL LN APT 5',40206)
	,(11,'Michael','Soluri','17-Jun-1985',173245124,'10770 S KILBOURN AVE',60453)
	,(12,'Gregory','Emmons','06-Sep-1988',137693229,'10 LOUISA PL APT 2F',7086)
	,(13,'Jessica','Barr','04-Feb-1989',155895863,'1 FAWNRIDGE DR',94945)
	,(14,'Daniel','Mccabe','06-Sep-1985',148236776,'1 CALLE MARGINAL GARCIA',674)
	,(15,'Sharon','Schwartz','06-Sep-1987',117569460,'1 KRITTER CT',8050)
	,(16,'Dorthy','Wear','13-Dec-1988',170517705,'1 CLARK RD',35747)
	,(17,'Betsy','Blansett','17-Jun-1990',182202498,'10 CALLE 1 DE FLORIDA',612)
	,(18,'Margaret','Payne','25-Jul-1985',157359609,'1003 BLOOMFIELD AVE',7006)
	,(19,'James','Walker','26-Jan-1989',142829150,'100 CONGLETON HOLLOW SPUR RD',40447)
	,(20,'Sarah','Reeves','22-Jun-1990',146171169,'1 BLUEBERRY LN',1832)

我有一个小型 MVC Web 应用程序，其中有一个页面用于列出上述 SQL Server 的详细信息。MVC Controller 将详细信息加载到 `Employee` 记录列表中，并将其传递给 HTML 视图，显示如下：

image

在 MVC 应用程序中，我使用以下数据模型从 SQL 数据库表中加载详细信息。

public class Employee {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DOB { get; set; }
        public int SSN { get; set; }
        public string Address { get; set; }
        public int PostalCode { get; set; }

        public Employee() {

        }
    }

我正在使用以下连接字符串来连接到 SQL Server 数据库。

 const string zConnectionString =
                @"Server=.\SQL2K16; Network Library=DBMSSOCN;Database=SQLTraining;
                Trusted_Connection=True;";

为了实现和使用此功能，需要在 SQL Server 和应用程序（客户端应用程序）两端遵循几个步骤。

在 SQL Server 端，有几种方法可以启用 Always Encrypted 功能。我们将通过向导详细了解如何使用这些功能。

  1. 右键单击要加密详细信息的表，然后选择“Encrypt Columns”（加密列）。这将引导您进入向导。

    image

  2. 您将看到介绍屏幕，其中包含有关“Always Encrypted”是什么的一些详细信息。单击“Next”（下一步）进入下一个屏幕。

    image

    这是列选择屏幕，允许您选择要加密的列以及使用的加密类型。SQL Server 2016 提供了两种加密类型。

    Microsoft 文档中包含了此建议：对将用作搜索或分组参数的列（例如，政府 ID 号）使用确定性加密。对于不需要与其他记录分组且不用于联接表的机密调查评论等数据，请使用随机加密。

    因此，在本例中，我们将选择 `DOB`（出生日期）和 `SSN`（社会安全号码）列进行加密。对于 `DOB`，我们将选择 `Randomized`（随机），对于 `SSN`，我们将选择 `Deterministic`（确定性）。

    一旦选择了加密类型，向导界面应该与下面显示的屏幕类似

    image

    仔细看，您会发现 Encryption Key（加密密钥）组合框已禁用。原因是到目前为止我们还没有创建任何 Column encryption keys（列加密密钥）。如果先创建密钥，那么您将可以选择使用现有密钥或生成新密钥。

    image

    在本演示中，我们将使用创建新列加密密钥的选项。单击“Next”（下一步）继续到下一步。

    • Deterministic（确定性）-> 确定性加密对于任何给定的明文值总是生成相同的加密值。使用确定性加密允许对加密列进行点查找、相等连接、分组和索引。但是，它也可能允许未经授权的用户通过检查加密列中的模式来猜测有关加密值的信息，特别是当可能的加密值集合很小时，例如 True/False，或 North/South/East/West 区域。确定性加密必须为字符列使用具有 binary2 排序顺序的列排序规则。
    • Randomized（随机）-> 随机加密使用一种不太可预测的方式来加密数据。随机加密更安全，但会阻止对加密列进行搜索、分组、索引和联接。
  3. 下一步是 Column Master Key Configuration（列主密钥配置）。Column Master Key（列主密钥）将用于加密和保护 Column Encryption Key（列加密密钥），后者用于加密数据。我们将使用“Auto generated column master key”（自动生成的列主密钥）选项，向导将为我们生成密钥。在创建新主密钥时，有两个选项可供选择，即新生成的密钥的存储位置。单击每个选项旁边的“info”（信息）小按钮将提供有关每个选项的更多详细信息。

    image

  4. 单击“Next”（下一步）进入下一步。在此步骤中，您可以决定是否需要为加密过程生成 PowerShell 脚本，还是立即继续加密。在本例中，我们将选择第二个选项，然后单击“Next”（下一步）按钮。

    image

    在此步骤中，将向您展示数据加密期间将遵循的步骤

    image

    单击“Finish”（完成）完成加密过程。过程完成后，单击“Close”（关闭）按钮。

    image

现在，如果您查看 SQL 表中的详细信息，可以看到 `SSN` 和 `DOB` 列中的数据已被加密。

SELECT * FROM dbo.Employee

image

如果您查看 `Employee` 表的表创建脚本，您可能会看到 SQL Server 在我们为这两个列启用加密后进行的一些更改。

CREATE TABLE [dbo].[Employee](
	[Id] [INT] NULL,
	[FirstName] [VARCHAR](100) NULL,
	[LastName] [VARCHAR](100) NULL,
	[DOB] [DATE] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
		ENCRYPTION_TYPE = RANDOMIZED,
		ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[SSN] [INT] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1],
		ENCRYPTION_TYPE = DETERMINISTIC,
		ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
	[Address] [VARCHAR](255) NULL,
	[PostalCode] [INT] NULL
) ON [PRIMARY]

您可以看到它已为这两个列添加了 `ENCRYPTED WITH` 子句。`ENCRYPTED WITH` 子句包含 3 个属性：

如果在 SSMS 的对象浏览器中检查 Always Encrypted 密钥，您会看到主密钥和列加密密钥的以下元数据。

image

Column Encrypted Key（列加密密钥） – CEK_Auto1

image

有关更多信息，请参阅以下 URL： https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/

Column Master Key（列主密钥） - CMK_Auto1

image

有关更多信息，请参阅以下 URL： https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/06/always-encrypted-key-metadata/

现在，如果我们尝试在未对示例 .NET 应用程序进行任何操作的情况下获取详细信息，您将收到类似如下的错误：

image

现在我们将研究在应用程序端（业务）需要更改的内容，以便检索所需信息。

1. 确保目标框架是 4.6 或更高版本。

image

2. 在连接字符串中包含“Column Encryption Setting=enabled”（列加密设置=已启用）。

我正在使用以下连接字符串来连接到 SQL Server 数据库。

 const string zConnectionString = @"Server=.\SQL2K16; Network Library=DBMSSOCN;
 Database=SQLTraining;Trusted_Connection=True;Column Encryption Setting=enabled;";

现在，如果我们从应用程序中检查详细信息，可以看到 `DOB` 和 `SSN` 值被获取为明文，即使这些值在 SQL Server 中已被加密。

image

image

希望这有助于您理解 SQL Server 2016 中的“Always Encrypted”功能以及如何将其集成到现有应用程序。

  • COLUMN_ENCRYPTION_KEY –> CEK_Auto1，因为我们选择了让 SQL 生成新密钥的选项。
  • ENCRYPTION_TYPE –> 可以是 `RANDOMIZED`（随机）或 `DETERMINISTIC`（确定性）
  • ALGORITHM（算法）–> 始终为 `AES_256`
  • COLUMN_MASTER_KEY –> 保护列加密密钥值的列主密钥的名称。
  • ALGORITHM（算法）–> 用于生成列加密密钥的加密值的算法（RSA_OAEP）。
  • ENCRYPTED_VALUE（加密值）–> 列加密密钥的加密值。假定该加密值是通过使用指定的列主密钥和指定的算法对列加密密钥的明文进行加密而产生的。
  • KEY_STORE_PROVIDER_NAME（密钥存储提供程序名称）–> 存储列主密钥的密钥存储的提供程序名称。
  • KEY_PATH（密钥路径）–> 指定密钥存储中列主密钥位置的密钥路径。
© . All rights reserved.