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

问题描述

我有一个场景，我需要在 proc 中使用文字（硬编码字符串）来对抗“始终加密”列，因为这会失败并出现以下错误，

操作数类型冲突：varchar 与使用 (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') 加密的 nvarchar(20) 不兼容

我正在尝试在存储过程中为 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

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

过程 sp_describe_parameter_encryption，第 1 行 [Batch Start 第 4 行] 无法准备语句。执行批处理时出错。错误信息是：内部错误。sp_describe_parameter_encryption 返回的结果集中缺少语句或过程<--my proc code here--> 中参数“@p3467a2cdc3d547a3be48f46dfc7e9580”的元数据。

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

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

标签： sql-serversql-server-2017always-encrypted

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

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

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

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

问题描述

解决方案

推荐阅读