SQL Server before and after auditing of DML/data changes

Before and after auditing tracks changes to data, showing the old and new values after each change. This data can be re-constructed to show an entire history of row changes and is important for forensic auditing in the case of malicious or inadvertent data changes

In ApexSQL Audit, before and after auditing is implemented via CLR triggers. For more information on what CLR triggers are see the links at the bottom of this article

ApexSQL Audit before after data auditing is designed to ensure strong 256-bit data encryption from the moment the data change is captured by the CLR trigger and all the way until the audited data change values are stored in the central repository database. In this way, the auditing process meets strict compliance and security auditing requirements.

Note: Auditing before and after values is in many cases an obligatory type of audit. However, before and after auditing should be carefully and selectively implemented for sensitive data only, which is generally both important but also infrequently updated. Before and after auditing when implemented randomly or for high activity tables, can occupy, due to the underlying technology involved, a large amount of audited data store, with possibility to create in some cases significant overhead on SQL Server performance. So the important rule with before and after auditing is to apply it selectively.

To setup before and after auditing for specified SQL Server in ApexSQL Audit, after selecting the SQL Server that have to be audited, select the Before-after tab, that will open the before-after screen

To add SQL Server database(s) for before after auditing, press the Add database that will open the Add databases dialog where databases that will be added for before and after auditing can be selected.

After selecting the checkboxes of the databases that will be added for before and after auditing, press OK. Databases will be listed in the Before-after tab

Quick tip icon

Quick tip:

If SQL Server replication is in use for the selected database, before-after auditing should not be established and enabled. Before and after data auditing doesn’t support SQL Server replication. More details can be found in the Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION MSDN article. This is valid for both types, Transactional and Merge replication. Although those two types are very different, triggers behave almost the same for both

Now the user can select what tables to audit for each added database, what columns for selected table and to select what DML operation (insert, update and delete) to audit per table. ApexSQL Audit permits auditing of before and after data changes for each table column individually, and thus ensure a level of granularity to accommodate most if not all compliance and/or internal auditing requirements

The Add table button will open the dialog where user can select the tables that will be audited for before after changes

Quick tip icon

Quick tip:

Make sure that selected table names don’t contain the following special characters to avoid errors while auditing: ? |” \ / : *

Now when the tables are added, the application will list the columns for each table and the user can set which column will be audited for insert, update or delete operations

Once the databases, tables, columns and operation types e.g. update are selected, pressing the will create the appropriate CLR triggers for selected tables/columns and before and after auditing will start

The tool is designed to be resilient to audited tables schema changes, which mean that the trigger code is optimized to ensure normal operations on the table where schema change occurred (not quite possible with DML Triggers). Also, the user can easily handle and adopt application to any schema changes of audited tables. When any such DDL change has occurred, the application has an option to reload the tables via the Before-after tab using the Reload schema button. The button will initiate reloading the already added tables and if any schema (DDL) change has been detected, the new table schema will be displayed and the table name will be highlighted

Now, in case that schema changes do not affect what was already set for auditing, just press Apply in the notification bar, and the appropriate CLR triggers will be recreated in the target database to accommodate the new table structure

The before and after auditing report is the part of standard ApexSQL Audit reporting including the ability to use appropriate dedicated filters for narrowing down the report results

To preview of the before and after report, use the Complete audit trail or DML changes reports. The Before-after checkbox from the Event source group in the Complete audit trail or DML changes reports must be selected. If only Before-after report should be displayed, then checkboxes for Trace and Internal must be unchecked in Complete audit trial and in the DML changes report only Trace have to be unchecked

Below is an example of before and after changes audited by ApexSQL Audit and displayed in the Common report

For those who prefer to create specific reports for before-after changes, full customization is available. Below is one example of a custom report condition that will display the before after data changes occurred in the table Sales.CreditCard of the AdventureWorks 2014 database

The user has an option to create and save multiple specific reports to show only some specific audited changes and/or to create and save a global report that will allow reviewing of all before after changes in a single place

One of the very important auditing requirements is to be notified about any inadvertent or unplanned changes to audited tables. ApexSQL Audit has an alerting engine that is capable of sending alerts on tracked before and after changes in real time, including the notification via email. For that purpose, the dedicated Before-after alerts are implemented.

After selecting Before-after alert from the New dropdown menu, the new wizard will be started

The Basic settings window is the first step of the wizard and it is fully customizable to match the user preferences. A various variable that can be used in the message subject and body will ensure that user will have all the necessary information when receive the alert, which can make possible to perform immediate reactions to potential threats

Unlike the regular Auditing alerts, the Before-after alerts are crafted to suit the specific before and after auditing needs, and therefore some different condition categories are introduced in the Conditions step of the wizard

The filter categories highlighted on the above image allows the user to set up a very precise alerting conditions that will ensure tracking even the specific data values of a particular interest for the user

In the Actions step of the wizard the sending alert to an email address for immediate notifications or writing the alert into a Windows Event Log (more precisely in Application Log) for users that are using SIEM/SOC or SCOM solution in their enterprises

For users where CLR trigger based before and after data auditing is not preferable, they should consider another ApexSQL auditing tool – ApexSQL Log. ApexSQL Log is non-intrusive auditing solution, with zero overhead on audited SQL Server, thanks to the fact that it is based on the direct SQL Server transaction log file reading. This enables the full before and after data auditing even for the data changes occurred way before the ApexSQL Log is deployed as the auditing solution

ApexSQL Audit CLR trigger based auditing for data changes where the immediate notification and appropriate reaction is needed and ApexSQL Log for deep forensics including full row history, can be complimentary approaches

For more information on transaction log based before and after auditing see “Trigger less before and after auditing using the transaction log”

Resources:

CLR Triggers
Create CLR Triggers
How to: Create and Run a CLR SQL Server Trigger
Generic Audit Trigger CLR C#(Works when the trigger is attached to any Table)
Detailed SQL Server data audit through a .NET (CLR) trigger

February 25, 2016