How to implement compliance with the PCI DSS regulatory standard for SQL Server – Part 4

In part 1 and part 2 of this series information was provided on how to configure ApexSQL Audit to accomplish PCI requirements from 3 and up to 8, while in part 3 the addressing requirements 10.1, 10.2 and 10.3 of the PCI DSS 3.1 standard via ApexSQL Audit was explained

In this part, the rest of the PCI DSS 10-Track and monitor all access to network resources and cardholder requirements section will be described and as well as some requirements from section 12 that can be met using the ApexSQL Audit. This article is based on the latest PCI DSS 3.1 compliance regulation

The PCI DSS requirements that will be covered in this article include:

10.5 – All audit trails/logs must be secure so they cannot be altered or, if altered, then any altering of the trial must be evident

10.5.1 – Only persons in charge for the PCI DSS compliance or individuals with a business related need should be allowed to have insight in the audit log

10.5.2 – Audit logs, in any form, must be protected from unauthorized modifications and each incident of tampering of audit log must be evident and logged

10.5.3 – Audit logs must be stored to a central repository database that prevents altering or makes such tampering, if not impossible, then difficult and also evident

10.5.5 – Each change of the audit log must be detected and the person in charge must be alerted when such an activity occurs

10.6 – It requires the regular inspection of the logs. Logs and security events for all system components, must be reviewed to identify anomalies or suspicious activity

10.7 – Requires implementing a retention policy for audited data which implies that audited data for at least three months should be available immediately for reviewing, while audit trail history has to be retained for one year at least

12.2 – Requires establishing of procedures that will be executed on a daily basis with the aim to ensure the consistency of the PCI DSS operational application

12.9.1 – Requires creating of the incident plan and procedures that will be executed in the event of system breach. The plan and procedures must be capable of responding immediately to a system breach in order to ensure the process of business continuity and recovery, data backup processes, analysis of the all critical SQL Server components

10.5 Secure audit trails so they cannot be altered 1

10.5.1 Limit viewing of audit trails to those with a job-related need 1

The ApexSQL Audit central repository database is designed to be stored on a dedicated SQL Server hosted by a dedicated computer, which ensures internal segregation from the rest of the system and makes easier implementing the adequate protection of the audit data, including strong access control limited to “need to know” persons only. The web based reporting ability of ApexSQL Audit is designed to allow access to audited data from different computers, but is limited to the internal local area network only, isolating audited data from external-facing technologies, thus making the risk of those data being accessed or altered significantly lower, as they are more secure within the internal network.

Additionally, ApexSQL Audit allows establishing of additional protection to allow or deny certain user access to the ApexSQL Audit GUI, as well as to limit access to audited data via web reports to selected users only

10.5.2 Protect audit trail files from unauthorized modifications, 10.5.5 Use file-integrity monitoring or change-detection software on logs to ensure that existing log data cannot be changed without generating alerts (although new data being added should not cause an alert) 1

Besides the ApexSQL Ability to check for the integrity breaches within the audited data via the Verify integrity feature, and the ability to send alerts when such an unwanted event occurs, ApexSQL Audit additionally applies encryption of the audited data values collected and stored using before-after auditing. The before-after auditing process imposes strong data encryption for all audited data values, from the moment of data collection up to the storing of data into the central repository database. Meaningful modification of stored data is made very difficult and would require significant effort and time. Additionally, any malicious access to actual data values, stored in the central repository database, with the purpose of reading those data is prevented by allowing the intruder to see only obfuscated data

The before-after data stored, in in the central repository database can be read only using ApexSQL Audit reports. In this way, ApexSQL Audit establishes adequate protection of audit data, required by the PCI DSS, and facilitates maintaining data completeness and accuracy, while at the same time data ensuring that audited data integrity can be guaranteed

10.5.3 Promptly back up audit trail files to a centralized log server or media that is difficult to alter 1

ApexSQL Audit can archive the central repository database into another database which can be moved to a dedicated archiving SQL Server or into dedicated file storage. The ApexSQL archives are fully tamper evident, which minimize the workload that should be otherwise imposed on users to maintain and safeguard the archived date. The central repository database archives usage is not time limited, meaning that user can make the decision when a specific archive is no longer needed so that it can be purged from the system

