Transcript
Hello and welcome to this ApexSQL Audit video on configuring General Data Protection Regulation (or GDPR) compliance auditing and alerting in ApexSQL Audit.
During the course of this video, we will demonstrate how to configure auditing on all SQL Server events required for GDPR compliance. We will demonstrate how to setup automatic alerts on specific events to allow sufficient time and provide needed information to resolve any potential breaches of data access. We are also going to highlight out-of-the-box reports available which will allow you to demonstrate your organization’s compliance.
Once ApexSQL Audit is configured, compliance with the following GDPR articles will 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)
Configuring ApexSQL Audit to track SQL Server operations required by the GDPR compliance standard is easy.
Let’s first take a look at how to add multiple SQL Server instances and databases for auditing in ApexSQL Audit. Once the application is started, 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.
We can repeat this process to add different SQL Server instances for auditing.
The next step is to add specific SQL Server events for auditing. Even though ApexSQL Audit allows auditing of 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. 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.
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 per additional criteria. 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 we are finished with adjusting our auditing filters, 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.
Now that we’ve seen how to configure actual auditing for the purpose of GDPR compliance, let’s see how to ensure we are alerted in case of critical events or breaches which require appropriate 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.
If you’ve followed this guide and used the GDPR compliance template in order to configure auditing, then 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, we are going to create an appropriate alert, which will be triggered and will send an email notification to specific recipients who will then be able to act accordingly as per GDPR requirements.
To start the process, 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.
In the next step, 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 checkboxes 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 Account link, users will need to first configure the details of SMTP server which will be used to send the actual emails, if this has not been previously configured already. Now, we need to choose the email recipients by choosing the profiles and clicking on the edit profiles button and filling in the recipient details.
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 will complete the process and our alert will be active from this moment onwards and raised immediately if the alert condition is triggered.
Now that we’ve seen how to configure auditing and create alerts which will allow us to comply with the mentioned GDPR articles, lets also look at 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, per demand, for the last year for example, and create a report preview to immediately inspect it. Furthermore, you can also generate reports in one of the four supported formats by clicking on the Generate button and choosing the format which suits your requirements best. You can then use these reports to present your auditing results to the designated data protection authority.
Last, but not least, as per GDPR compliance requirement, users must, at all times, 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. If we check the built-in alerts, we can see that the Central repository database tampering alert is configured to trigger on any tampering event whenever a SQL Server user tries or performs any kind of change on the repository structure or on the actual data. This alert can be edited to also send an email alert in the same manner as we’ve seen when we were creating our custom GDPR alert before.
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.
After short processing, ApexSQL Audit will show if there are any potential tampering events and in case that there are integrity breaches, ApexSQL Audit will provide the full details on who performed it, when and more.
Thanks for watching. For more information, please visit apexsql.com
