Many SQL auditing tools and solutions are available to help DBAs achieve change-auditing and compliance goals. To achieve high performance auditing fit for a specific environment, it is important to consider all of these different SQL auditing tools which use different approaches, techniques and mechanisms to audit various SQL Server operations and events in order to choose and implement a most suitable one. In this article, we are going to look and compare 5 different SQL auditing tools which leverage different SQL Server mechanisms for auditing, including embedded auditing, transaction logs, database triggers, SQL traces and more. These are:
SQL Server Audit
SQL Server Audit is first of SQL auditing tools we’ll examine. It leverages Extended Events in order to audit events on both server and database levels. It utilizes 3 separate components for auditing:
- SQL Server audit object – an object which defines the target of auditing
- Database audit specification – a SQL Server audit object which specifies what exactly is audited on the database-level
- Server audit specification – a SQL Server audit object which defines which exact server-level events will be audited
More detailed information on SQL Server Audit components and features, as well as general information can be found in SQL Server Audit feature – Introduction article.
SQL Server Audit is available on SQL Server 2012 or better for all editions, while also being supported by SQL 2008 for enterprise and developer versions only. Since it leverages Extended Events, the overhead is generally lightweight, but if auditing is configured to pick up large quantities of events in high-traffic databases, as with any of the SQL auditing tools that leverage Extended Events, SQL Server Audit may use more resources and affect SQL Server performance.
Configuring SQL Server audit is pretty straight forward as far as SQL auditing tools configuration goes and doesn’t require big time investment. As mentioned above, it is necessary to create aforementioned 3 components to setup auditing, which can be achieved via SQL Server Management Studio through existing auditing wizards, or via queries and SQL code. Detailed guide on how to configure SQL Server auditing can be found in How to set up and use SQL Server Auditing article.
Once the SQL Server Auditing is configured, audited data will be saved in one of the following, based on the user’s preference:
- Binary file
- Windows event log
- SQL Server event log
Note that output information will be exactly the same, regardless of the chosen output, while output files can be read using SQL Server Management Studio, Windows Event Viewer and Log File Viewer.
More details and recommendations on how to read SQL Server Audit data can be found in How to analyze and read SQL Server Audit information article which offers recommendations and solutions on reporting for this first auditing solution in our SQL auditing tools list.
Pros:
- Light weight
- Easy to setup/configure
- Various output choices
- Audits DML, DDL, Security and other SQL Server events
- Supports all transaction log recovery models (full, bulk, simple)
Cons:
- The audits details don’t specify what exactly is recorded in the main SQL Server audit object component – no event types, objects and databases are specified here
- Minimal info available for audited events – only time, SPID, server name, database name, and object name information is available, while critical information such are client host, IP address and other are missing
- No out-of-the-box mechanisms for deletion or archiving of audited data
- Tool is missing any features that would allow or help with multiple SQL Server instances and every configuration step must be individually performed and manually repeated making this time and error-prone task
- Not supported on all SQL Server versions and editions
See more articles on auditing with SQL Server Audit below
- SQL Server Audit Overview
- SQL Server Audit feature – DDL event auditing examples
- SQL Server Audit feature – discovery and architecture
- Understanding the SQL Server Audit
- Reviewing the SQL Server Audit
- Intro to Auditing in SQL Server
SQL Server Change Data Capture (CDC)
SQL Server Change Data Capture is integrated in SQL Server and is second in our list of SQL auditing tools. It is available in SQL Server 2008 and better for enterprise editions, while standard edition is supported starting SQL 2016 SP1. SQL Server Change Data Capture periodically queries online transaction log file (LDF) in order to read information on before and after change values for insert, update and delete operations. Since reading online transaction log file is lightweight process, SQL Server Change Data Auditing doesn’t affect database performance in most cases. Drawback of this approach is that this kind of asynchronous auditing can delay transaction log file truncation (since it locks transaction log file) – changes marked for capture via CDC can’t be truncated until they are actually audited/captured, and even though this ensures that the auditing data will not be lost, preventing transaction log truncation can make it grow and affect the database operations.
Compared to some previously described SQL auditing tools on the start of our SQL auditing tools list – SQL Server Audit, SQL Server Change Data Capture does not audit most of the events audited by SQL Server Audit, and focuses only on DML operations for which is provides more information and details on audited data – audited information includes exact before and after change values, complete history of changes on a specific row which is not available in SQL Server Audit. Unfortunately, it lacks information on who made the change, when and how.
More detailed information on SQL Server Change Data Capture components and general features can be found in SQL Server Change Data Capture – Introduction article.
Audited information is stored in the repository tables which are created individually inside audited databases and there is no centralization what so ever. In case of repository tables growing too large, a specific cleanup job can be executed to purge the data “cdc.<database_name>_cleanup”.
Full guide on how to setup SQL Server Change Data Capture can be found in the How to enable and use SQL Server Change Data Capture article, the second SQL Server integrated solution in our SQL auditing tools list.
Pros:
- Light weight
- Supports all transaction log recovery models (full, bulk, simple)
- Prevents truncation of data sources (online transaction log) until auditing is completed
Cons:
- Audits only DML operations
- Asynchronous auditing
- Does not audit information on who made the change, from where and how
- No centralization for repository and reporting
- No drill-down mechanisms
- Each database table must be individually configured
- Requires intermediate SQL knowledge to setup and configure
- Not supported on all SQL Server versions and editions
See more articles on auditing with Change Data Capture (CDC)
- ApexSQL Log vs. SQL Server Change Data Capture (CDC)
- ApexSQL Audit vs. SQL Server Change Data Capture (CDC)
- Monitoring changes in SQL Server using change data capture
- SQL Server FILESTREAM with Change Data Capture
ApexSQL Trigger – trigger-based auditing
ApexSQL Trigger is one of 3 ApexSQL SQL auditing tools which we are going to look at in this article. It utilizes database triggers to capture before and after changes on insert, update and delete operations similar to SQL Server Change Data Capture which uses online transaction log as we’ve mentioned before. ApexSQL Trigger is configured using a user-friendly interface and must be manually configured for each database and table, yet the interface is shaped to allow quick configuration making this much pleasurable and faster experience than SQL Server Change Data Capture. In addition, ApexSQL Trigger can also audit schema changes (DDL).
On the data storage, ApexSQL Trigger belongs to both decentralized and centralized SQL auditing tools since it stores audited data inside 2 database tables which can be created in the table being audited, or in a completely separate dedicated database which can be used to store audited data from multiple databases even coming from different SQL Server instances.
Information on the audited data is also more plentiful in ApexSQL Trigger in comparison to previously mentioned SQL auditing tools and the main difference is in the fact that ApexSQL Trigger provides critical information on who made the change and from where they connected which is not available in SQL Server Change Data Capture. Furthermore, ApexSQL Trigger comes with out-of-the-box reports which can be easily checked from the UI or exported without accessing SQL Server directly.
A quick guide on general features and how to setup ApexSQL Trigger, our trigger-based solution in our SQL auditing tools list and configure auditing of SQL databases for DML and DDL changes can be found in An introduction to ApexSQL Trigger article and video.
Pros:
- Quick and easy to set up
- Provides full information on who made the change, when, how, from where etc.
- Audits both DML and DDL changes
- Built-in reporting
- No SQL knowledge required to setup and operate
- Supports all SQL Server versions and editions from SQL 2005 onwards
- Supports all transaction log recovery models (full, bulk, simple)
Cons:
- Database triggers are created inside audited tables
- Not a light weight performance when auditing many tables – all triggers can impose performance degradation
See more articles on ApexSQL Trigger-based auditing in these links:
ApexSQL Log – transaction log auditing
ApexSQL Log is next in live in our list of SQL auditing tools created by ApexSQL which reads not only from online transaction log files, but also from reads directly from transaction log backups and detached LDF files to create a complete history of DML and DDL changes while offering plethora of information for each audited operation. This also means that in addition to performing on-demand and continuous auditing, ApexSQL Log can perform forensic auditing as well and read from the old transaction log backups or detached LDF files which were created even before the tool was installed on the server. ApexSQL Log comes with a user-friendly UI which allows users to create and perform auditing tasks by following through a simple wizard which allows users to choose data sources for auditing, choose various filters and outputs and more.
ApexSQL Log uses continuous auditing feature to perform ongoing auditing tasks to ensure no audited data is duplicated or dropped. This process can be automated to run on a predetermined frequency by utilizing command line interface (CLI) which fully supports all ApexSQL Log features in options available in GUI. So, for those that prefer using CLI, they can completely manage and complete all auditing tasks without accessing ApexSQL Log GUI.
As was the case with the above-mentioned SQL auditing tools, ApexSQL Log also audits DML operations for before and after changes and can show both before and after values on insert, update and delete operations as well as full history of row changes for all audited table fields. While ApexSQL Log uses similar repository approach as ApexSQL Trigger and uses dedicated database tables to store audited data, ApexSQL Log also provides additional output options and can show all auditing results directly in GUI grid where the results can be examined, filtered or saved. ApexSQL Log can also export directly to SQL BULK or SQL script, HTML and CSV, while the audited data includes plethora of information for each audited operation
More information on ApexSQL Log general features can be found in An introduction to ApexSQL Log article and video.
While ApexSQL Log is primarily one of the SQL auditing tools which is a focus of this article, as some bonus features, ApexSQL Log can also roll back (undo) changes based on the information audited from the transaction log files making it an ideal auditing + recovery solution – having an auditing solution which can also be used to recover lost data in disaster scenarios is a huge boon of the tool. Additionally, not only can it roll back changes, but also replay them by creating a redo script, which can be used to replicate the changes on another database, both DML and DDL – making it a viable and easy solution for database replication.
Pros:
- Belongs to light weight SQL auditing tools
- Quick and easy to setup using GUI or CLI
- Various output choices – directly to database, SQL BULK, SQL Script or direct reporting
- Advanced drill-down mechanisms
- Supports all SQL Server versions and editions from SQL 2005 onwards
- No SQL knowledge required to setup and operate
- Provides full information on who made the change, when, how, from where etc.
- Audits both DML and DDL changes
- Forensic auditing – can read changes which occurred before the tool was installed on the server
- Bonus feature – disaster recovery
- Bonus feature – SQL database replication
Cons:
- Audited database must be in a full recovery model – to prevent deletion of the transaction log data used by ApexSQL Log as an auditing source
- Asynchronous auditing
- No out-of-the-box reporting available (repository tables are queried manually)
See more articles on auditing with the SQL transaction log below
- Auditing by Reading the SQL Server Transaction Log
- How to continuously audit transaction log file data directly into a SQL Server database
- Auditing by Reading the SQL Server Transaction Log
- How to continuously audit transaction log file data directly into a SQL Server database
- How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog
ApexSQL Audit – profiler/extended events based auditing
ApexSQL Audit is last but definitely not least from our list of SQL auditing tools we are going to look at in this article. It is the most robust and complete auditing solution of all we’ve presented here, and here is why:
ApexSQL Audit leverages SQL traces in order to audit almost 200 SQL Server events, by far the most of all mentioned SQL auditing tools. It audits all DML and DDL operations, queries (SELECT, SELECT INTO), security related events, backup/restore jobs, warnings and errors on all audited SQL Server instances. Additionally, it can also audit before-after values using database triggers – a feature it shares with ApexSQL Trigger.
ApexSQL Audit is a centralized solution which stores audited data for all audited SQL Servers and database in a single tamper-evident repository database which comes with numerous out-of-the-box reporting while also providing support for report customization. With ApexSQL Audit, it is possible to audit large number of SQL Servers and database within the same domain, and all control and configuration is centralized in an easy to use GUI from which both configuration and reporting can be performed.
ApexSQL Audit is one of active SQL auditing tools which performs synchronous auditing – all events are audited immediately when they are executed on the SQL Server and audited data is directed to the central repository which can be hosted either on audited or not-audited (dedicated) SQL Server.
With the above in mind, it is not a surprise that ApexSQL Audit is a valid solution which can be used to comply with different auditing and compliance standards including PCI, HIPPA, GDPR, SOX, FISMA, BASEL II, FERPA, GLBA, FDA and more.
ApexSQL Audit configuration is two-fold – auditing can be configured using a ‘simple’ filter which allows visual overview of all audited events which need to be ‘checked’ in order to be audited, or via using ‘advanced’ filter which is based on logical conditions which can be combined without limitations to achieve great precision and top granularity which allows ApexSQL Audit to enforce auditing in very specific use cases and environments.
Auditing with ApexSQL Audit is enhanced with several different fully customizable mechanism, more than any previously mentioned SQL auditing tools. All out-of-the-box reports can be run either manually or scheduled to be executed at specific times with dynamic filters. They can also be automatically sent to specific email addresses using SMTP Server to send them. Reports can also be previewed from within the application UI and be created in CSV, XLS, Word or PDF formats.
Additionally, ApexSQL Audit comes with several out-of-the-box alerts which will alert users on any potential issues while the tool is auditing. Furthermore, custom alerts can be created to raise an alert on any auditing event, whether it is an unauthorized access attempt, data loss, permission changes or more – a custom alert will be written inside Windows Event Log and can also be immediately send via email, again, using SMTP as a platform.
More information on ApexSQL Audit general features can be found in An introduction to ApexSQL Audit article and video.
Pros:
- Centralized solution
- Quick and easy to setup using GUI
- Supports all SQL Server versions and editions from SQL 2005 onwards
- Provides full information on who made the change, when, how, from where etc.
- Audits almost 200 SQL Server events
- Alerting on specific audited events
- High-end out-of-the-box and custom reporting
- Tamper-evident repository
- Built-in repository maintenance and archiving features
- Compliance regulation standard templates for configuration and reporting
- Export, share and apply configuration between SQL Servers and databases
- Supports all transaction log recovery models (full, bulk, simple)
- Low overhead
Cons:
- Potential performance degradation when auditing huge quantities of data and running ‘regular’ and before-after auditing processes
- Beginner SQL knowledge required to setup and operate
- Easy to learn, hard to master (large number of options and possibilities)
See more articles on auditing with ApexSQL Audit below
- Capturing who saw what in SQL Serverg
- SQL Server before and after auditing of DML/data changes
- Security and compliance in SQL Server
- How to analyze and read SQL Server Audit information
- SQL Server auditing – how to be alerted about important auditing events
March 1, 2019
