sql-server - Sql Server Always Encrypt - 从 .net 核心代码访问时出错 - 数据类型 varchar(50) ... 在等于运算符中不兼容

Question

在 Sql Server 中，当我尝试使用以下代码访问表时，表中的某些列已加密：

            string connectionString = "Server=xxx;Database=xxx;user id=xxx;password=xxx;Column Encryption Setting=Enabled";

            using (SqlConnection connection = new SqlConnection(connectionString))
            using (SqlCommand cmd = connection.CreateCommand())
            {
                connection.Open();

                cmd.CommandText = @"SELECT * FROM PersonSample WHERE Postcode=@Postcode";

                SqlParameter parameter = new SqlParameter("@Postcode", "AB10 6EN");
                cmd.Parameters.Add(parameter);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(@"{0}, {1}, {2}, {3}, {4}", reader[0], reader[1], reader[2], reader[3], reader[4]);
                        }
                    }
                }


            }

我在使用 cmd.ExecuteReader 时遇到错误：

Microsoft.Data.SqlClient.SqlException: 'The data types 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 = 'VerifiEncryptTest') collation_name = 'Latin1_General_BIN2' and nvarchar(8) 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 = 'VerifiEncryptTest') 
are incompatible in the equal to operator.
Statement(s) could not be prepared.'

我读过“确定性加密必须对字符列使用带有 binary2 排序顺序的列排序规则。”，有问题的列 Postcode 的排序规则类型为“Latin1_General_BIN2”，肯定是 binary2 吗？

标签： sql-serveralways-encrypted

Answer 1

我找到了答案，我需要像这样添加参数（所以包括 VarChar Sql 类型）：

                SqlParameter parameter = new SqlParameter("@Postcode", SqlDbType.VarChar, 50);
                parameter.Value = "AB10 6EN";