sql-server - 如何对 SQL Server 2017 中的表中的加密列执行插入、更新和删除操作

Question

我叫 Hari varma，是一名 SQL DBA。

目前，我在测试服务器的 SQL Server 2017 Development Edition 中遇到 Always Encryption 问题。

我在 Always Encryption 和 TDE 方面没有任何经验。我的一位客户要求我对数据库进行加密。

我已经对 Always Encryption 进行了一些测试，并且能够通过执行以下操作来加密和解密列数据:

On the SQL Server instance-->Options-->Additional connection Parameter-->Column Encryption Setting = Enabled

启用列加密后，我可以查看表中的加密数据。

但是我无法在此加密列中插入、更新和删除数据。此外，我需要能够为允许/不允许查看此加密列上的数据的用户设置权限。

我需要向特定用户授予哪些权限并提供 Always Encryption 和 TDE 的任何先决条件。

Answer 1

首先，重要的是要了解您的 SQL Server 实例在使用 Always Encrypted 时不知道用于加密和解密数据的 key 。 key 保存在外部，通常保存在 key 存储中，例如 Windows 证书存储或 Azure Key Vault。这意味着 SQL Server 本身无法加密或解密数据 - 这必须由有权访问 key 的客户端应用程序来完成。

However I am not able to insert, update, and delete data in this encrypted column.

我假设您正在尝试通过 SSMS 或类似工具直接插入、更新和删除数据。这只能在有限的范围内实现。这是因为 SSMS(在本例中是您的客户端应用程序)需要能够在将数据发送到您的 SQL Server 之前对其进行加密。

阅读更多关于 inserting data into columns that are encrypted via Always Encrypted 的信息在此处的 SQL Server 中(使用 SSMS)。

关于如何通过 SSMS 插入加密数据的简要总结:

• 您需要在连接字符串中启用列加密设置。当您在 SSMS 中连接到 SQL Server 实例时，这是在选项>>其他连接参数下完成的。在其中添加此文本:Column Encryption Setting=Enabled
• 连接到数据库并打开查询窗口后，您需要启用始终加密的参数化。这是在查询>>查询选项>>执行>>高级>>为始终加密启用参数化下在 SSMS 中完成的。

完成上述两个步骤后，您就可以像这样将数据插入加密列中:

DECLARE @ParameterToBeEncrypted NVARCHAR(100) = 'Decrypt me';
INSERT INTO dbo.MyTable(MyEncryptedColumn) VALUES (@ParameterToBeEncrypted);

之所以可行，是因为您的客户端应用程序 (SSMS) 能够在将您用于初始化 @ParameterToBeEncrypted 的值发送到 SQL Server 之前对其进行加密。这仅在您的当前用户有权访问列加密 key 时才有效。 SQL Server 永远不会看到普通/非加密值('Decrypt me') - 它只会看到应该插入到加密列中的加密值。

Which permissions I need to give on a particular user and provide any prerequisites for Always Encryption

它结合了 SQL Server 中的权限和能够访问用于加密和解密数据的 key 。必要的数据库权限是VIEW ANY COLUMN MASTER KEY DEFINITION 和VIEW ANY COLUMN ENCRYPTION KEY DEFINITION。你可以read more about the necessary permissions here.

如果您想加密数据库中的一组现有数据，那么最好的办法是编写您自己的客户端应用程序(例如使用 C# 或类似语言)或创建一个 SSIS 程序包(用作客户端应用程序)。客户端应用程序或SSIS包应从数据库中读取数据，在数据库外对数据进行加密，然后将其作为加密数据发送回数据库。