10.6 Review logs for all system components at least daily. Log reviews must include those servers that perform security functions like intrusion-detection system (IDS) and authentication, authorization, and accounting protocol (AAA) servers (for example, RADIUS), 12.2 Develop daily operational security procedures that are consistent with requirements in this specification 1

This PCI DSS requirement is generally related to ability of ApexSQL Audit to accurately report on any changes, and to facilitate the review of the created reports is required on the daily basis.

Besides the fact that ApexSQL Audit is capable of auditing and logging any SQL Server associated event, its ample reporting can ensure precise displaying of all required information according to this requirement. All reporting can be made in a consolidated form with the option to create fully customized reports that can match to any special requirements imposed by internal or external auditors

It is not unusual that many SQL Server and cardholder data related breaches are detected days or months after the actual breach occurred, and thus the daily review of precisely defined reports minimizes the time period between the potential breach and its exposure

ApexSQL Audit reports comprise all the relevant event information needed to ensure quick identification and proper qualification of individual events. The image below displays the information available for the collected SQL Server events

ApexSQL Audit has two report types, common reports and custom reports. Design of these report types allows meeting all PCI DSS reporting related requirements, which includes fulfilling the need for any specific individually defined reports. Detailed information about comprehensive ApexSQL Audit custom reporting are available in the ApexSQL Audit Feature highlight: Custom reports article

10.7 Retain audit trail history for at least one year, with a minimum of three months immediately available for analysis (for example, online, archived, or restorable from back-up) 1

This requirement is specifically related to audited data retention. Keeping audited data available for a minimum of one year should ensure that late detected breaches can be thoroughly investigated by allowing investigators enough historical data to better determine the time period when a breach occurred, and/or repeated, and to create the more precise projection of the actual impact on the system(s) affected.

PCO DSS consider that a three-month period of retaining data is enough for immediate analysis and identification of the breach. This is considered sufficient enough to minimize the impact of data breach

An additional advantage of ApexSQL Audit is that archived databases can be easily added as a data source for reporting

The reporting system will then intelligently scan all added data sources for required information and within the time frame defined in the report’s filter

In this way, ApexSQL Audit allows immediate access to archives and thus to the data stored there, which makes much easier accomplishing and even significantly surpassing the requirement for at least three months’ period of instant availability of audited data

12.9.1 Create the incident response plan to be implemented in the event of system breach. Ensure the plan addresses the following, at a minimum: 1

  • Specific incident response procedures
  • Business recovery and continuity procedures
  • Data back-up processes

ApexSQL Audit can help in implementing this requirement for the above mentioned plan specifics. With the ability to quickly generate precise reports, ApexSQL Audit can help establishing precise procedures based on specifically relevant information for locating the root causes of the incident. This facilitates that an appropriate and adequate response procedures with minimal delay

ApexSQL has another tool that can resolve the situation when cardholder data are compromised in a way that they are altered, deleted or new data injected. ApexSQL Log is a SQL Server transaction log reader that can rollback data and object changes that have affected a database, which also includes changes that had occurred way before ApexSQL Log was installed. This can significantly speed up the business recovery and continuity. More about data recovery can be read in How to recover SQL Server data from accidental UPDATE and DELETE operations article. Of course, the business recovery and continuity plan must include regular database backups creation. ApexSQL Backup, another tool included in the ApexSQL DBA bundle, facilitates automation of database backup creation which also includes the management of created backups to enable point-in-time database rollbacks

In addition, ApexSQL Audit can track and log all backup and restore operations and then to create reports that will allow insight in the backup process, which should allow auditors to inspect and review that the backup policy is maintained and accomplished. Naturally, ApexSQL Audit can also track and report on all database backup restore activities. The report has information about who, when and what as well as whether backup/restore attempts were successful or unsuccessful

To audit backup and restore activities, use the following simple filter

All backup and restore operations for each database in the target SQL Server will be logged

If auditing of all or only specific databases are required, the Advanced filter’s granular auditing can ensure that

To audit backup/restore for all databases set the following

To audit backup/restore only for specific databases


With this particular filter, the backup and restore events will be logged for databases AdventureWorks2014, DefaultDb1, QBase and bagetyv2, listed in the Database name condition

Below is a report example on the backup operations for AdwentureWorks2014. Text data contain necessarily information about backup type (full or differential backup) the exact time of backup creation

Reviewing this report in regular time frames can ensure timely identifying whether the backup operations are executed in scheduled time or not


January 22, 2016