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

问题描述

我收到一个 SqlException ：

操作数类型冲突：varchar是与加密的
与（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'）不兼容varchar（50）
collat​​ion_name ='SQL_Latin1_General_CP1_CI_AS'\r\\\
Incorrect参数
从客户端收到加密元数据。在调用批处理时，
发生错误，因此客户端可以
通过调用
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'\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;

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

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

我发现这篇文章

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

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

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

推荐答案

有两件事你可以尝试，

确保连接字符串中已启用列加密设置。这可以使用 SqlConnectionStringBuilder 对象并将 SqlConnectionStringBuilder.ColumnEncryptionSetting 设置为启用如下

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屋！