ApexSQL Audit: Sneak Peek – Part I

We are announcing the release of a new SQL Server tool – ApexSQL Audit, which will bring a wide range of possibilities for auditing access, changes, and security on SQL Server instances, databases, and objects

(*)The tool will consist of three modules:

  • the ApexSQL Audit application, service and Central repository database
  • the web console reporting system
  • the Distributed Comply instances

The main application will require one SQL Server 2008 R2 or later instance for installation and, while auditing that SQL Server instance, it will also be used to set up auditing options, maintain the centralized repository, and configure auditing of other SQL Server instances. Any additional SQL Server instance to be audited will require a local installation of the Distributed Comply application

Audited operations and actions

The main application will be used to set up auditing options for each audited SQL Server instance and database which include operations and objects

The Server filter will allow auditing of DDL operations as well as security operations performed on the server

The Database filter will allow selecting any existing user database on the specified server. Furthermore, operations performed on the selected database and its objects can be individually filtered – selected for auditing

Operations on a selected database are divided in several sections among which are:

  • Query (SELECT)
  • Execute
  • Security (Authentication changes, Permissions changes, Attempted logins)

The Objects filter will allow selection of these SQL Server objects:

  • Tables
  • Stored procedures
  • Functions
  • Views

Database Filter Settings

Quick fine-tuning of the exact auditing configuration will be possible via visual filtering:

Visual filtering

Captured information

Depending on the operation performed on a server instance or a database, the captured information will provide data on what was executed, who did it, the name of the host used, etc.

Audited storage

ApexSQL Audit will use a centralized auditing repository – all audited data will be stored on one SQL Server and in one centralized database – no matter how many SQL Server instances are being audited. This way, it will be easier to backup and protect captured data from malicious operations

The central Repository Database will store and allow querying of all auditing events captured by Distributed Comply instances per user’s configuration. It will also store all the configuration options, as well as the history of configuration changes, which is often useful during the auditing process

Security Data will be stored in the Central Repository database in the same form as it is captured to allow direct and quick querying of the same. To ensure data integrity, ApexSQL Audit will implement a sophisticated tampering-evident algorithm that allows detection of any tampering even when it’s done by trusted parties. The algorithm will leverage standard hashing algorithms and database version tracking to make any tampering on a table, row group, or row level evident to external auditors

Auditing performance

ApexSQL Audit will leverage SQL Server’s tracing technology to capture SQL statements executed on the server. Distributed Comply instances were designed from the ground up to leverage multicore architecture of today’s servers, and its throughput is limited by actual throughput of the SQL Server and its tracing subsystem. By default, ApexSQL Audit will be configured to capture the most essential information namely DDL and DML statements, permission changes, and the like, as described above

Supported systems

ApexSQL Audit will support Windows XP and higher, Microsoft SQL Server 2005, 2008, 2008R2, and 2012. Note that the main application will require the SQL Server 2008R2 version or higher

And that’s not all. Stay tuned for more sneak peeks of this upcoming ApexSQL tool and its reporting system

(*) – Disclaimer: This article is a preview of a product still in development/production, as such, its content is subject to change and may not reflect the final release

See also

Sneak Peek: ApexSQL Audit – Part II

April 8, 2013