使用始终加密的AzureKey保管库从客户端应用程序通过存储过程插入表中 [英] Insert into table via stored procedure from client application using always encrypted AzureKey vault

问题描述

我创建了一个客户端应用程序，该应用程序使用Always Encryption进行所有加密和解密值。

我使用Azure密钥保管库存储密钥。我遵循了此。硬编码查询（INSERT INTO ....）

对我来说很好存储过程，它不起作用。我已经提到了这个 Stack发布。但是不幸的是，解决方案/答案并不能解决我的问题。因此，我决定打开一个新问题。

这是我的C＃代码

  SqlCommand cmd = _sqlconn.CreateCommand（）; 
 
 //使用参数化的SQL插入数据
 cmd.CommandText = @ INSERT INTO [dbo]。[患者]（[SSN]，[名字]，[姓氏]，[ BirthDate]）值（@ SSN，@ FirstName，@ LastName，@ BirthDate）;; 
 
 SqlParameter paramSSN = cmd.CreateParameter（）; 
 paramSSN.ParameterName = @ @ SSN; 
 paramSSN.SqlDbType = SqlDbType.Char; 
 paramSSN.DbType = DbType.AnsiStringFixedLength; 
 paramSSN.Direction = ParameterDirection.Input; 
 paramSSN.Value = ssn; 
 paramSSN.Size = 11; 
 cmd.Parameters.Add（paramSSN）; 
 
 SqlParameter paramFirstName = cmd.CreateParameter（）; 
 paramFirstName.ParameterName = @ @ FirstName; 
 paramFirstName.DbType = DbType.String; 
 paramFirstName.Direction = ParameterDirection.Input; 
 paramFirstName.Value = firstName; 
 paramFirstName.Size = 50; 
 cmd.Parameters.Add（paramFirstName）; 
 
 SqlParameter paramLastName = cmd.CreateParameter（）; 
 paramLastName.ParameterName = @ @ LastName; 
 paramLastName.DbType = DbType.String; 
 paramLastName.Direction = ParameterDirection.Input; 
 paramLastName.Value = lastName; 
 paramLastName.Size = 50; 
 cmd.Parameters.Add（paramLastName）; 
 
 SqlParameter paramBirthdate = cmd.CreateParameter（）; 
 paramBirthdate.ParameterName = @ @ BirthDate; 
 paramBirthdate.SqlDbType = SqlDbType.Date; 
 paramBirthdate.Direction = ParameterDirection.Input; 
 paramBirthdate.Value =生日
 cmd.Parameters.Add（paramBirthdate）; 
 
 cmd.ExecuteNonQuery（）; 
  

当我对使用存储过程进行一些更改时。它会引发类似错误

其他信息：操作数类型冲突：char与使用（encryption_type ='DETERMINISTIC'， encoding_algorithm_name ='AEAD_AES_256_CBC_HMAC_SHA_256'，column_encryption_key_name ='CEK_Auto1'，column_encryption_key_database_name ='TestDB'）collat​​ion_name ='SQL_Latin1_General_CP1_CI_AS'

$ b b code> string execute = @ [dbo]。[insertsp];

SqlCommand cmd =新的SqlCommand（execute，_sqlconn）;
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramSSN = cmd.CreateParameter（）;
paramSSN.ParameterName = @ @ SSN;
paramSSN.SqlDbType = SqlDbType.Char;
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add（paramSSN）;

SqlParameter paramFirstName = cmd.CreateParameter（）;
paramFirstName.ParameterName = @ @ FirstName;
paramFirstName.DbType = DbType.String;
paramFirstName.Direction = ParameterDirection.Input;
paramFirstName.Value = firstName;
paramFirstName.Size = 50;
cmd.Parameters.Add（paramFirstName）;

SqlParameter paramLastName = cmd.CreateParameter（）;
paramLastName.ParameterName = @ @ LastName;
paramLastName.DbType = DbType.String;
paramLastName.Direction = ParameterDirection.Input;
paramLastName.Value = lastName;
paramLastName.Size = 50;
cmd.Parameters.Add（paramLastName）;

SqlParameter paramBirthdate = cmd.CreateParameter（）;
paramBirthdate.ParameterName = @ @ BirthDate;
paramBirthdate.SqlDbType = SqlDbType.Date;
paramBirthdate.Direction = ParameterDirection.Input;
paramBirthdate.Value =生日
cmd.Parameters.Add（paramBirthdate）;

cmd.ExecuteNonQuery（）;

这是启用加密后的模式

 创建表[dbo]。[患者] 
（
 [ssn] [varchar]（20）收集Latin1_General_BIN2加密为（COLUMN_ENCRYPTION_KEY = [CEK_Auto1]，ENCRYPTION_TYPE =确定性的，算法='AEAD_AES_256_CBC_HMAC_SHA_256'）NULL，
 [名字] [varchar]（50）NULL，
 [姓] [varchar]（50）NULL，
 [出生日期] [日期时间] NULL 
