Sql-server – AlwaysEncrypted error when inserting via stored proc

always-encryptedsimple-parameterizationsql serverstored-procedures

lets start with the error first:

Encryption scheme mismatch for columns/variables . The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '3' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_P_H', column_encryption_key_database_name = 'Payments_Hub') (or weaker)

I'm trying to insert into a column with AlwaysEncrypt encryption on using a stored procedure:

The column 'PARAMETER_VALUE' is the encrypted column

CREATE PROCEDURE dbo.SP_UI_Parameter    
(
@ID int OUTPUT,
@OPERATION_ID int,
@PARAMETER_NAME varchar(100),
@PARAMETER_METRIC varchar(100),
@PARAMETER_VALUE varchar(100) 
)
AS
BEGIN

IF @ID IS NULL
BEGIN

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID,
PARAMETER_NAME, PARAMETER_METRIC, PARAMETER_VALUE)

SELECT @OPERATION_ID, @PARAMETER_NAME, @PARAMETER_NAME, @PARAMETER_VALUE

SET @ID = SCOPE_IDENTITY()

END
ELSE
BEGIN

UPDATE [dbo].[TOKEN_PARAMETER] SET
OPERATION_ID = @OPERATION_ID,
PARAMETER_NAME = @PARAMETER_NAME,
PARAMETER_METRIC = @PARAMETER_METRIC,
PARAMETER_VALUE = @PARAMETER_VALUE

END

END
GO

Why am I getting the error for the query ?

Is there something I must include within the STORED PROCEDURE ?

  1. I have included "Column Encryption Setting=Enabled " in my connection settings
  2. I have also enabled Parameteterization for AlwaysEncrypted
  3. The key is stored on the local machine
  4. I have created DETERMINISTIC encryption
  5. SQL Server 2016 database Engine using SSMS 17 – can this be an issue ?

SQL Server Version Info: (EDITED)

Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) – 13.0.4466.4 (X64) Dec 22 2017 11:25:00 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

SSMS Stored Procedure Call: (EDITED)

DECLARE @return_value int,
@ID int

EXEC    @return_value = [dbo].[SP_Insert_Parameter2]
 @ID = @ID OUTPUT,
 @OPERATION_ID = 3,
 @PARAMETER_NAME = N'algorithm',
 @PARAMETER_METRIC = N'algorithm',
 @PARAMETER_VALUE = N'99999'

 SELECT @ID as N'@ID'

 SELECT 'Return Value' = @return_value

 GO

Thanks In Advance

Best Answer

So I figured out what I was doing wrong AlwaysEncrypted requires the variables to be defined in line:

DECLARE @OPERATION_ID int = 4
DECLARE @PARAMETER_NAME varchar(100) = 'algorithm'
DECLARE @PARAMETER_METRIC varchar(100) = 'algorithm'
DECLARE @PARAMETER_VALUE varchar(100) = 'TestValue3'

INSERT INTO [dbo].[TOKEN_PARAMETER] (OPERATION_ID, PARAMETER_NAME,
PARAMETER_METRIC, PARAMETER_VALUE)
SELECT @OPERATION_ID,
       @PARAMETER_NAME,
       @PARAMETER_METRIC,
       @PARAMETER_VALUE

This allows for successful insert into the table. When using a stored procedure you cannot use the same method to insert.

So in essence, there is nothing wrong with how to stored procedure works. The application needs to change the way it populates the columns, using the same stored procedure

Please see below link for reference:

https://social.technet.microsoft.com/wiki/contents/articles/37979.working-with-the-always-encrypted-feature-in-sql-server-2016.aspx#Add_Data_to_Always_Encrypted_Column

Still waiting on applications team to come back with results but i'll mark the question as solved if this fixed the issue

Related Question