SQL Server audit and GDPR compliance: Configure, alert and report [How to]

Are you responsible for making sure that your SQL Server Environment is GDPR-compliant?

With Quest® ApexSQL Audit, you can configure General Data Protection Regulation (GDPR) compliance auditing, alerting and reporting for your entire SQL Server environment.

GDPR compliance is as easy as 3 steps: Configure, alert and report

In this blog, I'll show you how easy it is to configure auditing on all SQL Server events required for GDPR compliance.

In this blog, I'll show you how to set up a SQL Server audit required for GDPR compliance for all events.

Also, learn how to set up automatic alerts on specific events to allow sufficient time and provide required information to resolve any potential breaches.

We will also highlight out-of-the-box reports available which will allow you to demonstrate your organization’s compliance.

Compliance with five GDPR Articles

Once ApexSQL Audit is configured, compliance with the following GDPR articles can be met including:

• Article 5 – Principles relating to processing of personal data(Chapter II Principles)
• Article 24 – Responsibility of the controller (Chapter IV Controller and processor)
• Article 25 – Data protection by design and by default (Chapter IV Controller and processor)
• Article 32 – Security of processing (Chapter IV Controller and processor)
• Article 33 – Notification of a personal data breach to the supervisory authority (Chapter IV Controller and processor)

All of this information will be stored in a tamper-evident repository.

Step 1: Configure GDPR auditing

Configuring ApexSQL Audit to track SQL Server operations required by the GDPR compliance standard is easy.

You can add multiple Servers and databases all through the UI. Simply click on the Configure button in the main ribbon.

Next, click on the Add server button and provide the SQL Server name, either by choosing it from the drop menu or typing it manually, and click Add again.

In the Agent properties form, provide a valid Windows administrator name and password, and optionally edit any of the Advanced options available and finally click on the OK button to complete the process.

This process can be repeated for additional servers.

Add specific SQL server events, use the built-in GDPR compliance template

The next step is to add specific SQL server events for auditing. While you have the capability to audit almost 200 SQL Server events, most of these are not required in order to comply with GDPR, which makes filtering out unnecessary operations helpful.

While we can add all required events separately by checking each and every operation for auditing, it is far simpler and much faster to use the GDPR compliance template that is built right into ApexSQL Audit. Ensure that your SQL Server instance is selected in the Server pane on the left and click on the Compliance drop menu and choose GDPR.

As you can see, ApexSQL Audit provides many templates.

Next, in the Apply profile form we can choose to apply the selected GDPR template on both the SQL Server level, as well as on the database level. So, simply check all of the databases which need to be audited per GDPR compliance requirements and click the Apply button.

While this can be the actual end of the GDPR auditing configuration, in many cases we need to further filter in or out additional criteria.

Custom filter options to include or exclude

We can choose specific applications and logins to be excluded if, for any reason, they are not required to be audited for GDPR. To add these filters, simply click on the Application or Login tabs and choose to include or exclude specific ones from the auditing task.

We can do the same for the SQL Server objects on the database level. First select the database in the left panel and then click on the Objects filter in order to include or exclude specific objects from the auditing job by ticking appropriate checkboxes next to the objects names.

Once done, click on the Apply button to complete the configuration process.

From this moment on, ApexSQL Audit will be auditing all relevant GDPR operations and will store them in its tamper-evident repository which can be directly queried or accessed via the same ApexSQL Audit UI in order to extract reports.

Step 2: Alerting

Now that we’ve configured auditing for the purpose of GDPR compliance, let’s ensure we are alerted in case of critical events or breaches which require actions based on the GDPR regulations. As defined in GDPR, in case of any data breach, including access, processing or data loss, organizations must provide full information, on the event, to the designated data protection authority as well as the customers affected by the data breach in a maximum of 72 hours following the incident. Therefore, it is of great importance to be immediately alerted on such events to allow designated personnel to act on the breach and ensure they comply with the requirement.

At this point, unauthorized access (audit login failed) is already being monitored and will be audited by ApexSQL Audit with all of the available details, including who made the attempt, when, from where and more. To make sure we have enough time to react on any unauthorized access attempts, let’s create an alert, which will be triggered and send an email notification to specific recipients who will then be able to act accordingly as per GDPR requirements.

Click on the Manage alerts button in the main ribbon. Next, click on the New button to initiate the alert creation wizard. In the first step, choose to create an Auditing alert.

Next, we can customize the alert title and description, but the most important task is to uncheck the limitation to the number of notifications per minute, which is checked by default for all alerts, in order to ensure we are alerted on each failed access attempt.

Click Next to proceed. Now, select the SQL Server instances that will be monitored by checking the check boxes next to the SQL Server instance names and click Next.

Now, we need to actually define the alert condition, which is, in our case, to alert on any server operations in the security group named “Audit login failed”.

Click Next and now check the “Send this alert report via e-mail” option.

Clicking on the Next button takes us to the alert summary where we can examine our alert and also choose an alert name.

Clicking on the Finish button to complete the process.

Step 3: Reporting

Now that we’ve seen how to configure auditing and create alerts which will allow us to comply with the aforementioned GDPR articles, there are several built-in reports which are needed in order to present auditing results when required.

While there are several reports which can be used to demonstrate compliance with specific GDPR articles, including the Unauthorized access report and Logon activity history report (both for GDPR Article 5), ApexSQL Audit also has an out-of-the-box report which will pull the data from the repository for all operations which are audited for the purpose of GDPR compliance.

Simply click on the Reports button and choose New report and click on the GDPR report.

Next, add any filters, and create a report preview. You can also generate reports in one of four supported formats by clicking on the Generate button and choosing the appropriate format. These reports can be used to present your auditing results.

And lastly, users must be able to demonstrate the integrity of the audited information. Since the ApexSQL Audit central repository is a tamper-evident database, we can perform this check both manually and automatically.

There is a built-in alert that is triggered whenever a SQL Server user tries to or performs any kind of change on the repository structure and/or on the actual data. This alert can be edited to also send an email alert.

Additionally, ApexSQL Audit can demonstrate auditing trail integrity on demand. This is done by first clicking on the Verify button in the main menu. Next, ensure that the online repository is checked, as well as any of the previously created repository archives which need to be submitted for the integrity check and click on the Start button to initiate the process.

ApexSQL Audit will show if there are any potential tampering events. In case that there are integrity breaches, ApexSQL Audit will provide the full details on who performed it, when and more.

As you can see, ApexSQL Audit is an easy yet powerful tool to ensure that your SQL Server environment is GDPR-compliant.

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!