Various techniques to audit SQL Server databases

SQL Server auditing has gone from a nice to have to a legal requirement, especially following new legislation like HIPAA and GDPR. Organizations are now tasked with auditing access to records, reporting suspicious and potentially malicious activity, forensically auditing data changes, as well are tracking login attempts, security changes and much more.

SQL Server auditing can be broken into several techniques:

Manual auditing – this might involve a set of queries and possibly reports to track activity per table, transactions by users, recent changes to sensitive tables etc. But, in addition to being time consuming, it will be virtually impossible to scale this to all possible auditing events

SQL Server Extended Events – as the ultimate replacement for SQL Server profiler and traces, extended events offer several advantages including built in GUI tools and potentially better performance. Extended events can audit a wide range of actions, but suffers from some deficiencies like not being able to provide information on what was deleted or inserted. Also, there is no means for before-and-after auditing to compare new and old values for updates.

SQL Server triggers – these have been a staple for years. They can be set up easily and track a variety of information. Triggers lend themselves to full customization allowing users to build their own auditing information repositories. Triggers are an intrusive technology and can throw errors to your client applications when they break. They aren’t recommended for high throughput or bulk insert tables/operations and maintenance of a trigger based layer can be time consuming.

Note: To automatically add template based trigger based DML (and DDL) auditing to SQL Server databases – see ApexSQL Trigger

SQL Server transaction logs – The transaction log in SQL Server is like the black box of an airplane. It will record everything that occurs, which lends itself well to purposes like auditing. There is no additional overhead as this is already a built in process in SQL Server. But log files are notoriously difficult to read and even when they can be the data isn’t organized for easy consumption and much of it is in hexadecimal format. See the article Read a transaction log, for more information on various solutions

SQL Server Profiler and SQL Server traces – this has been the go to technology for auditing in SQL Server for years but it tends to produce voluminous amounts of information and is slated to be deprecated. Read more about this here Is this the end of SQL Profiler?

SQL Server Audit – this feature has been around since SQL Server 2008 and offers rudimentary, “aggregate” auditing capabilities like who made a change and when, but doesn’t offer other information, which today would be considered essential, like what was actually changes

Change tracking – this SQL Server feature is a step above SQL Server Audit but requires some experience and understanding of the underlying table structures and only tracks changes to the primary key, making it not so useful for anything other than very superficial audits

Change data capture (aka CDC) – this is SQL Server’s improvement on Change tracking and is available in the Standard edition since SQL Server 2016. CDC offers much better information and auditing capabilities than Change tracking but lacks a user interface to make viewing and processing the information easy. T-SQL knowledge will be required to query the tables and pull information

Temporal tables – This is another feature SQL Server has introduced that offers a complimentary solution to CDC. With temporal tables you can see a full history of changes and it can also be used for recovery purposes. The feature does require the creation of history tables for each auditing table and requires interaction with T-SQL to view results. See Concept and basics of Temporal tables in SQL Server 2016 for more information on this feature

Which solution or combination of solutions is appropriate? It depends on your auditing requirements, time for setting up and maintain audit trails, as well as storage, security and reporting requirements

Manual auditing

Running scripts to audit for certain events or activity is something most DBAs have done, at one time or another. The collection of ad hoc scripts can build over time to a powerful toolkit especially combined with native functionality like CDC and Temporal tables. Many such open source scripts can be found on the internet. But in general such solutions are free only if your DBA works on a Pro Bono basis. Otherwise, they can be time consuming and costly to produce and maintain, especially if the owner of this solution leaves the company or changes to another position. So we’ll forgo discussions of such manual solutions.

Utilizing SQL Server Extended Events

SQL Server Audit is a SQL Server feature, first introduced in the version 2008 that uses SQL Server Extended Events to audit SQL Server actions. It enables auditing different actions, providing much granularity in the setup process and covering a wide range of the SQL Server activity

To create a new SQL Server Audit object:

