How to ensure continuous auditing and reporting in SQL Server

Challenge

Organizations are loaded with regulation requirements to audit and report on data access, data changes, login attempts, malicious and inadvertent activity to easier raise awareness on any anomaly behavior, and repeatedly improve controls while eliminating any potential risk-related consequences.

Continuous auditing and reporting is a great tool for risk assessment and controls improvement. Ideally, every auditor is seeking a hands-free auditing mechanism with the ability to receive contextual reports and alerts frequently and proactively. In this article, we are going to demonstrate how to configure and set up continuous auditing with scheduled reports in SQL Server.

Solution

This article is meant to explain how to ensure continuous auditing and reporting in SQL Server while levering a third-party solution ApexSQL Audit, a SQL Server, and a compliance-ready solution that is capable of tracking around 200 different SQL events. Implementing SQL Server and database auditing can be challenging as it is a process which grows as the maturity of regulation requirements expand.

Define audit goals and scope

While some companies are implementing auditing solutions to track activities on identifiable, critical, personal, and financial data they keep, the others are interested in meeting regulatory requirements such as GDPR, HIPAA, PCI DSS, SOX, etc.

Auditing scope essentially depends on compliance regulations that are defined via respective frameworks for certain industries, also as internal organization requirements. It is so important to define the list of the most important SQL operations to be tracked while combining both compliance and security auditing

After audit scope and goals are defined, it is an almost no-time investment to configure continuous auditing for the SQL Server with ApexSQL Audit. So let’s dive into auditing configuration to meet appropriate auditing needs. For the purpose of this article, we are going to configure auditing to meet GDPR compliance guidelines while expanding it with a bit more security auditing on top

1. The first step is to add SQL Server in the auditing list, to do so run ApexSQL Audit application interface, visit the Configure tab and continue by clicking the Add server button

   

2. In the next step, add the server by typing the SQL Server name or simply select it from the drop-down list and click Add

   

3. In the next step, configure the agent properties and collection session details

   

4. After this, the SQL Server is added and ready to be configured for auditing by specifying what operations, databases, applications, and logins are included for tracking. The configuration can be customized in three different approaches:

   • Simple filters
   • Advanced filters
   • Out-of-the-box pre-defined configuration compliance-ready templates

   In this article, we are going to use a mixture of pre-defined GDPR configuration templates and simple filters

5. A compliance template can be applied for both server and database-level configurations in one edition set by navigating to Compliance drop-down and selecting the appropriate template

   

6. In the next step select SQL Servers and databases and click Apply

   

7. The GDPR compliance template is now applied for the selected server and database. We are going to add some more configuration changes and filter conditions on the server-level by selecting couple security-related operations and including certain application and logins

   

From this point, continuous auditing is configured to track and collect data based on the configuration. In the next section, we are going to explain how to ensure continuous reports of audit events.

Auditing data review

Reviewing data proactively is recommended practice, unlike retro-actively reviewing audit data in post-disaster stages. With that approach, it will be easier to consume audit data, review less data at the time, and timely act on any suspicious activity that may negatively impact security or compliance, continuous auditing gives that ability.

The best way to achieve this goal is to plan frequent and scheduled review tasks. ApexSQL Audit is a go-to solution for creating and scheduling as many custom reports. For the purposes of this article, we are going to demonstrate how to build, run, and schedule the GDPR reporting template on a custom schedule.

Creating a schedule report starts with building a reporting template that will be used as a base to retrieve audit data, therefore here is the quick guide on how to create it

1. Select Reports tab in the main application ribbon and click New, select the template from the drop-down that can be used as a base for further customization

   

2. Reporting templates can be customized and filter data on a huge span of details to easily specify exact filter criteria on which data will be extracted

   

3. Now when the reporting template is built, the next step is to create a scheduled configuration for how and when the report will be created. New schedule configuration is created via Schedule tab in the main ribbon and clicking the New button

   

4. The first step in the schedule wizard configuration adds reporting templates for automation

   

5. The next step brings a robust frequency configuration to define the frequency

   

6. Output formats, e-mail profiles, and output destination are the things of the next step. The reports can be extracted and formatted in multiple formats such as Word, PDF, Excel, and CSV and then forwarded via an-email notification

   

7. In the next step, schedule configuration is assigned with a custom title

   

8. The last step of the wizard introduces a quick summary of the schedule configuration to help with a quick overview of the current set up before saving it

   

With this, continuous auditing and reporting for SQL Server are established. Scheduled reports help to effectively communicate with multiple teams, database administrators, security, and any other to consume audit data reports and real-time notification alerts to prevent any critical activity.