Auditing SQL Server data changes – the centralized solution

In the previous part of the Methods for auditing SQL Server data changes articles series, How to analyze and read SQL Server Audit information, we described several native SQL Server auditing features – Change Tracking, Change Data Capture and Audit. We described their unique and shared characteristics, how they store captured information, how to provide the information, and explained the advantages and disadvantages of each feature.

In this chapter, we’ll focus on ApexSQL Audit, a SQL Server auditing and compliance tool designed for tracking data and object access and changes, unauthorized access attempts, and security changes. It uses a SQL Server traces technology (also used by SQL Server Profiler), but enhances the way it is natively used by additionally parsing captured information, and providing tamper-evident centralized audited information storage.

ApexSQL Audit provides one centralized GUI that is used for setting up auditing configuration in one place. Auditing is possible on SQL Server instance, database and object levels

Using the ApexSQL Audit GUI, auditing can be set on a server using the Server filter settings section for DDL and security events. On the database level, using the Database filter settings, Operation types and Objects sections, fine granular selection can be set for auditing operations, events, and database objects.

ApexSQL Audit captures more than 200 operations and events, available in the following auditing categories:

• DML – INSERT, INSERT BULK, SELECT INTO, WRITETEXT, UPDATE, MERGE, UPDATETEXT, DELETE, and TRUNCATE TABLE
• DDL – ALTER, DROP and CREATE statements for all object types, including rename and other similar operations like enable/disable trigger, Revert, Update Statistics…
• Execute – calls to function, stored procedures, CLR procedures, and extended stored procedures
• Query – SELECT, SELECT INTO, READTEXT, and output parameters from EXEC
• Warning – all operations issued as warnings by SQL Server
• Security – all operations affecting security of a SQL Server instance or its databases
• Error – all operations reported as errors by SQL Server

Additional filtering is provided within the ApexSQL Audit’s reporting system.

The audited information is captured on the SQL Server instance hosting the ApexSQL Audit central repository database, or on other additional (local or remote) SQL Server instances. In case of remote servers, the captured data is automatically transferred and stored to the ApexSQL Audit’s central repository database.

Unlike the previously described native SQL Server auditing features, ApexSQL Audit uses a centralized repository for the captured information on multiple SQL Server instances and their databases.

Moreover, the central repository database is a SQL Server database with all the derived benefits (e.g. availability, security …), and its location (a SQL Server instance that hosts it) is customizable – selected during the installation process.

The installation process wizard consists of several steps, as described in the Deploying ApexSQL Audit online knowledgebase article.

In the following example we’ll see how to setup auditing on a particular SQL Server instance, its database, and to track DML operations. In our case, we’ll audit a SQL Server instance named SQLINSTACE01, its database AdventureWorks2014, and three database tables (PersonCreditCard and PersonPhone). To do that, using ApexSQL Audit GUI:

1. Select desired SQL Server instance
2. Add the database for auditing
3. Check the DML option in the Operations section
4. Select the Include objects option and choose the tables that need to be audited
5. Confirm the changes by selecting the Apply option

For testing purposes, we’ll insert a new row to the PersonCreditCard table:

INSERT INTO [Sales].[PersonCreditCard] (
	[BusinessEntityID]
	,[CreditCardID]
	,[ModifiedDate]
	)
VALUES (
	123
	,17038
	,2013 - 07 - 31
	)

To see captured information, click on the Reports tab in the main GUI or start the stand-alone reporting application.

The stand-alone reporting application offers access to ApexSQL Audit reports from anywhere in the network allowing employees who should not have access to the central instance and the configuration of the filters a way to pull reports out.

It offers many built-in common reports divided into several groups, and a custom reports designer module.

The built-in common reports can be further filtered by specifying additional parameters (a specific SQL Server instance, database name, login used, etc.), or by specifying the operation type (e.g. Select, Merge, Delete, Alter, Insert, etc.)

For our example, to see the captured INSERT operation:

1. Select the DML history template from the New report dropdown, under Change and activity auditing
2. Set appropriate filter parameters and click the Preview option
3. The resulting report will look like:

If for any reason (e.g. different report design) a custom report is required, you can use the custom report designer, to create a specific report.

ApexSQL Audit captures information that includes the time of the performed action, the user name that performed the action, affected server, database and the object that was the target of the action.

It provides the complete history of actions for the table data changes with fine grained auditing and reporting setup. The reporting module is user-friendly, and doesn’t require any T-SQL knowledge.

ApexSQL Audit comes with the centralized GUI and captured information repository that provide multiple benefits when it comes to setting up auditing on multiple SQL Server instances (local and remote), and repository maintenance (e.g. built-in archiving feature).

The following table provides comparison between the auditing methods we described in this articles series:

*Required for deeper analysis and reporting

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

Useful resources:

MSDN – SQL Server Audit Action Groups and Actions
MSDN – CREATE SERVER AUDIT (Transact-SQL)
Auditing database-level objects in SQL Express 2012
MSDN Work with Change Data (SQL Server)
MSDN – Change Data Capture Tables (Transact-SQL)
MSDN – cdc.<capture_instance>_CT
MSDN – Overview of Change Data Capture
MSDN – Change Data Capture and Other SQL Server Features
MSDN – Enable and Disable Change Data Capture (SQL Server)
MSDN – Enable and Disable Change Tracking (SQL Server)
MSDN – Change Tracking
MSDN – Comparing Change Data Capture and Change Tracking