我收到SqlException：

Operand type *: 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/

我的问题很相似,但是我没有找到解决方案.

解决方法:

您可以尝试两种方法,

确保在您的连接字符串中启用了列加密设置.可以使用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]'