Expand Security and right-click Audits in SSMS
Select New Audit
You will need to create a name for the audit, and then indicate whether to store the audit data in an application security event log, event log or a file. Finally, indicate a location for the audit file
Click OK and your audit will appear in the Audits node of the Object Explorer
By default, it’s disabled. The disabled status is indicated by a red arrow. Right-click and select Enable Audit, to enable
Depending on whether you want to audit the activity on an entire SQL Server instance or just a particular database you will choose between Server Audit Specification or Database Audit Specification
For a Database Audit Specification, expand the node of the database to audit, go to Security, right-click Database Audit Specifications and select New Database Audit
In the Create Database Audit Specification dialog, indicate the specification name, associate the specification with the audit object created in the previous step, specify the activity to audit in the Audit Action Type. For auditing a particular database, indicate the database, object, or schema as an Object Class, the name of the audited object, and the audited login

In the drop-down list for Audit Action Type, you can see all actions that can be audited using SQL Server Auditing

In this dialog, you will specify the user accounts to be monitored.

Database audit specifications are disabled, by default. To enable them, select this option in the context menu

Now, all DELETE statements executed against the Person.BusinessEntityAddress will be audited and inserted into files the names of which start with Audit-, such as Audit-AW2012Test_9D93CA4A-8B90-40B8-8B0B-FCBDA77B431D_0_130161593310500000.sqlaudit, and stored in E:\

For high volume databases, it is considered a good practice to save audited info to a file. This file can’t be opened directly though, even with a hex editor. To view it, use fn_get_audit_file

For example:

1 2	SELECT event_time,action_id, statement, database_name, server_principal_name FROM fn_get_audit_file( 'E:\Test\Audit-*.sqlaudit' , DEFAULT , DEFAULT);

shows the following results:

The results will include columns but not the actual deleted data. The user, who made the deletion, and when the deletion was made are the main audit elements here, which is a disadvantage for users who need more comprehensive audit data.

Other disadvantages include:

SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance
It is difficult to comprehensively manage multiple instances and consolidate the audit data.
There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting.
This feature isn’t available in the standard version of SQL Server until SQL Server version 2016.

Using SQL Server triggers

SQL Server triggers are perhaps as old as SQL Server itself (don’t quote me on that). As per their name, they are artifacts in the SQL Server engine that “fire” on a particular even such as the insertion of a new record. Triggers exist for both DML (data) and DDL (schema) operations and because they are T-SQL based can be fully customized and integrated into your database directly.

A trigger can exist for only one table, so you will need to create and maintain at least one trigger for each “sensitive” table you wish to audit. As triggers are user-defined objects T-SQL must be written to specify what data to capture and ultimately what to do with it. A table or tables will need to be created, to which the triggers will point and deliver the audited information they have captured. Then this audit trail information can be queried and reported on.

To illustrate we’ll use an example. In our case, a trigger that is fired after a record was inserted into the Person.Person table inserts a table name, time and date when the record was inserted and the user name used to insert the record into a dbo.Repository table should look like this:

CREATE TRIGGER PersonPerson_I

ON Person.Person

AFTER INSERT

INSERT INTO dbo.repository (

TABLE_NAME,

TABLE_SCHEMA,

AUDIT_ACTION_ID,

MODIFIED_BY,

MODIFIED_DATE,

[DATABASE]

)

values(

'Person',

'Insert',

SUSER_SNAME(),

GETDATE(),

'AdventureWorks2012'

) GO

Although built-in and effective, triggers are difficult and time consuming to create, as you may need hundreds or more, and to maintain, as they will need to be updated each time the underlying table is changed. The use of 3^rd party tools to automate the creation of triggers based on templates can help

ApexSQL Trigger is a SQL Server database auditing tool for capturing data and schema changes including who made the change, which objects were affected, when the change was made as well as the information on the SQL login, application and host used to make the change. It stores all captured information in a central repository table. Audit data can be reported on and exported. Triggers are based on templates that can be customized. Once done, each trigger will be created perfectly, with no errors and thousands of triggers can be created in seconds. If the underlying database changes, affected triggers can easily be refactored.

