操作数类型冲突:varchar 与尝试插入加密数据库的 varchar(50) 不兼容 [英] Operand type clash: varchar is incompatible with varchar(50) trying to insert in encrypted database

问题描述

我收到一个 SqlException:

操作数类型冲突:varchar 与 varchar(50) 加密不兼容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')collat​​ion_name = 'SQL_Latin1_General_CP1_CI_AS' 参数不正确从客户端收到加密元数据.发生错误在批处理调用期间，因此客户端可以通过调用刷新参数加密元数据sp_describe_parameter_encryption 并重试.

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' Incorrect 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;

我在将数据插入未加密的字段方面没有问题.

I have no problems with inserting data into not encrypted fields.

我找到了这篇文章

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

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

My issue is similiar but i haven't found the solution.

推荐答案

您可以尝试 2 件事，

There are 2 things you can try,

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

Ensure that Column encryption setting is enabled in your connection string. This can be done using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled as follows

strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

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

If your stored procedure was created before you encrypted your column, you will need to refresh metadata for your stored procedure as follows

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

这篇关于操作数类型冲突:varchar 与尝试插入加密数据库的 varchar(50) 不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！