How to audit your auditing in SQL Server – tracking when triggers are disabled

SQL Server auditing triggers are mostly used to maintain the integrity of the information on a database, or to provide an auditing trail of data changes. A trigger is a special type of a database object which is automatically executed upon certain conditions – e.g. actions performed by the user. What auditing triggers must provide while auditing data changes are answers to the following forensic questions:

1. Who changed the data?
2. What was the date and time when the change occurred?
3. The type of data change – whether it was inserted, updated, or deleted
4. In case the change was a data modification (aka UPDATE) statement, what was the data value before and after the change?
5. What SQL Server object was changed – e.g. whether it was table, trigger, or a single row

There are a wide range of common malicious data change scenarios that can be performed without leaving any traces behind (e.g. bank account, sales, or commission systems data). To do that, a user could disable any auditing triggers, change the data, and enable the trigger afterwards

A trigger can be disabled using T-SQL or SQL Server Management Studio GUI. Either way, to disable or enable a DML trigger, a user must have ALTER permission on the table on which the trigger was created at a minimum

“Disabling a trigger does not drop it. The trigger still exists as an object in the current database. However, the trigger will not fire when any INSERT, UPDATE, or DELETE statement on which it was programmed is executed. Triggers that are disabled can be reenabled. Enabling a trigger does not re-create it. The trigger fires in the same manner as when it was originally created” [1]

The same permission requirements go for deleting a trigger, but we won’t consider this as a common scenario – the deleted trigger must be re-created afterwards in order to mask the activities. However, such scenarios can be tracked the same way which we’ll describe below

Using the SQL Server Audit feature to track when triggers are disabled

Currently, only SQL Server Enterprise Edition and SQL Server Developer Edition support the Audit feature in order to track enabling and disabling of triggers

To capture these events, you need to create a Server audit specification first – the SQL Server Audit object collects server or database-level actions and groups of actions:

USE master;
GO
CREATE SERVER AUDIT ServerAudit
TO FILE (FILEPATH = 'c:\audits\', MAXSIZE = 2 GB)
WITH (ON_FAILURE = CONTINUE);
GO
ALTER SERVER AUDIT ServerAudit
WITH (STATE = ON);

The next step is to create a Database audit specification at the database level. The audit group we need to capture in our case is SCHEMA_OBJECT_CHANGE_GROUP – there is no audit group that exclusively captures enable/disable trigger events

USE [ACMEDB];
GO
CREATE DATABASE AUDIT SPECIFICATION schema_change
FOR SERVER AUDIT ServerAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GO

Query the previously created audit, by using the LIKE operator to narrow down captured entries to the ones related to enable/disable triggers:

SELECT
       event_time AS [Time],
       server_principal_name AS [User],
       object_name AS [Object name],
       Statement
  FROM sys.fn_get_audit_file('c:\audits\ServerAudit*', NULL, NULL)
WHERE
       database_name
       =
       'ACMEDB'
   AND (
       Statement LIKE '%DISABLE%TRIGGER%'
    OR Statement LIKE '%ENABLE%TRIGGER%')ORDER BY
                                          [Time] DESC;

The results will show who disabled/enabled a trigger and when:

Although the solution we described is applicable for SQL Server Enterprise Edition and SQL Server Developer Edition users only, a SQL Server Database Audit is fairly simple to be implemented, and can help with tracking when triggers are disabled/enabled

Tracking when triggers are disabled using a DDL trigger

SQL Server DDL triggers can be used to track DDL operations, whether the changes were accidental or deliberate. The DDL triggers solution requires creation and maintain of both audited information storage and triggers

In the following SQL script example, the disable/enable DML trigger event is captured via the SQL Server’s Eventdata () function, used in the DDL trigger. The SQL script creates DDL trigger that captures ALTER operations (although DDL trigger can capture CREATE and DROP events too, for the purpose of auditing disable/enable DML triggers we’ll capture the ALTER events only):

CREATE TRIGGER DDL_Audit
ON DATABASE
    FOR ALTER_TABLE
AS
     DECLARE
        @auditevent xml;
     SET
     @auditevent = EVENTDATA();
     INSERT INTO DDL_Audit_Events
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @auditevent.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @auditevent.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(max),
     @auditevent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );

