SQL Server Always Encrypted Data

[Roman Wienicke]

Hello,

I got a problem updating data in an SQL Server 2016. The table data was updatet to be encrypted with the "always encrypted wizard" in the SMSS. I'm using a stored procedure to update the data.

The table has existing data which was encrypted and now the application should work, but there are errors executing the stored proc from the application.

I can update the data in the SMSS with the stored procedure, everything is working fine (parameters are encrypted by SSMS)

I can create a new table and update the encrypted data.

I can't use the existing stored proc, with the following error:

"System.Data.SqlClient.SqlException: 'Operand type clash: bigint is incompatible with bigint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', "

db.CommandType = CommandType.StoredProcedure;

var parameters = new HashSet<IDataParameter>(db.PropertiesToParameter(method, db.CreateParameter("@instituteID", method.Institute.Id)));

parameters.RemoveWhere(p => Regex.IsMatch(p.ParameterName, @"accountNumber|ownerFirstName|ownerLastName|iban", RegexOptions.IgnoreCase));

var p_accountNumber = db.CreateParameter("@AccountNumber", method.AccountNumber);
p_accountNumber.DbType = DbType.Int64;
parameters.Add(p_accountNumber);

var p_fn = (SqlParameter)db.CreateParameter("@ownerFirstName", method.OwnerFirstName);
p_fn.DbType = DbType.AnsiStringFixedLength;
p_fn.Size = 255;
parameters.Add(p_fn);

var p_ln = (SqlParameter)db.CreateParameter("@ownerLastName", method.OwnerFirstName);
p_ln.DbType = DbType.AnsiStringFixedLength;
p_ln.Size = 255;
parameters.Add(p_ln);

var p_iban = (SqlParameter)db.CreateParameter("@iban", method.Iban);
p_iban.DbType = DbType.AnsiStringFixedLength;
p_iban.Size = 50;
parameters.Add(p_iban);



db.ExecuteNonQuery("[dbo].[PaymentMethodsUpdate]", parameters.ToArray());

executing the following code works:

db.CommandType = System.Data.CommandType.Text;
var data = db.GetDataRow("Select * from dbo.test");
db.CommandType = System.Data.CommandType.StoredProcedure;



var p1 = db.CreateParameter("@value", 102);
p1.DbType = DbType.Int64;

var p2 = (SqlParameter)db.CreateParameter("@name", "from test");
p2.DbType = DbType.AnsiStringFixedLength;
p2.Size = 50;




var res = db.ExecuteNonQuery("[dbo].[testInsert]",
p2, p1
);


all the encrypted parameters are checked and equal (case etc.)

thank you for your help

Continue reading...