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 ?
- I have included "Column Encryption Setting=Enabled " in my connection settings
- I have also enabled Parameteterization for AlwaysEncrypted
- The key is stored on the local machine
- I have created DETERMINISTIC encryption
- 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