Error while trying to update Always Encrypted column using a stored procedure

Question

In our development environment, one dedicated machine is set as SQL Server Database server. All other development machines are connected to this server as clients using SQL Server Management Studio. Facing some issues after implementing "Always Encrypted" in our database. We have configured the Column Master Key and Column Encryption Keys on the Database Server machine. Later exported the certificate from Database Server machine and imported on one development machine. One Windows application and one ASP.NET Web Application are running on  this machine. Also verified the certificate was created under local machine account.

We are getting the following error while trying to update Always Encrypted column using a stored procedure. Stored procedure is called from our ASP.NET Web application. We are using System.Data.Linq.DataContext class to call the stored procedure.

"Operand type clash: varchar(8000) 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' is incompatible with varchar(9) 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' "

<Code in DataContext class to call stored procedure>

[Function(Name = "dbo.usp_Update_Applicant_web")]        

public ISingleResult<SingleCoumnResult> UpdateApplicant([Parameter(DbType = "VarChar(9)")] string recordId, [Parameter(DbType = "VarChar(9)")] string str_socialsecuritynum,                     [Parameter(DbType = "VarChar(50)")] string str_lastname, [Parameter(DbType = "VarChar(50)")] string str_firstname, [Parameter(DbType = "VarChar(9)")] string str_telephonyID)        

{            

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), recordId, str_socialsecuritynum, str_lastname,str_firstname, str_telephonyID);             return ((ISingleResult<SingleCoumnResult>)(result.ReturnValue));        

}

<Stored Procedure> Here "EncryptedSSN" is the encrypted column.

ALTER PROCEDURE [dbo].[usp_Update_Applicant_web]                  

(                    

@recordId varchar(9)

,@str_socialsecuritynum varchar(9)                                                   ,@str_lastname varchar(50)                                                  

,@str_firstname varchar(50)   

,@str_telephonyID varchar(9)=null  )            

AS

BEGIN                              

UPDATE   

 Applicant                                                 

SET                                                        

EncryptedSSN = @str_socialsecuritynum ,     

LastName = @str_lastname ,

FirstName = @str_firstname                                                           

,telephonyID = @str_telephonyID                                                  

WHERE          

recordId= @recordId

END

We have observed that the same stored procedure works fine if the it is invoked through the Windows Application from the same machine.

Answer 1

Hi @Usha George

Have you enabled Always Encrypted when you connect to a database in SSMS. By default, Always Encrypted is disabled.

If you don't enable Always Encrypted for a connection, the .NET Framework Data Provider for SQL Server, SSMS uses, won't try to encrypt query parameters or decrypt results.

To enable (disable) Always Encrypted:

1. Open Connect To Server dialog (see Connect to a SQL Server instance for details).
2. Select Options.
3. Select the Always Encrypted tab. To enable Always Encrypted, select Enable Always Encrypted (column encryption). To disable Always Encrypted, make sure Enable Always Encrypted (column encryption) isn't selected.
4. Select Connect.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".