always encrypted in sql server 2016 trigger update

Aluwani Nethavhakone - BCX 21 Reputation points
2021-04-03T16:17:27.83+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-03T18:21:53.04+00:00

    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.


2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-03T17:18:11.44+00:00

    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.

    0 comments No comments

  2. Aluwani Nethavhakone - BCX 21 Reputation points
    2021-04-03T17:46:37.487+00:00

    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')

    0 comments No comments