To create a SQL Server database trigger based audit trail with ApexSQL Trigger, follow these steps

Start ApexSQL Trigger
Connect to the database to audit
In the main grid, select the table(s) to audit
In the Columns pane, select the column(s) to audit
Check the transactions to audit – including Insert, Update and/or Delete
Repeat the steps 3 to 5 for all tables you want to audit
In the menu, click Create triggers
The script that generates the specified triggers is shown in the Script dialog. Check it out and press F5 to execute it against the database to create the triggers.

Once the triggers are created, they will be fired for every subsequent INSERT, DELETE and UPDATE executed against the table, from that point on, and the details of the operation are stored into pre-built user-defined tables.

Reporting is easy with built-in reports, and the audit tables can be queried directly as well

Triggers are very powerful building blocks of a good SQL Server audit solution but downsides include the amount of time to create and maintain them. For high transaction tables, triggers can also add unneeded performance overhead.

Reading transaction logs

If you ever wanted a one-stop-shop for comprehensive SQL Server transaction auditing information, the transaction log would probably be the first place you would look. By definition, it must maintain a history of everything executed against SQL Server as every data and schema change is automatically added to the online transaction log. Simply gaining access to this information can act as an audition solution in and of itself.

The challenge is that the transaction log was never meant for human eyes. Open transaction log data sources like the online log, detached logs or log backups is challenging but once opened, reading them is even harder. Several options exist to read the transaction log including fn_dblog, fn_dump_dblog, and DBCC PAGE. For more information on these options see the article: Read a transaction log

Each of these solutions have some pretty severe limitations in that the information they display is unorganized and difficult to process. Some information is presented as hexadecimal, which is hard/impossible to read and auditing for things like Updates and Blogs is also very challenging

Fortunately, there is a 3^rd party tool that was created to abstract the complexity of decrypting log files, from users, as it can read the files and convert it into helpful and easy to read information, presented in a grid that allows for additional manipulation

To read transaction logs, use a SQL Server transaction log reader such as ApexSQL Log. It audits, reverts or replays data and object changes that have affected a database, including those that have occurred before ApexSQL Log installation. It also captures information on the user, application and host used to make each change

Start ApexSQL Log
Connect to the database you want to audit
In the Select SQL logs to analyze step, add the transaction log backups and detached transaction logs you want to read. Note that they have to form a full chain in order to provide successful auditing
Use the Filter setup options to narrow down the result set using the time, operation type, table’s name, user and other filtering options
Click Open
The results are shown in the main grid and you can easily create undo and redo scripts, or export them into CSV, HTML, XML or SQL files and save on the hard disk

The advantages of using the SQL Server transaction log as an auditing mechanism include:

no overhead, since there are no additional processes for capturing the audit information that can affect SQL Server performance.
an audit can be performed for a period of time even before the tool was installed
the tool can be set up to run unattended, and nightly to capture a continuous record of auditing events directly back into a SQL Server table for direct querying
many value added features like filtering, sorting, reporting, exporting etc

The disadvantages are:

a database has to be in the full recovery model, and a full chain of transaction logs must exist
databases that use TDE or AlwaysEncrypted can’t be read
not all actions that a user might want to audit are stored in a transaction log. For example, SELECT statements

Using SQL Server Profiler and tracing

Technology exists in SQL Server to provide a running audit of every operation performed and event that occurs, in the form of SQL Server traces. Setting up the SQL Server Profiler to create such traces is a viable method to audit your databases but do the fact that the amount of data produced can be voluminous and that this technology will be deprecated by Microsoft, it isn’t a good long term solution

Extended events are the successor to SQL Server profiling and promises a more viable technology, one that Microsoft has committed to for the future. Extended Events cover all of the events found by profiling but without producing GBs of audit files that must be processed

