Cannot insert or update varchar empty/null value into always encrypted column

Question

I am trying to update empty "" or null value from C# using sql client into always encrypted column; however I am getting below error. It works if I pass a space " ".

Error:

nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK', column_encryption_key_database_name = 'db') is incompatible with nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK', column_encryption_key_database_name = 'db')

Please post your code/query and the table schema. The error complains about the parameter/field size, not nulls or empty strings. One of them has a length of 50, the other 4000. How did you try to update the table? Did you use a parameterized query or stored procedure? What was the size of the parameter? — Panagiotis Kanavos, Jun 28 '18 at 10:01
column schema is nVarchar(50) If I pass any value it works, there is issue for empty and null values, 4000 size I have not used either in table or procedure, no idea from where it is taking 4000 — dilipkumar katre, Jun 28 '18 at 10:03
If you don't use a size, a default one will be assumed. Post your code. — Panagiotis Kanavos, Jun 28 '18 at 10:05
thank you, your reply helped, size is required while creating parameter object, that was missing in my code. working sample code below. SqlConnection connection = new SqlConnection(dbString); connection.Open(); SqlParameter p = new SqlParameter("@lastname", System.Data.SqlDbType.NVarChar, 50); p.Value = string.Empty; SqlCommand command = new SqlCommand("update associate set lastname=@lastname", connection); command.Parameters.Add(p); command.ExecuteNonQuery(); connection.Close(); — dilipkumar katre, Jun 28 '18 at 10:15
If you have solved the problem (I read your last comment that way?) please enter the solution as an answer and accept it. That removes the question from the unanswered list. — ewramner, Jun 28 '18 at 10:17

score 1 · Accepted Answer · edited Jun 28 '18 at 10:27

Working code is below:

        SqlConnection connection = new SqlConnection(dbString);
        connection.Open();
        SqlParameter p = new SqlParameter("@lastname", System.Data.SqlDbType.NVarChar, 50);
        p.Value = string.Empty;

        SqlCommand command = new SqlCommand("update associate set lastname=@lastname", connection);
        command.Parameters.Add(p);
        command.ExecuteNonQuery();
        connection.Close();

The issue was the client parameter definition must match the encrypted column type on the server exactly.

Cannot insert or update varchar empty/null value into always encrypted column

1 Answers1