CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresencryptionalways-encrypted
Adam
Adam

Reputation: 1

Stored proc Always Encrypted and CASE with default value

I am testing always encrypted to be potentially used in a legacy application. For a test I encrypted an NVARCHAR column in one of the basic lookup tables in the application. I got warnings from the encryption wizard warning me about some of the stored procedures not measuring up. Upon inspection of the procedure, I noticed it is complaining about a particular CASE statement on the encrypted column.(NOTE: the encrypted column is adl.adl_Text NVARCHAR(50) ). The column returns a default(pre defined string) in the case that the table PK is null. I understand an ISNULL may work here but this is an example of the problem. There is no string comparison going on with the encrypted column so I am a bit confused what the issue is.

I have tried using a constant at the top of the procedure instead of the literal string inline with the query.

DECLARE @add_removed NVARCHAR(50)
SET @add_removed = 'Address Removed'

SELECT addr.Field1
  , addr.Field2
  , CASE WHEN addr.FieldPK IS NULL THEN @add_removed ELSE adl.adl_Text END AS StatusReason
FROM address addr
LEFT JOIN Address_Delete_Reason adl ON adl.PK = addr.adl_FK

I previously had:

SELECT addr.Field1
  , addr.Field2
  , CASE WHEN addr.FieldPK IS NULL THEN 'Address Removed' ELSE adl.adl_Text END AS StatusReason
FROM address addr
LEFT JOIN Address_Delete_Reason adl ON adl.PK = addr.adl_FK

The error I am getting is :

Error: Operand type clash: varchar is incompatible with nvarchar(50) 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 = 'DBTEST').

Upvotes: 0

Views: 864

Answers (1)

GreyOrGray
GreyOrGray

Reputation: 1729

Since the procedure existed before applying the encryption, try refreshing the always encrypted metadata for the stored proc using sp_refresh_parameter_encryption

The encryption metadata for parameters of a module can become outdated, if:

Encryption properties of a column in a table the module references, have been updated. For example, a column has been dropped and a new column with the same name, but a different encryption type, encryption key or an encryption algorithm has been added.

You'll probably need to do this for every proc that references the newly encrypted objects:

EXEC sys.sp_refresh_parameter_encryption @name = '[YourSchema].[YourProc]'

Upvotes: 1

Related Questions