The challenge is to cover SQL Servers which only support profiling and also those that only cover extended events, although there is an overlap of versions. A solution meant for profiling will have to be abandoned, as a company transitions to Extended events. Also, there is a lack of value added functionality like alerts, reporting, exports, customization, interface with productivity features etc that necessitates a lot of time to turn these baseline technologies into an enterprise auditing solution.

Fortunately, there is a 3^rd party tool that can bridge the gap between profiling and extended events and puts a thick layer of value added features and functionality on these underlying technologies to provide a turn-key auditing solution, right out of the box

ApexSQL Audit is a SQL Server auditing tool built on SQL Server traces and extended events that provides “who saw what, when” type information. The ApexSQL Audit auditing ecosystem also includes fault tolerant auditing, centralized reporting, user friendly interface for setting auditing on more than 230 operations, and a temper-proof centralized repository for storing audit records and configuration safely. It configures traces according to configurable settings but can commence auditing immediately after install using its default configuration that covers most common auditing requests

Start ApexSQL Audit
Click the ‘Add server’ in the Configure tab to select a server for audit
Click the ‘Add database’ button to select a database for auditing, and select server or database operations you want to audit.

Another option is by using the Advanced filter type:

Now, whenever any operation you selected is performed on audited SQL Server, a record will be saved in the central repository database

To see auditing records, you can use a built-in local reports

ApexSQL Audit is truly an Apex predator in the auditing ecosystem. It offers a fully functional and feature rich interface that abstracts DBAs from extensive setup, configuration, maintenance and reporting requirements. It offers key enterprise features like fault tolerance and tamper resistance that allows you to meet stringent auditing requirements. With web based reporting, even remote users can view audit reports and participate in compliance audits.

Technique	Advantages	Disadvantages	Suits best when
Manual auditing	Flexibility, customization	Coding Development Cost Long implementation Usually undocumented Labor costs Lack of continuity	A specific auditing solution is needed and no ready-made tool can be used
SQL Server Auditing	Flexibility A large number of action types audited Easy to set No additional cost	No deleted, inserted, updated records Can affect performance Not available in all SQL Server versions and editions Primitive interface Difficult to configure over multiple tables, databases, instances	Enterprise, Developer or Evaluation SQL Server editions, when detailed auditing is not necessary, and no info about the records affected is needed
Using SQL Server triggers	Easy to set up Customizable Can be integrated into client software	Effort to create and maintain triggers Can cause overhead in a high transaction database	Not all tables and DML operations need to be audited; auditing data need to be easily accessed and queried Commercial software products that need to be self-audited When auditing needs to be integrated into client apps
Reading transaction logs	No additional overhead DML and DDL changes can be audited Can show records that were affected Row history and before-and-after	Difficult without a log reader Not all actions are audited (security, queries, executes, logins, etc.)	High transaction environments with short downtime, where affected records must be seen, and changes rolled back
Using SQL Server Profiler and SQL Server traces	Flexible Already available in SQL Server	Voluminous data store requirements Will be deprecated	A wide range of SQL Server database actions must be audited. It’s recommended to have a tool designed to read traces, filter results and generate reports

In this article we reviewed a host of SQL Server auditing techniques, described their advantages and disadvantages and provided walk-thrus for some solutions.

For several of these solutions, 3^rd party solutions exist to add significant value to the option, at times making it more viable to consider.

For example, ApexSQL Trigger mitigates one of the main problems with triggers which is how time consuming they can be to create and maintain.

ApexSQL Log cuts through the myriad of problems and challenges normally associated with SQL Server transaction log reading.

And finally, ApexSQL Audit bridges the gap between SQL Server profiling and extended events, while providing major value add with advanced features and functionality

Nesha Maric

Sales Engineer at ApexSQL

Nesha is an IT engineer and SQL enthusiast which specialized in SQL Server auditing, compliance and disaster recovery from 2012 onwards.

He is a passionate and competitive gamer and experienced fisherman with a keen eye for perfection and high standards and expectations for anything he invests his time into

View all posts by Nesha Maric

Using SQL Server triggers

Reading transaction logs

Using SQL Server Profiler and tracing

References

Related posts: