I have a scenario where i need to have literals(hard coded strings) inside proc used against "Always Encrypted" columns, Since this fails with the following error,
Operand type clash: varchar is incompatible with nvarchar(20) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto4', column_encryption_key_database_name = 'DBName')
I am trying to do Parameterization for Always Encrypted within the stored proc, similar to below
GO
CREATE PROCEDURE InsertProc
@Var1 nVarchar(20)
As
BEGIN
DECLARE @Plaintext nvarchar(20)='testText'
INSERT INTO testClass(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
VALUES (@Plaintext,@Var1,default)
END
I have also enabled parameterization and also enabled column encryption setting for connection. Still getting the following error when creating procedure,
Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 4] Statement(s) could not be prepared. An error occurred while executing batch. Error message is: Internal error. Metadata for parameter '@p3467a2cdc3d547a3be48f46dfc7e9580' in statement or procedure<--my proc code here--> ' is missing in resultset returned by sp_describe_parameter_encryption.
NOTE:I still could execute and insert if i manually run the script which is inside the proc.But Proc creation is issue
Could some one help to fix or provide alternative solution.
I found a work around for using hard coded strings in procedures.
Example:
Create table with encrypted column
CREATE TABLE [dbo].[Encrypted_nVarchar_256](
[SNo] [smallint] IDENTITY(-32768,1) NOT NULL,
[EncryptValue] [nvarchar](256) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_key], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
[Value_Description] [nvarchar](256) NULL,
CONSTRAINT [Pk_Enc_nVar_256] PRIMARY KEY CLUSTERED
(
[SNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Insert text into encrypted column the value that you would required to be used inside procedures.
DECLARE @EncryptValue nvarchar(256) ='';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue,'Empty string')
DECLARE @EncryptValue1 nvarchar(256) ='Some string you would like to hard code';
INSERT INTO Encrypted_nVarchar_256(EncryptValue,[Value_Description]) VALUES(@EncryptValue1,'Your description')
--more rows as you need
GO
Now you could consume in stored procedure
CREATE PROCEDURE InsertProc
@Var1 nVarchar(20)
As
BEGIN
--Passing hard coded ''(Empty string) as insert value
INSERT INTO testTable(EncryptedCol1,EncryptedCol2,NonEncryptedCol)
SELECT TOP 1 EncryptValue,@Var1,"some string" FROM Encrypted_nVarchar_256 where Sno=-32768
--Comparing some hard coded string
SELECT * from testTable
where EncryptedCol1=(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256
where Sno=-32768)
--Using in functions
SELECT COALESCE(EncryptedCol1,(SELECT TOP 1 EncryptValue FROM Encrypted_nVarchar_256 where Sno=-32768)) as [new col name] from testable
END
Voila!!! now you could do anything with just a little twist.