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

Usha George 0 Reputation points
2024-07-26T11:55:34.62+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
69 questions
{count} votes