Audit SQL databases using a SQL Server trigger tool (4262402)

SQL Server Audit Overview

In this rapidly changing IT world, auditing the SQL Server database becomes a vital task of each database related project, to make sure that the data is stored and maintained securely. In this way, the customers will be happy to deal with such a secured company that stores their sensitive data securely.

For more information about the database audit concept, check the SQL Server Audit Overview.

There are different methods to audit databases in SQL Server, as shown below:

• Writing the events to the SQL Error Logs and C2 Trace files
• Taking advantage of events stored in the system objects and review it using the Ad Hoc Queries
• Auditing the database DML and DDL changes using the deprecated SQL Profiler
• Auditing the database using SQL Trace deprecated feature
• Auditing the database level events using Extended Events
• Auditing the DDL changes, DML changes and server Logons using SQL Server Triggers
• Auditing database changes by reading the SQL Server Transaction Log file
• Auditing table data changes using Change Data Capture
• Auditing table data changes using SQL Server Change Tracking
• Auditing events at the SQL Server and database levels using the SQL Server Audit feature
• Auditing table data changes using System-Versioned Temporal Tables
• Auditing SQL Server and database level changes using 3^rd party tools, such as ApexSQL Audit

For detailed information on how to audit the SQL Server databases using the mentioned method, check the SQL Server Audit series.

SQL Server Trigger Overview

SQL Server trigger is a special type of stored procedure that is automatically fired and executed when a specific event occurs at the SQL Server instance, database, or table level. SQL Server provides us with three types of triggers: The SQL Server Logon trigger that fires a stored procedure in response to a LOGON event, the DML trigger that is fired as a response for a data insertion or modification action, such as INSERT, UPDATE or DELETE action, and the DDL trigger that is fired as a response to a schema changes actions, such as CREATE, ALTER, DROP, GRANT, DENY, and REVOKE statements.

The SQL Server Trigger response to these actions can be in different ways. For example, the trigger can be used to execute a script after executing the statement that fires that trigger. On the other hand, the trigger can be used to prevent executing the statement that fired it and execute a different statement instead of the firing statement.

SQL Server triggers can be used as a database auditing solution, to track and log different types of Logon, DML, and DDL operations to a custom auditing table, or proactively prevent the users from performing a specific action on the database table and log the prevented action. This is due to the fact, SQL Server provides us with a special type of virtual tables called Inserted and Deleted that can be used to keep the column values before and after the modification. In the end, the complexity of the auditing solution here depends on your SQL development skills.

In general, it is not highly recommended to use the SQL Server triggers as an auditing solution, especially for the heavy transaction production tables, as executing the trigger after or instead of each statement will cause an additional overhead on the SQL Server resources that may lead to a real performance problem.

Audit Using ApexSQL Trigger

ApexSQL Audit is a 3^rd party tool that can be used to track and audit different types of DML and DDL change at a specific SQL Server database and show what changes performed, who performed it, and when this action is performed. It can also be used to translate data into meaningful information using the lookups and add extra data to the audit trail using the watches. Also, the ApexSQL Trigger provides us with the ability to generate different types of filtered and exportable audit reports that contain the data values before and after the audited change.

ApexSQL Trigger can be downloaded from the ApexSQl Download page, and installed to your server or machine easily, using the straight-forward installation wizard. During the ApexSQL Trigger installation wizard, you will be asked to agree on the license agreement, specify the path where to install the ApexSQL Trigger tool, then it will be installed in a few seconds without any complexity, as shown below:

Getting started

When the ApexSQL Trigger tool is installed completely to your machine, you can start a new project by clicking on the New icon, at the top left part of the tool, that will ask you to provide the name of the SQL server that hosts the database to be audited, and the credentials that will be used to connect to that SQL Server instance, as shown below:

If you are using ApexSQL Trigger for the first time, the tool will notify you to manage the structure of the ApexSQL Trigger tool, by specifying the repository where the audit data will be stored, connect to the database that you will audit, rather than connecting to the default master database, and finally specify the architecture components that will be used to audit that database, as shown below:

When the ApexSQL Trigger architecture is configured completely, it will connect to the database to be audited and show all the database tables grouped by the database schema name.

To audit a list of database tables, check the name of these tables to be audited under the schema name, select the columns that will be audited under that table and whether to audit the INSERT, UPDATE or DELETE operations executed on these tables.

After making and reviewing your choices, click on the Create button, under the Triggers options group, to generate a script for the SQL Server Triggers that will be used to audit these tables, as below:

In the displayed Script window, review the T-SQL script for creating the SQL Server Triggers that will be used to audit the selected database tables, then click on the Execute option, under the SQL options group, to run the script and create the SQL Server Triggers, as shown below:

Now, the SQL Server Triggers that will be used to audit the database tables are created successfully. This means that any INSERT, UPDATE, or DELETE statement executed on these tables will be caught and logged to the audit repository.

Let us perform some data changes on these tables, by adding new records, deleting an existing record and modify some columns values and see how these actions are audited.

ApexSQL Trigger Viewer

To view the audit logs for the performed changes, you can use the list of reports provided by the ApexSQL Trigger tool, such as Standard and Aggregate reports, or use the ApexSQL Trigger Viewer free tool to browse the audit data.

ApexSQL Trigger Viewer is a free reporting interface that provides you with the ability to connect to a specific ApexSQL Trigger repository and query the audit data by grouping, sorting, and filtering the data and export it to multiple files types such as Excel, CSV or PDF.

ApexSQL Trigger Viewer can be downloaded from the ApexSQL Download page, and installed to your machine easily, using a straight-forward installation wizard, in which you only requested to accept the license agreement and specify the path for installing that tool.

To query the audit data using ApexSQL Trigger Viewer, you need to connect to the audit repository, where the ApexSQL Trigger tool keeps the audit data, by clicking on the New button and provide the name of the SQL Server instance where the repository database hosted, the name of the repository database and the credentials that will be used to connect to that instance, as shown below:

After connecting to the repository database, define the filter by filling the simple filter values, or draw your own advanced filter by providing a WHERE clause, then click on Apply to retrieve the list of audit records that meet your filter criteria, as shown below:

If you plan to review the audit data later or provide it to the security team, you can easily export the audit data by clicking on the Export option and choose the type of the generated file, from the available list, as shown below:

In addition to the detailed display for the audit data, ApexSQL Trigger Viewer provides you with an Aggregate report, that groups the audit data by tables and show the number of actions per each table, as shown below:

Audit Schema Changes

ApexSQL Trigger provides us with the ability to audit the DDL changes on the audited database. To enable the DDL audit, click on the Manage option, under the Architecture options group from the Advanced tab, then choose to include the DDL reporting stored procedures and click Install to install and enable it, as shown below:

To verify that the DDL audit is enabled, click on the Manage option under the Triggers options group of the Home tab, and check from the DDL section at the bottom of the window that the DDL audit is enabled. Also, you can check it from the status bar beside the connection information, where the DDL icon will be displayed, as shown below:

After performing a number of DDL changes on the database tables, we can use the ApexSQL Triggers Viewer tool again to review the DDL audit data, by clicking on the Schema option, under the Reports options group, fill the simple filter values or write your own WHERE clause in the advanced filter, then click on the Apply option to view the DDL audit data, with the ability to export it to different files types from the Export option, as shown below:

Conclusion

We discussed the ApexSQL Trigger (a third-party tool) in this article and learned how it is used to audit your SQL Server Database DML and DDL changes, by creating SQL Server Triggers on the audited tables.