How to easily pass SQL Server audits

Internal and external audits are becoming a mandatory requirement in companies world-wide, SQL Server audits included. There are many global auditing compliance standards like HIPAA, GDPR, PCI, which require companies to audit numerous events, including data changes, access and more. Furthermore, internal management and auditors often come up with additional auditing requirements, of which, the following are the most frequently represented requirements when performing SQL Server audits:

• Data changes
• Schema (object) changes
• Security and permission changes
• Access to the raw data
• And more

While SQL Server itself offers some internal mechanisms and potential solutions for SQL Server audits, including SQL Server Auditing, SQL Server Extended Events, SQL Server triggers, Change tracking, Change data capture (CDC) and more, all of the mentioned mechanisms allow only partial auditing of some of the many SQL Server events without additional mechanisms for alerting, reporting, and often come up with difficult requirements and knowledge of SQL Server itself to set up and operate, especially when SQL Server audits need to be implemented on large number of servers and/or databases. This is where ApexSQL Audit, a SQL Server auditing tool that enables auditing of almost 200 SQL Server events, comes into play.

Let’s take a deeper dive into ApexSQL Audit and check some of the key features that will enable users to setup SQL Server audits on many instances and databases and achieve compliance with multiple internal and external auditing standards and requirements.

Audit a SQL Server

Starting from the top, after going through a simple wizard which installs ApexSQL Audit and allows auditing of multiple SQL Server instances across the domain in a manner of minutes, the first job when configuring single or multiple SQL Server audits is to add a SQL Server itself and configure which exact events will be audited based on the auditing requirements defined by management or auditors.

Note that when the server is being added, users can choose one of the tree available auditing mechanisms which the tool will leverage as an integral part of its auditing system:

• SQL Traces
• Extended Events
• SQL Audit

SQL Server instances are added individually and appear in the server tree on the left (1) while the exact event to be audited are shown in the middle (2) of the screen.

User can configure:

1. Operation filters – across four groups of events, including DDL (schema changes), Security events (including login events, permission changes and more), execute operations, as well as backup and restore related events
2. Application filters – include or exclude specific applications from the auditing job
3. Logins filter – where auditing can be configured on the pre-login level

Additionally, in order to further define the auditing trail, ApexSQL Audit offers the ‘SQL text data’ filter which allows users to specifically define if they will audit SQL text data or impose a limit to exclude some of those very large SQL texts where needed:

Note that auditing configuration can be changed and updated whenever the need requires it, and that it is done seamlessly without any kind of pause in auditing job.

Database auditing

Following the same approach as when configuring auditing on the server level, the database level has the same UI for configuring SQL Server audits on database levels – databases are first added to the list, and then individual events are ‘checked’ to be included in the auditing job.

On the database level, ApexSQL Audit can be used in order to capture events and operations, including:

1. DDL (schema change) operations
2. DML (data change) operations
3. Queries
4. Security-related operations and events
5. Execute jobs
6. Warnings
7. Errors

Additionally, auditing can be configured on the object level as well by using the object filter, which allows including or excluding specific objects in/from the auditing job:

In those cases where there are common auditing requirements, based on the international standards, on n both server and database levels ApexSQL Audit offers compliance templates that can be used to quickly configure auditing to achieve compliance with PCI, GDPR or any other auditing standards:

Alternate auditing

Instead of using the simple UI with high visual appeal and simplicity to configure auditing on server or database levels, ApexSQL Audit offers a different approach, specifically designed for complex auditing requirements where elevated granularity and precision are required.

The “Advanced” filter is based on logical conditions and allows users to combine an unlimited number of conditions in order to achieve complex auditing setups, which is especially useful in those situations where there are multiple compliance requirements to fulfill:

Before-after auditing

Another, and completely separate, method of auditing available in ApexSQL Audit is the “Before-after auditing”. This feature is designed in order to allow auditing of exact table field values on any of the regular DML operations (insert, update, delete) and will capture both the ‘original’ value before the change, as well as the new value.

For example, if we have the “Salary” column in our table, and someone changes the value from $2,000 to $5,000, ApexSQL Audit will show both the value before the change and after the change. Additionally, all relevant information, including who made the change, when, how and more, will be included in the audited data.

As already mentioned, before-after auditing has a separate configuration screen, which is pretty straight forward. First, add the databases to the ‘list’ and then choose exactly which tables will be included in the before-after auditing job. Furthermore, by using the appropriate checkboxes, auditing can be configured directly on the column level:

Reporting

Now that we’ve seen how to put the gears to motion and configure auditing, let’s take a quick glance into reporting capabilities of ApexSQL Audit. Out of the box, there are 20 standard reports which are commonly required and used by the majority of users. Furthermore, all of these reports can be used as a starting point to create custom reports as per the user’s needs and then saved for future use:

All of the SQL Server audits can be previewed within the GUI and filtered further before being exported in one of the supported formats – PDF, Word, Excel, CSV. Users can also choose which exact information will be included in the report by including specific columns and adding both operation and details information to achieve great precision:

In addition to manually creating reports, ApexSQL Audit comes up with a scheduling feature that allows users to configure automatic creation of specific reports on a pre-determined frequency. Furthermore, these auto-created reports can also be forwarded to multiple email recipients (via SMTP) which can also be defined in each reporting schedule:

Alerting

Another important part of SQL Server audits is being alerted on critical events which can negatively impact production or raise a red flag to DBAs or other departments on an event which can influence auditing job or jeopardize production environment. These events are usually revolving around permission changes, deletion/drops of specific data or objects, unauthorized or unexpected access to the data and more.

ApexSQL Audit, again, comes up with several built-in alerts that are focused on the well-being of audited environment (production) and auditing job as well. These alerts will notify users if their attention is needed to ensure that the issues like low disk space, connection interruption or similar need their immediate attention. Aside from the built-in alerts, ApexSQL Audit allows users to configure an unlimited number of alerts to receive notifications on important events. These alerts can be configured on literally any audited event. Whether you need to be alerted when an object is dropped, data is changed, or some permissions are made different, using the custom alerts and setting up these important notifications to trigger on critical events is easily achievable by following the simple yet powerful wizard which will lead the user through setting up alert details, configuration as well as define how the notification will be forwarded to users, with email (via SMTP again) being the top pick here:

Security (tamper-evident repository)

Last but not least, let’s quickly address the data safety of all SQL Server audits made by ApexSQL Audit. While the tool allows auditing of multiple SQL Server instances from a single central point, all the data is stored within one central repository database, which is tamper-evident. This means that the ApexSQL Audit repository that stores the audited data is designed to recognize any inadvertent or malicious changes to the audited data. Additionally, ApexSQL Audit will raise alerts and notification on any potential tampering with the audited data and capture information who did it, when and how.

In conclusion, with the above-mentioned features, ApexSQL Audit is a complete solution which enables users to easily pass SQL Server audits and achieve compliance with many and multiple auditing standards and ensure that the audited data is protected while still being easily accessible to the users with its plentiful reporting mechanisms and options.