4

I am getting a SqlException:

Operand type clash: 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.

My C# code:

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();
    }
}

And my stored procedure: 

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;

I have no problems with inserting data into not encrypted fields.

I found this article

http://dataap.org/sql-2016-ctp/column-level-encryption-using-always-encrypted-in-sql-server-2016/

My issue is similiar but i haven't found the solution.

Mulana Monova
  • 41
  • 1
  • 1
  • 4

3 Answers3

5

There are 2 things you can try,

Ensure that Column encryption setting is enabled in your connection string. This can be done using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled as follows

strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

If your stored procedure was created before you encrypted your column, you will need to refresh metadata for your stored procedure as follows

Use [Database]
GO    
--Do this for all stored procedures
EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[Clients_Insert]'
Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
  • 1
    Than you for the answer.. I found a solution.. for some reason i should insert fields with the max length, so when i tried to insert something with length 12 into my field which is type varchar(50) i get an error.. i just fill up to 50 symbols with blank spaces .. – Mulana Monova Dec 15 '16 at 16:17
  • Hi Mulana Monova, Filling up to 50 symbols should not be necessary, If you can post your updated code, stored procs and the schema, I might be able to help. – Nikhil Vithlani - Microsoft Dec 16 '16 at 01:09
  • 1
    What if I try to insert data by without using stored procedure? Because currently I'm facing the same issue. – Mr. K Feb 14 '18 at 12:53
  • 2
    @Mr.K Set `Column Encryption Setting=enabled` in the connection string as described [here](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-2017) – Daniel Fisher lennybacon May 22 '18 at 14:13
5

If someone is still looking for an answer on this, what worked for me is that you need to use DbType.AnsiStringFixedLength data type in the SqlParameter data type, for the encrypted columns.

Refer this article for more information

devC
  • 1,384
  • 5
  • 32
  • 56
-2

Anyone who still face this issue and none of the above checks didn't help, make sure the parameter names exactly match (case sensitively) both in the procedure and the code.

Rony
  • 211
  • 2
  • 6