sql-server - 参数化(始终加密)- 内部存储过程

Question

我有一个场景，我需要在 proc 中使用文字(硬编码字符串)来对付“Always Encrypted”列，因为这会失败并出现以下错误，

Operand type clash: varchar is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4', column_encryption_key_database_name = 'DBName')

我正在尝试在存储过程中对 Always Encrypted 进行参数化，类似于下面

GO
    CREATE PROCEDURE InsertProc  
    @Var1 nVarchar(20)  
    As  
    BEGIN     
        DECLARE @Plaintext nvarchar(20)='testText' 

        INSERT INTO testClass(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
        VALUES (@Plaintext,@Var1,default)
    END

我还为连接启用了参数化和列加密设置。创建程序时仍然出现以下错误，

Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 4] Statement(s) could not be prepared. An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p3467a2cdc3d547a3be48f46dfc7e9580' in statement or procedure<--my proc code here--> ' is missing in resultset returned by sp_describe_parameter_encryption.

注意:如果我手动运行 proc 中的脚本，我仍然可以执行和插入。但是 Proc 创建有问题

有人可以帮助修复或提供替代解决方案。

Answer 1

Always Encrypted 的主要目的是使 SQL Server(因此您的 DBA)无法解密您的敏感数据。为此，SQL Server 绝对无法访问加密 key ，也无法加密和解密数据。所有加密和解密都由客户端(您的应用程序、SSMS 等)执行。为 Always Encrypted 启用参数化是 SSMS 的一项功能。启用后(并且您为连接指定 Column Encryption Setting=Enabled)，SSMS 将检测脚本中声明的变量，将其转换为参数化查询并执行转换后的版本。这样，参数值将在客户端加密，SQL Server 根本看不到它们的纯文本值。

在您的示例中，@Plaintext 不是在您的脚本中声明的变量，而是存储过程的局部变量。所以这段代码(赋值)将在 SQL Server 引擎本身中执行，并且由于它无法访问加密 key ，因此根本不可能加密该值。因此，您也需要将其作为存储过程的输入参数移动。在这种情况下，很容易给它一个默认值并在调用过程时忽略它，但默认值是存储在元数据中的东西并且是明文的(未加密)。必须有人对其进行加密，而唯一能做到这一点的就是客户端。因此，您需要从客户端传递它而不是将其设为默认值，即不可能在服务器中使用“硬编码值”。

如果这对您来说是一个主要障碍，那么 Always Encrypted 可能不适合您的情况。如果您使用带有证书的列级加密，您将能够完成所有这些事情。但这破坏了 Always Encrypted 的主要优势 - 您的 DBA 无法访问您的 secret 。