locked
trigger to set logs history on tables using always encripted RRS feed

  • Question

  • HI

    Till now i had a trigger responsible to record any values changes on table records to ta table of logs. Now that i start to encrypt the tables with always encrypted that same trigger, will now work ,and i get it why. but now how can i implement a functionality that does the same

    can audit tables be a solution?

    Friday, June 22, 2018 9:31 AM

Answers

  • Do you mean column level encryption/ always encrypted being applied on some of the columns?

    If yes, then you cant obviously capture changes to it

    you need to include logic in your trigger to only include the other columns


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, June 24, 2018 8:42 AM
  • If you have a general log table that will not work out, since all you have in the database is a bunch of bytes that make no sense without the decryption key. If you were permitted to store the data in the log table, how would you know which key to use to decrypt it?

    You need to have a specific log table, where the columns have the same encryption as the columns in the source table.

    Sunday, June 24, 2018 9:56 AM

All replies

  • You start encrypt the tables or just some columns? do not include the always encrypted in  to be inserted to the log table ? Does the app make changes on AE columns?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, June 24, 2018 8:13 AM
  • Do you mean column level encryption/ always encrypted being applied on some of the columns?

    If yes, then you cant obviously capture changes to it

    you need to include logic in your trigger to only include the other columns


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, June 24, 2018 8:42 AM
  • If you have a general log table that will not work out, since all you have in the database is a bunch of bytes that make no sense without the decryption key. If you were permitted to store the data in the log table, how would you know which key to use to decrypt it?

    You need to have a specific log table, where the columns have the same encryption as the columns in the source table.

    Sunday, June 24, 2018 9:56 AM