In the previous part of the Using SQL Server traces for SQL Server auditing series, we described SQL Server traces technology, what it provides in terms of SQL Server auditing, and how it can be utilized via SQL Server Profiler and the native SQL Server fn_trace_gettable system function.
Although the previously described native tools provide access to tracing technology results, these solutions do not provide an out-of-the-box solution:
- Auditing for multiple SQL Server instances must be setup manually, individually on each SQL Server instance
- Capturing must be manually scheduled, since the trace files can get recycled
- Captured information is not stored in a central repository
- T-SQL knowledge is required in order to see the captured information
- Different columns are of interest for different event types. For example, the DatabaseName field is not related to the Audit Login Failed event, yet the Audit Login Failed event has a value in the DatabaseName field, so it cannot be omitted. The bottom line is that redundant information is captured and saved.
ApexSQL Audit is a tool for SQL Server auditing and compliance, including objects and data access and changes, failed logins, and security changes. ApexSQL Audit leverages SQL Server traces (the same technology used by SQL Server Profiler). ApexSQL Audit adds additional processing of captured data, fault tolerance for a number of scenarios, automatic transfers and loading into centralized repository, and so on.
Based on the user configuration set through the ApexSQL Audit GUI, it configures traces or uses the default configuration – a set of common auditing requirements with optimal usage of the central repository database.
ApexSQL Audit captures more than 200 operations, separated into following 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
- Execute – calls to stored procedures, extended stored procedures, CLR procedures, and functions
- Query – SELECT, READTEXT, and output parameters from EXEC
- Security – all operations that affect security of a SQL Server instance or one of its databases
- Warning – all operations issued as warnings by SQL Server
- Error – all operations issued as errors by SQL Server
The ApexSQL Audit reporting module that comes as part of the main software package can be installed as a stand-alone application to any machine on the network as well to provide access to reporting for non-technical personnel or personnel that should not have access to the central instance and filter configuration.
It provides more than 20 built-in common reports divided into several groups and a custom report designer.
The built-in common reports can be sorted and filtered by specifying additional parameters (a specific SQL Server instance or database name, login used, etc.), and by selecting the operation type (e.g. create, drop, delete, alter, truncate, etc.)
For example, the following Unauthorized access report corresponds to the failed logins, and it’s available in a single click:
Any specific report that is not provided among the common reports can be created using the custom report designer based on logical conditions for all possible event sources and events for maximum granularity.
By specifying values for each filter field, the custom report will provide the exact range of required information. The following operators can be used to build filters:
- Logical operands: And, Or
- Conditional operators: is, is not, contains, does not contain
The logical operands can form blocks allowing for complex conditions.
Besides having the ability to view reports in the reporting module, they can be exported into several common formats, such as PDF, Word, or Excel.
As the auditing process is constant by default (unless interrupted by an admin), the amount of audited captured data can be significant. Unlike SQL Server Profiler, ApexSQL Audit provides archiving captured information in order to:
- preserve HDD storage space used by the centralized auditing repository, and
- maintain low impact on the SQL Server instance hosting the centralized auditing repository while creating reports
In addition, ApexSQL Audit provides other value-added features like tamper-evident design, an out-of-the box solution for a number of compliance requirements (HIPAA, SOX, GLBA, etc.), multiple SQL Server instances auditing, and more.
Useful resources:
[1] Microsoft SQL Server 2008 Bible, Wiley Publishing, Inc.
[2] Introducing SQL Trace
[3] Create and Run Traces Using Transact-SQL Stored Procedures
[4] Default trace enabled Server Configuration Option
[5] SQL Trace
This article is part of a series
See previous part See all parts
November 8, 2013
