How to ensure continuous auditing of SQL Server with zero audited data loss

An optimal continuous SQL Server auditing approach must include:

1. Continuous auditing
2. Real time data collection
3. Ability to generate meaningful reports
4. Alerting on unwanted activities
5. Tamper proof store of audit data

In many cases, the primary requirement that must be fulfilled is that auditing must be performed with zero auditing data lost.

And this is where 3^rd party auditing solutions like ApexSQL Audit can help.

ApexSQL Audit

ApexSQL Audit is a SQL Server auditing and compliance tool that audits over 200 SQL Server events and stores collected data into a single tamper-evident central repository database. Captured information is available through a range of built-in reports with an additional custom report designer to meet specific user requirements. It enables enterprise-wide auditing and compliance from a single client, while reduces security risks with a real-time alerting.

ApexSQL Audit is designed to meet the requirement for continuous auditing in SQL Server. The application is carefully designed to prevent any loss of audited data even in situations of hardware or software failure. ApexSQL Audit is designed to ensure the maximum continuity of audited data flow even in cases of network or database failure.

ApexSQL Audit system components and descriptions

The ApexSQL Audit system includes a central instance that installed on the same machine where the central repository database resides. The independent auditing instances are installed on each machine that hosts the audited SQL Server instance, as illustrated in the diagram below.

The ApexSQL Audit central instance is a Windows service that can be best described as an intelligent communication node that receives, sends and distributes information among the various application components. The ApexSQL Audit central instance archives the audited data received from the auditing instances into the central repository database. Unlike the central instance, the audited instance is in charge of direct auditing of the SQL Server instances, processing the audited data and forwarding it to the central instance.

At the beginning of the auditing process, SQL Server events/changes to be audited must be selected. This can be accomplished via the auditing filters located within the ApexSQL Audit GUI according to end user auditing requirements.

Once the user sets the auditing filter for each auditing SQL Server instance, the GUI will pass the configuration of the auditing filter to the central instance, which will process it and distribute the appropriate auditing filter configuration to each auditing instance.

Each auditing instance is designed to work as an independent entity on the computer that hosts it, initiating the auditing of the SQL Server and collecting audited data.

Note: The ApexSQL Auditing instance is a Windows service that runs as a Windows OS component on the machine that hosts the SQL Server instance. It is not a component of the SQL Server and ApexSQL Audit does not install any auditing agents/components on the audited SQL Server instances.

The collected audited data is processed and filtered locally by the auditing instance itself before being forwarded to the central instance. Only the data required in accordance with the received auditing filter configuration will be forwarded to the central instance, thus creating minimal impact on the network.

Auditing continuity in the case of network communication failure

A challenge arises if communication between the central and auditing instance is disrupted (i.e. due to network issues). At the moment when the communication is disrupted, for any reason, the auditing instance will start to temporarily store audited data to the local hard drive.

Regardless of how long the communication between the central and auditing instances is disrupted, as long as any disruption exists, the auditing instance will continue to store the audited data to a local file system. Therefore, it is necessarily to ensure sufficient free space on the audited server’s hard drive.

Note: To ensure the maximum data safety, granting a dedicated HDD storage for the auditing instance is highly recommended. The location for the temporary files is configurable during the installation of the auditing instance.

As soon as the communication between the auditing and central instances is established again, the auditing instance will immediately start to resume sending the audited data stored in the local file system to the central instance. After the central instance send back confirmation that a data package is received, the auditing instance will delete that data from the hard drive, releasing the temporarily occupied file system space.

Auditing continuity in the case of central auditing instance database failure

An additional ApexSQL Audit safety mechanism is available to ensure continuity in situations when the SQL Server instance that hosts the central repository database experience issues. In the situation when the ApexSQL Audit central instance cannot communicate with the central repository database, and therefore cannot store that audited data received from auditing instances, a mechanism will be triggered to ensure the instance store all audited data in a local file system – until such time as the communication with the central repository database is no longer interrupted.

After communication is reestablished with the central repository database, the ApexSQL Audit central instance will start with transferring audited data from the file system, into the central repository database.

Note: The same principles valid for the auditing instance HDD storage configuration are applicable here as well. Adequate HDD storage space size must be ensured, for the central repository.

The combination of safeguards against remote and/or local instance database or network failure, implemented in ApexSQL Audit, ensures reliability of auditing data and auditing process continuity.