c# - 操作数类型冲突 : varchar is incompatible with varchar(50) trying to insert in encrypted database

Question

我收到一个SqlException:

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 = 'CEK_Auto1', column_encryption_key_database_name = 'PB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'\r\nIncorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.

我的 C# 代码:

using (var connection = new SqlConnection(GetConnectionString()))
{
    using (var cmd = new SqlCommand("Clients_Insert", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = client.Email;
        cmd.Parameters.Add("@ContactPerson", SqlDbType.VarChar, 400).Value = client.ContactPerson;

        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

我的存储过程:

ALTER PROCEDURE [dbo].[Clients_Insert]
    @Email VARCHAR(50),
    @ContactPerson VARCHAR(400)
AS
BEGIN
    INSERT into dbo.Clients(Email, ContactPerson) 
    VALUES (@Email, @ContactPerson);

    SELECT SCOPE_IDENTITY();
END;

将数据插入未加密的字段时没有任何问题。

我找到了这篇文章

http://dataap.org/sql-2016-ctp/column-level-encryption-using-always-encrypted-in-sql-server-2016/

我的问题类似，但我还没有找到解决方案。

Answer 1

您可以尝试以下两件事，

确保在连接字符串中启用列加密设置。这可以使用 SqlConnectionStringBuilder 对象并将 SqlConnectionStringBuilder.ColumnEncryptionSetting 设置为 Enabled 来完成，如下所示

strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

如果您的存储过程是在加密列之前创建的，则您将需要刷新存储过程的元数据，如下所示

Use [Database]
GO    
--Do this for all stored procedures
EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[Clients_Insert]'