sql - varchar 与使用 (encryption_type = 'DETERMINISTIC' 加密) 的 varchar(50) 不兼容

Question

我正在尝试从应用程序传递参数以在存储过程中进行搜索。我这样传入参数:

SqlParameter param1 = new SqlParameter(@"@FilterCustomerPO", "G06756");
param1.DbType = DbType.AnsiString;
param1.Direction = ParameterDirection.Input;
param1.Size = 50;

sqlCmd.Parameters.Add(param1);

在存储过程中，定义如下:

ALTER PROCEDURE [dbo].[usp_POListing]
    @FilterCustomerPO VARCHAR (50)
AS
BEGIN
    SELECT * 
    FROM [Order]
    WHERE PONumber = @FilterCustomerPO
END

PONumber 列使用 DETERMINISTIC 加密类型进行加密。

当我传入一个值时，出现错误:

Operand type clash: varchar is incompatible with varchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnEncryptionKey', column_encryption_key_database_name = 'DataPortal') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

我见过很多其他人对此进行了报道，但这些解决方案都不适合我。正如您所看到的，我正在使用参数化查询，因此不确定我可能会丢失什么。

编辑:

我也尝试过以这种方式传递参数 - 同样的错误:

sqlCmd.Parameters.Add("FilterCustomerPO", SqlDbType.VarChar, 50);
sqlCmd.Parameters["FilterCustomerPO"].Value = "G06756"

订单表的定义如下:

CREATE TABLE [dbo].[Order]
(
    [OrderID] [INT] IDENTITY(1,1) NOT NULL,
    [CustomerID] [INT] NOT NULL,
    [OrderNumber] [INT] NOT NULL,
    [DBCOrderNumber] [VARCHAR](25) NOT NULL,
    [PONumber] [VARCHAR](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [BillingName] [VARCHAR](255) NOT NULL,

    CONSTRAINT [PK_Order] 
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
                    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

我的客户端连接字符串还包括列加密设置=已启用

请注意:我能够很好地检索数据。当我将参数发送到查询中，并在收到错误时尝试进行比较/过滤时。

Answer 1

这是AlwaysEncrypted ，它仅使用客户端加密 key 。它旨在防止 SQL Server(或其管理员)解密数据。如果您想要使用服务器管理的 key 进行列加密，SQL Server 也有这个功能，但它是一个不同的功能。请参阅Encrypt a Column of Data .

在 Order.PONumber 的 AlwaysEncrypted 数据类型中，实际上并不只是 varchar(50)。这是

[varchar](50) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (
      COLUMN_ENCRYPTION_KEY = [ColumnEncryptionKey], 
      ENCRYPTION_TYPE = Deterministic, 
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')

这是列类型的全部部分，因此您不能使用varchar(50)类型的参数。要搜索此列，客户端必须使用指定的 key 和算法加密参数值，以便 SQL Server 可以将确定性加密的列值与加密的参数值进行匹配。 SQL Server 没有列加密 key ，因此无法解密列值或加密参数值。

SQL Server 具有将加密表参数传播到存储过程或函数的能力。这个看起来效果很好。要检查存储过程参数是否已选择列加密，请检查 sys.parameters。 EG

select name, encryption_type_desc, encryption_algorithm_name
from sys.parameters
where object_id = object_id('usp_POListing')

我创建了一个简单的重现，并且能够像这样调用存储过程:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp27
{
    class Program
    {
        static void Main(string[] args)
        {

            using (var con = new SqlConnection("server=localhost;database=testdb;integrated security=true;Column Encryption Setting=enabled"))
            {
                con.Open();

                var sqlCmd = new SqlCommand("usp_POListing", con);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                var param1 = new SqlParameter("@FilterCustomerPO", "G06756");
                param1.SqlDbType = SqlDbType.VarChar;
                param1.Size = 50;
                param1.Direction = ParameterDirection.Input;

                sqlCmd.Parameters.Add(param1);

                var dt = new DataTable();
                using (var rdr = sqlCmd.ExecuteReader())
                {
                    dt.Load(rdr);
                }
            }
        }
    }
}

一般参见Develop using Always Encrypted with .NET Framework Data Provider