sp_describe_parameter_encryption does not send encryption information

Question

I have a stored procedure with 5 input variables, few of the variables are used to update or compare against columns from tables that are encrypted using "Always Encrypted" feature.

The "sp_describe_parameter_encryption" procedure returns encryption information for all parameters except one parameter, for which it is mentioning as normal attribute.

Hence the values is passed as "Plain Text", this in turn while execution creates error.

Sample Code

create proc Test
@var1 nvarchar(500),
@var2 nvarchar(500),
@var3 nvarchar(500),
@var4 nvarchar(500),
@var5 nvarchar(500),
AS
BEGIN
  SELECT * from Sometable where encryptedColumn=@var1 --works well(var1 is passed as encrypted.)
  update Sometable2  SET encryptedColumn=@var2 WHERE <SOMECONDITION> --works well(var 2 is passed as encrypted.))
--some code
--some code

   select * from sometable2 where encryptedColumn=@var4
  --fails with the following error
  --Operand type clash: nvarchar is incompatible with nvarchar(500) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEKName, column_encryption_key_database_name = 'DatabaseName')
--if this query is commented, the proc runs fine

END

when executing "sp_describe_parameter_encryption" command, The encryption for the variable(var4) is marked as plaintext and I expect it to return the encryption information since, it is compared against an encrypted column.

Could some one provide some leads, or mark me what I am missing

since you are comparing the encrypted column with the plain text `(@var4)` which cause the issue. You need to compare the `@var4` with plaintext column. Are you sure that `@var4` is encrypted column ? — Jayendran, Dec 20 '18 at 16:11
Yes i am comparing @var4 against encrypted column and it is the role of ADO.net to send the variable as encrypted based on the output of sp_describe_parameter_encryption...which is not happening... This is the question here — Samuel A C, Dec 20 '18 at 16:57

score 0 · Answer 1 · answered Feb 20 '20 at 16:35

Make sure the casing of all of your parameters match exactly between client and server. When implementing AE at my current position, we discovered that the call to sp_describe_parameter_encryption is apparently case sensitive on the parameters, and will throw an ambiguous error if they don't match exactly. I can think of no good reason for this, so hopefully it is just a bug.

sp_describe_parameter_encryption does not send encryption information

1 Answers1