The auditing data from EVENTDATA XML should be stored in an appropriate table:

CREATE TABLE DDL_Audit_Events
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(250)
             ,
             DDL_Database_Name         varchar(250)
             ,
             DDL_Object_Name           varchar(250)
             ,
             DDL_Command              varchar(max)
);

The DDL_Audit trigger fires on each database ALTER event, and captured information can be queried from the DDL_Audit_Events audit repository table:

SELECT
       DDL_Event_Time
       , DDL_Login_Name
       , DDL_Database_Name
       , DDL_Object_Name
       , DDL_Command
  FROM ACMEDB.dbo.DDL_Audit_Events WHERE
DDL_Command LIKE '%DISABLE%TRIGGER%'
OR DDL_Command LIKE '%ENABLE%TRIGGER%'
ORDER BY
         DDL_event_time DESC;

The results are similar to the SQL Server Audit feature results:

Although this method can produce viable auditing results, tracking when triggers are disabled using a DDL trigger has several disadvantages. First of all, a user with sufficient permissions (the minimum is an ALTER permission on the table on which the trigger was created) can easily disable the DDL trigger, and disable the DML trigger afterwards. Another way to avoid tracking by DDL triggers is to perform a DML trigger disable/enable change and then delete captured information from the DDL trigger’s auditing repository

Previously described auditing methods, the SQL Server Audit feature and DDL triggers capture more events than needed. All schema (e.g. views, foreign and primary keys, indexes, permissions …) changes are captured, not just enable and disable triggers

The SQL Server Audit feature is not available in all SQL Server editions. Additionally, the described methods need to be manually applied to all SQL Server instances and their databases, and auditing must be maintained, e.g. maintain of captured information repositories, and setting up auditing of any new database

Tracking when triggers are disabled with ApexSQL Log

ApexSQL Log is a recovery and auditing tool for SQL Server databases which audits, reverts or replays data and schema changes that have affected a database. The audit information can be captured even for the operations executed before ApexSQL Log was installed, as it uses database transaction log and transaction log backups already containing information about changes made to a database

To track when triggers are disabled/enabled using ApexSQL Log:

1. Connect to the database you want to audit

   

2. In the Select SQL logs to analyze session step, add transaction log backups and/or detached transaction logs containing the data required to create the full chain, and a full database backup as the chain starting/ending point

   

3. Use the Filter setup session step and the Date/time range section to specify the time frame for the auditing process. This will speed up the reading by narrowing down the search process

   

4. Use the “Operations” filter to narrow down the search to the enable/disable trigger operations: uncheck all DML and DDL operations, and check the Enable/disable trigger option only

   

5. When all filters have been properly set, use the “Open result in grid” option to start the process

After the process is finished, the main application grid shows the transactions that changed the disabled/enabled status of database triggers. The transactions can be exported for later analysis, or rolled back via the Create undo script option, in order to revert the DDL changes

Comparing to SQL Server native tools, in order to audit when triggers are disabled/enabled, ApexSQL Log:

• Shows audited information even for the operations executed before it was installed by chaining different transaction log files together into one
• Does not require any additional action on SQL Server instance/database default settings – it’s enough to keep databases in the Full recovery model and maintain the full chain of transaction logs
• Does not requires T-SQL scripting knowledge
• Does not use any additional system or SQL Server resources in order to capture audit information
• It is SQL Server edition independent
• Converts data to CSV, HTML, XML, or SQL and help you save the content for later analysis

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

Useful resources:

[1] Delete or Disable DML Triggers
[2] Using the Default Trace in SQL Server 2005 and SQL Server 2008
[3] Fire a DDL TRIGGER when the new syntax “DISABLE TRIGGER” is executed
[4] SQL Server Audit (Database Engine)
[5] Create server audit specification
[6] Create database audit specification