question

AluwaniNethavhakoneBCX-4502 avatar image
0 Votes"
AluwaniNethavhakoneBCX-4502 asked AluwaniNethavhakoneBCX-4502 commented

always encrypted in sql server 2016 trigger update

I need to update a column using an encrypted column value after insert using a trigger. I need to get encrypted inserted column value and update other column using its value

I try below.

Declare @value vachar(16) = (select encyptedColumn from inserted) and get below error

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

but this works
Declare @value vachar(16) = '1234567890123456'

How can i get encrypted value and set it to a variable? without hardcoding it.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered AluwaniNethavhakoneBCX-4502 commented

That's dead in the water. You can never get that to work. I assume that function performs some logic on the input parameter. But all you have is a group of completely meaningless bytes, because the value is encrypted, and there is no way you can decrypt it inside SQL Server.

And if we overlook that small (well not so small) detail, the trigger is still wrong. As I said, you can't read something from inserted/deleted into a variable. (Unless you are running a cursor over the table, but please don't do that.

The above should be:

UPDATE myTable
CC_Key = dbo,myFunction(i.Type, i.CC_NO, i.FROM_DATE_TIME)
FROM  myTable T
JOIN  inserted i ON T.ID = i.ID

Note the FROM-JOIN clause.

But the above will not work as long as the column is encrypted. If you need the column encrypted, you need to re-think your approach.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks will try a different approach

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You can't get the encrypted value. That is the whole point with Always Encrypted: encryption takes place outside SQL Server, and the encryption keys are not available to SQL Server.

But that is only one error. This is wrong, even if the column is not encrypted:

Declare @value vachar(16) = (select encyptedColumn from inserted)

This is wrong, because a trigger fires once per statement, so this will explode with an error if many rows are inserted.

What you can do (I think, I have not tested) is:

UPDATE othertbl
SET     col = i.col
FROM  othertbl o
JOIN inserted i ON o.keycol = i.keycol

For this to work, othertbl.col needs have the same encryption key as the source column.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AluwaniNethavhakoneBCX-4502 avatar image
0 Votes"
AluwaniNethavhakoneBCX-4502 answered

Hi ErlandSommarskog

Thanks for response.

look at my trigger below.

ALTER TRIGGER [dbo].[myTrigger]
ON [dbo].[myTable]
AFTER INSERT, UPDATE
AS BEGIN


DECLARE @CC_NO AS VARCHAR (16) ;
SET @CC_NO = (SELECT inserted.CC_NO FROM inserted);

        UPDATE myTable
         
               CC_KEY   = dbo.myFunction(inserted.TYPE, @CC_NO, inserted.FROM_DATE_TIME),
                
        FROM   inserted
        WHERE  myTabl.ID = inserted.ID;
    END

What am trying to achieve is i need to assign @CC_NO with inserted value then pass it into a function which do some logic to get CC_Key.

If i dont declare a variable and call the function like dbo.myFunction(inserted.TYPE, inserted.CC_NO, inserted.FROM_DATE_TIME) i get below error.

Operand type clash: varchar is incompatible with varchar(16) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColumnKey', column_encryption_key_database_name = 'myDb')

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.