Part 1 and part 2 of this article described configuring ApexSQL Audit to meet PCI DSS 3.1 standard developed to ensure security of cardholders’ payments and data. In this part, the requirement sections 10-Track and monitor all access to network resources and cardholder will be described. For more details about differences between PCI DSS 2.0 and PCI DWW 3.0 and differences between PCI DSS 3.0 and PCI DSS 3.1, check the official PCI Security Standards Council LLC documents Summary of Changes from PCI DSS Version 2.0 to 3.0 and Summary of changes from PCI DSS Version 3.0 to 3.1
The PCI DSS requirements that will be covered in this article include:
10.1 – For an organization it is critical to establish a procedure or method capable of ensuring the exact relationship between the SQL Server components accessed and the specific user, with specific focus on those users with administrator privileges. The established system must be capable of generating the required audit logs and to provide the ability to trace each instance of suspicious activity to a specific user
10.2 – The focus of this requirement, regarding the SQL Server, is to ensure, via interviews, if the persons in charge of the PCI DSS implementation and maintenance, observation of the generated audit reports and examination of auditing settings is able to perform the following requirements:
10.2.1 – Each and every access of individual users to cardholder data stored in the SQL Server database must be logged and verified
10.2.2 – Every action on SQL Server by users with elevated privileges must be logged and verified, with focus on those users with “root” and “administrator” accounts, such as DBAs and sysadmins for example
10.2.3 – This is a strict requirement with a goal of ensuring that any unauthorized or malicious access to the auditing log with the purpose of changing any part of the log must be identified
10.2.4 – To meet this requirements, the auditing system must be designed to track SQL Server for single or multiple invalid login attempts
10.2.5 – Each and every SQL Server user account change must be tracked and logged, including any account addition or deletion, as well as any attempt of bypassing or impersonating a valid account. Each of the following sub-requirements must be implemented:
10.2.5.a – Requires that logging of any identification and authentication mechanisms used on SQL Server is itself audited and logged
10.2.5.b – Each and every SQL Server user privilege elevation have to be audited and logged
10.2.5.c – Any change, addition and/or deletion of every SQL Server login account with any administrative type privileges assigned must be audited and logged
10.2.6 – Any stopping or pausing of the auditing process or audit log generation must be logged and verified by the person in charge for PCI DSS compliance implementation
10.2.7 – Every creation and deletion of SQL Server system level objects have to be audited, logged and verified
10.3 – This requirement is explicitly related to the previous 10.2 requirement and derives directly from the results of the implemented 10.2 requirement. This requirement requires that every audited event described in 10.2 must have at least the following details:
10.3.1 – Who it was that performed the logged event
10.3.2 – What the type of the logged event was
10.3.3 – Date and time when event was executed
10.3.4 – Indication of the event execution success or failure
10.3.5 – The client host used for executing the event
10.3.6 – Affected data, system component, or resource name or identity
10.5 – All audit trails/logs must be secure so they cannot be altered or 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 tampering of audit log must be evident and logged
10.5.3 – Audit logs must be stored to a central repository database that is difficult to alter
10.5.5 – Each change of the audit log must be detected and person in charge alerted when such an activity occurs
10.1 Implement audit trails to link all access to system components to each individual user
ApexSQL Audit is a SQL Server compliance and auditing tool, designed to allow simple and easy compliance of organizations with PCI DSS regulation. It features an extensive array of options for auditing of SQL Server access, objects and data altering, and security changes on server, databases, objects, and data level, including the who, what, when and where information. It audits executed queries and logs belonging T-SQL text. ApexSQL Audit can also audit and log all SQL Server warnings and errors. ApexSQL Audit can audit Microsoft SQL Server 2005 or newer.
Just by implementing ApexSQL Audit as a PCI DSS compliance solution the 10.1 requirements will be mostly, if not completely, accomplished
10.2 Implement automated audit trails for all system components to reconstruct the following events
10.2.1 All individual user access to cardholder data
Using a valid account with enough privileges or creating a new unauthorized account by individuals with malicious intent can severely compromise cardholder data security and validity. By logging and reviewing the audit records of all individual accesses to cardholder data, this will significantly facilitate in identifying the compromised SQL Server user accounts and help identifying any misuse of same
To track all users that are accessing the cardholder data use the simple filter in ApexSQL Audit:
Chose the databases that contain the cardholder data for auditing via Add database
Use the following filter settings for the auditing of the chosen databases
In a situation when only some tables contain the cardholder data, the Object filter allows exclusive auditing of those specific tables. The filter permits only the user specified tables to be added for auditing
Select the Include objects radio button and then Chose objects to open the Add objects to filter dialog. Add tables that contain cardholder data for auditing
Use the Type dropdown menu to select the Table as the object type and then add, in the include list, the tables that should be audited by using the >> button. Once the filter is set, only the tables listed in the right pane (Include objects) will be audited
The procedure must be repeated for each database that contains the cardholder data. Note that the presented filter is for this specific requirement and that final auditing filter settings will be probably different if it has to match the other PCI DSS requirements that affects those databases
The advanced auditing filter that matches this requirement for one example database is the following
For each database, the filter should be set to match this but potentially also the specific internal requirements or other PCI DSS requirements depending on of the particular PCI DSS compliance implementation
In addition, defining the real-time alerts for these requirements will ensure that the necessary critical information, will be provided, to persons in charge of the PCI DSS implementation, on any undesired change or access to cardholder data. Alerts created for tables that store the cardholder data will be triggered on any access to cardholder data or by specified SQL Server users.
Cardholder data is, in general, not prone to frequent change, so each and every change or access of the unauthorized users could present potential data compromise. This is critical in preventing, detecting, or minimizing the impact of a data compromise and should be verified by authorized persons as soon as possible
10.2.2 All actions taken by any individual with root or administrative privileges
This requirement primarily means that SQL Server logins with elevated privileges should not be excluded from any auditing activities, and this is particularly valid for all administrator accounts. This means that PCI DSS doesn’t recognize the category of the so called trusted users and the more privileges that are assigned to specific a SQL Server account the more scrutiny will be required.
All such accounts have a significant potential to considerably impact the security and functionality of SQL Server. Auditing all activities, an organization will be in position to identify any issue as a consequence of an administrative mistake or abuse of privilege to the specific event and individual
This requirement is basically related to a regular review of the collected audited data with emphasis on users with elevated privileges. ApexSQL Audit provides the required login information in a generated reports allowing security officer to identify any activity that can compromise the SQL Server stability and/or security.
ApexSQL Audit custom reports allows easy reporting on such users individually. To create a report set the following filter, as shown below, for each user and save it
Create and save such reports individually for all users that are covered with this requirement, or create a single aggregate report to cover all named users. After saved, reports can be quickly generated in a single click that will allow tracking of every inappropriate activity to the user involved
10.2.3 Access to all audit trails
The most frequent way malicious users hide their actions is by attempting to alter audited data. Having a record of data changes in audit log allows tracings for any data corruption or tampering to an individual login.
ApexSQL Audit can meet this requirement, as its central repository database is designed as a tamper evident and secured using the strongest SHA-256 hash encryption with chaining algorithm.
Besides being able to check for the data integrity of the central repository database manually via the Verify integrity button, ApexSQL Audit can be set to automatically check the repository database integrity and to alert and sent notification to the security officer when tampering is detected
To establish and define the Central repository database integrity breach alert, chose Alerts in the GUI’s main ribbon, then select the Manage tab.
By default, the Central repository database tampering alert will be listed as enabled. The alert can be altered to meet the desired preferences or predefined policy. Use the Edit button in the menu or just double-click on it in the list
Edit the text according to individual preferences or company rules and press Next
The Check interval dialog allows setting the date and time for the initial integrity check of the repository database and the scheduling time period on which the operation will be executed regularly
Enabling the email notification is highly recommended for this particular alert.
In addition, for enterprises that are using their own reporting and alerting systems via harvesting the Windows Event Log, the Write this report to Windows Event Log will additionally write each raised alert into the Windows application log
All central repository database integrity checks will be logged and can be previewed using the Audit integrity checks report (shown below)
Integrity breaches can be reviewed in the Verify integrity dialog after accomplishing the integrity check
ApexSQL Audit archives are also tamper evident and the integrity of the archived data can be checked as well. In this way an obligation of security officer to organize the safeguarding of archives and manually check for potential by themselves is eliminated
10.2.4 Invalid logical access attempts
This requirement is established to ensure that all invalid and failed attempts to access the SQL Server are audited and logged, with specific emphasis on multiple failed login attempts. Individuals malicious actions often consider multiple access attempts on SQL Server. Multiple invalid login attempts are often a clear indication of an illegal attempts of an individual to use a “brute force” techniques to compromise a password. All failed logon attempts must be audited for every SQL Server that is under PCI DSS compliance in order to fulfill this specific requirement
To track failed login attempt via the ApexSQL Audit simple filter, set the following for each audited SQL Server:
To audit using the advanced filter
10.2.5 Use of and changes to identification and authentication mechanisms – including but not limited to creation of new accounts and elevation of privileges – and all changes, additions, or deletions of accounts with root or administrative privileges
10.2.5.a Verify use of identification and authentication mechanisms is logged, 10.2.5.b Verify all elevation of privileges is logged, 10.2.5.c Verify all changes, additions, or deletions to any account with root or administrative privileges are logged
This requirement is concentrated on guaranteeing the necessary auditing and logging of all login or user-related changes on a server level or a database level together with any role and permission altering.
All logins and users must have appropriate level of privileges which is designed to grant access to cardholder data only for specific individuals, according to security rules
The Server level auditing filter must have the following filters selected
The database level auditing filter must be set with the following set of values
A best practice is to define an alert for any activities defined by the auditing filter. Below is an example alert filter configuration that should ensure this requirement is met
10.2.6 Initialization, stopping, or pausing of the audit logs
Turning off the logging of the audited data or pausing them before performing illicit activities is a common practice for malicious users wanting to evade detection. Accordingly, Initialization of audit logs could indicate that the log function was disabled by a user to hide their actions. Therefore, PCI DSS requires that every initialization, stopping or pausing of process of logging the audited data must be registered and logged
ApexSQL Audit can track and report on every such change in the logging of audited data as part of the application internal event logging, which is not editable by user and user cannot influence in any way logging of internal events
10.2.7 Creation and deletion of system-level objects
The main target of this requirement is to prevent malicious malware type of software, that often creates and replaces system objects on the target SQL Server to achieve control over a particular function or operation on that server. By logging the creation or deletion of system-level objects, it will make possible to determine whether such action was accredited. The objects such as database tables are the most often target, but stored procedures, functions and triggers could be more dangerous as they are based on procedural programing language that making them easy targets to hide malicious code that would otherwise be invisible
To track the system level object creation at the SQL Server level the following filters should be used
At the database level the following filter settings should be implemented
All this should be implemented for each database in any server that is under the PCI DSS compliance requirement
To achieve the same using the advanced filter
10.3 Record at least the following audit trail entries for all system components for each event
10.3.1 User identification, 10.3.2 Type of event, 10.3.3 Date and time, 10.3.4 Success or failure indication, 10.3.5 Origination of event, 10.3.6 Identity or name of affected data, system component, or resource
This requirement is a direct extension to the previously described 10.2 requirement that requires that each auditable event described in 10.2 are logged in a way that will ensure the following details are included: user identification (login name), the type of event (i.e. insert, delete, drop table etc.), date and time of the event execution, the execution status of event (failed or successful), where the event came from (client host and application used to execute the event), object name, object schema and database and SQL Server name
ApexSQL Audit can fully accomplish this requirement via its reporting system
This article is part of a series
See previous part See next part
January 6, 2016