）
  

（PS：我已经启用了列加密在我的连接字符串中设置= enabled 。还尝试过使用 EXEC sys.sp_refresh_parameter_encryption @name ='[dbo]。[sp]'-都不适合我）

解决方案

只是一个区分大小写的问题（@ssn和@SSN）。

请不要忘记

SQL不能解决问题

C＃区分大小写

I'm created a client application that do all the encrypt and decrypt values using Always Encryption.

I've used Azure Key vault to store my Keys. I've followed this. It worked for me fine with the hardcoded query (INSERT INTO ....)

But when I tried with a stored procedure, it didn't work. I've already referred to this Stack Post. But the solutions/answer didn't solve my problems unfortunately. So I've decided to open a new question.

This is my C# code

SqlCommand cmd = _sqlconn.CreateCommand();

// Use parameterized SQL to insert the data
cmd.CommandText = @"INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate]) VALUES (@SSN, @FirstName, @LastName, @BirthDate);";

SqlParameter paramSSN = cmd.CreateParameter();
paramSSN.ParameterName = @"@SSN";
paramSSN.SqlDbType = SqlDbType.Char;
paramSSN.DbType = DbType.AnsiStringFixedLength;
paramSSN.Direction = ParameterDirection.Input;
paramSSN.Value = ssn;
paramSSN.Size = 11;
cmd.Parameters.Add(paramSSN);

SqlParameter paramFirstName = cmd.CreateParameter();
paramFirstName.ParameterName = @"@FirstName";
paramFirstName.DbType = DbType.String;
paramFirstName.Direction = ParameterDirection.Input;
paramFirstName.Value = firstName;
paramFirstName.Size = 50;
cmd.Parameters.Add(paramFirstName);

SqlParameter paramLastName = cmd.CreateParameter();
paramLastName.ParameterName = @"@LastName";
paramLastName.DbType = DbType.String;
paramLastName.Direction = ParameterDirection.Input;
paramLastName.Value = lastName;
paramLastName.Size = 50;
cmd.Parameters.Add(paramLastName);

SqlParameter paramBirthdate = cmd.CreateParameter();
paramBirthdate.ParameterName = @"@BirthDate";
paramBirthdate.SqlDbType = SqlDbType.Date;
paramBirthdate.Direction = ParameterDirection.Input;
paramBirthdate.Value = birthdate;
cmd.Parameters.Add(paramBirthdate);

cmd.ExecuteNonQuery();

When I changed a little bit for using the stored procedure. It throws error like

Additional information: Operand type clash: char is incompatible with varchar(20) 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 = 'TestDB') collation_name = 'SQL_Latin1_General_CP1_CI_AS'

string execute = @"[dbo].[insertsp]";

SqlCommand cmd = new SqlCommand(execute, _sqlconn);
cmd.CommandType = CommandType.StoredProcedure;

    SqlParameter paramSSN = cmd.CreateParameter();
    paramSSN.ParameterName = @"@SSN";
    paramSSN.SqlDbType = SqlDbType.Char;
    paramSSN.DbType = DbType.AnsiStringFixedLength;
    paramSSN.Direction = ParameterDirection.Input;
    paramSSN.Value = ssn;
    paramSSN.Size = 11;
    cmd.Parameters.Add(paramSSN);

    SqlParameter paramFirstName = cmd.CreateParameter();
    paramFirstName.ParameterName = @"@FirstName";
    paramFirstName.DbType = DbType.String;
    paramFirstName.Direction = ParameterDirection.Input;
    paramFirstName.Value = firstName;
    paramFirstName.Size = 50;
    cmd.Parameters.Add(paramFirstName);

    SqlParameter paramLastName = cmd.CreateParameter();
    paramLastName.ParameterName = @"@LastName";
    paramLastName.DbType = DbType.String;
    paramLastName.Direction = ParameterDirection.Input;
    paramLastName.Value = lastName;
    paramLastName.Size = 50;
    cmd.Parameters.Add(paramLastName);

    SqlParameter paramBirthdate = cmd.CreateParameter();
    paramBirthdate.ParameterName = @"@BirthDate";
    paramBirthdate.SqlDbType = SqlDbType.Date;
    paramBirthdate.Direction = ParameterDirection.Input;
    paramBirthdate.Value = birthdate;
    cmd.Parameters.Add(paramBirthdate);

    cmd.ExecuteNonQuery();

This is my schema after the Encryption enabled

CREATE TABLE [dbo].[patients]
(
    [ssn] [varchar](20) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [BirthDate] [datetime] NULL
)

(PS: I've already enabled the Column Encryption Setting=enabled in my connection string. Also tried with EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[sp]' - neither works for me)

解决方案

Just a Case Sensitive Issue (@ssn and @SSN).

Please not to forget that

SQL is not caSe SenSitIve

C# is CASE SENSITIVE

这篇关于使用始终加密的AzureKey保管库从客户端应用程序通过存储过程插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！