The PCI DSS (Payment Card Industry Data Security Standard) is a multidimensional security standard designed as a set of technical and operational requirements to protect data of credit card holders. The PCI DSS applies to all entities that store, process or transmit cardholder data, including software developers of applications and devices manufacturers when used in those transactions
This standard originated in 2005 and was created by the PCI SSC (Payment Card Industry Security Standards Council) organization. The PCI SSC organization is founded by American Express, Discover Financial Services, JCB International, MasterCard Worldwide, and Visa, Inc., with a goal to improve security of payment account data via the PCI Security Standards
In light of this standard, all database administrators and security administrators in charge for SQL Servers that store and maintain the payment card accounts data are obligated to comply with PCI DSS requirements related to SQL Server security
The PCI DSS standard is defined via six steps that reflect the goals for achieving security best practices. Each of these steps, in totality, comprise the PCI DSS requirement:
- Build and Maintain a Secure Network and Systems
- Install and maintain a firewall configuration to protect cardholder data
- Do not use vendor-supplied defaults for system passwords and other security parameters
- Protect Cardholder Data
- Protect stored cardholder data
- Encrypt transmission of cardholder data across open, public networks
- Maintain a Vulnerability Management Program
- Protect all systems against malware and regularly update anti-virus software or programs
- Develop and maintain secure systems and applications
- Implement Strong Access Control Measures
- Restrict access to cardholder data by business need to know
- Identify and authenticate access to system components
- Restrict physical access to cardholder data
- Regularly Monitor and Test Networks
- Track and monitor all access to network resources and cardholder data
- Regularly test security systems and processes
- Maintain an Information Security Policy
- Maintain a policy that addresses information security for all personnel
This document is focused on the PCI Data Security Standard (PCI DSS) 3.1 officially released by the PCI Security Standard Council on April 2015. The following PCI DSS requirements, regarding SQL Server, that are partially or fully supported by ApexSQL Audit with description will be presented in the first part of this article:
3.3 – Requires masking or not displaying Primary Account Number (PAN)
6.4 – All change control procedures have to be followed for any SQL Server configuration changes. The procedures must include the following:
6.4.1 – SQL Server development and test environments must be separated from the production SQL Server environments, and separation must be enforced with access controls
6.4.2 – In SQL Server environment developers and test users may have high access level or administrator-level account for development and test environment, but they must have separate accounts with only limited user level access in production environments
6.4.3 – Any data stored in the production SQL Server and especially live PANs must not be used in any form for purpose of development and/or testing
6.4.4 – The removal of any test data and accounts from the test and development SQL Servers must be performed before the application became active in the production
ApexSQL Audit
ApexSQL has created a PCI compliance checklist for ApexSQL Audit document, the purpose of which is to show specifically the areas of PCI DSS requirements where ApexSQL Audit can be implemented to cover said requirements fully or to provide assistance in complying to said requirements.
In this article the PCI DSS requirements that ApexSQL Audit can address are described, as well as details on how to configure ApexSQL Audit to match and/or help that requirements. ApexSQL Audit recommended auditing, alerting, and reporting settings than allows complying with the individual PCI DSS compliance requirements will be showed in detail
NOTE: Detailed steps for setting up the particular ApexSQL Audit feature that will be described in this article are mostly the common steps, self-explainable in general and therefore they will be described once and will not be repeated in describing the other PCI DSS requirements in this article, in favor of providing only the specific filtering conditions required for matching each specific PCI DSS requirement
3.3 Mask PAN when displayed (the first six and last four digits are the maximum number of digits to be displayed)
This requirement is fully covered by ApexSQL Audit as it will not store any raw data in the central repository database, but only the parametrized values in form of @1, @2, etc., which mean that any raw data info will not be displayed in the generated reports and or alert forms
6.4.1 Separate development/test and production environments
The requirement consists of a regular review of the audited data, with particular focus as to who executed the audited event and from where that specific SQL Server event is executed. Test and development environments tends to be less secure, due to its dynamic nature vs production environments. Therefore, it must be ensured that any access to the SQL Server from environments outside of the production environment, must be tracked and logged. ApexSQL Audit ensures the required Client host and Login information in the created reports, making available to person in charge information necessary to identify any access from environment that is not authorized
Furthermore, the ApexSQL Audit alerting engine includes the ability to create alerts in real time on any unauthorized client host access, which can guarantee when necessary an immediate reaction by security personnel responsible for maintaining the PCI DSS compliance
To set the alerts for any access to production SQL Servers from non-production machines:
In the Alerting tab select Manage and click New
Select Auditing alert
In the New alert wizard, the Basic settings step ensures full customization for the alert name, subject and report body text. When used, the Limit the number of reports for this alert to one per minute (for each server) option suppress alert spike in circumstances when number of events is unusually high. In this particular case where any kind of access to production SQL Server from non-production environment has to be prevented, this option should be left unchecked
The variables included in this dialog allows the amount of the information that will be displayed with triggered alert to be defined
The Server deployment dialog requires selecting the SQL Server instance
Setting up the condition that will trigger an alert is possible using the advanced filter, and in this particular situation described below the filter example below will raise alerts for any event that was executed from any client host not explicitly defined in the Client host condition field
In the “Where Client host is not” condition field, only the Client host names that belongs to the client host environment should be entered.
Opting to be informed via email when an alert occurs is highly recommended. This particular alert will send an email for any event that occurs on SQL Server which is executed from the client host that does not match one of client hosts listed in the alert filter condition. Reviewing the alert which can be set to contain all the necessary data should ensure resolving the breach reasonably fast
To speed up the documenting of any such activities, it is recommended to use the Custom report feature to create and save the dedicated custom report for easy reviewing on such activities
To create a report that can ensure quick review of any out of the production environment access activity, define the following condition in the filter and save it
A report created and saved in this way can be generated in a mouse click and any unwanted client hosts access could be reviewed and documented easily, which will allow the appropriate long term actions to be taken
6.4.2 Separation of duties between development/test and production environments
The intention of this requirement is to make sure permission for development and test SQL Server environments are separated from production. In case that a developer has to use an administrator-level account with elevated privileges in his development SQL Servers, he should not have any or if needed he should have a different account that will grant him only limited level access to the production SQL Server. In such situations, ApexSQL Audit should be set up to audit all development/test SQL Servers that are in any direct relation with production SQL Servers already audited by ApexSQL Audit, for any user permission changes
To set the simple auditing filter:
After adding the SQL Server which activities have to be audited, in the central panel by checking operations from the list will apply Simple auditing filters
Server level auditing filter should utilize the following filter settings
To audit the user privileges on the database level, select databases that have to be audited via Add database
The database level auditing must include the following filter settings related to user permissions
To achieve the same using the advanced auditing filter, set up the below conditions
These filters should be defined for all development, test and productions SQL Servers.
In addition, it is highly recommended to set up the alert(s) that will notify the person in charge when such an advent security change occurs. The alert condition should match the condition displayed in the advanced auditing filter
To have all this changes properly documented and to ensure quick and easy reporting on the particular changes defined with this specific PCI DSS requirement, a custom report should be defined and saved. This custom report can ensure that any inadvertent change can be tracked, reviewed but also how fast each of this changes is corrected if there were the need to do so. The report can be created as cumulative for all SQL Servers from development and production environment, but it is a better practice to create a separate custom reports for development and test SQL Servers vs the production SQL Server.
Here is the custom report filter that should be set
And below is the example of the report that will be created using this report
6.4.3 Production data (live PANs) are not used for testing or development
ApexSQL Audit can help in implementing this requirement using its ability to audit any access to production SQL Server tables where PAN data are stored by users unauthorized to read and use any PAN data via auditing any SELECT, SELECT INTO statement executed on those tables, including triggering of the real time alerts on each such event
To set up the auditing of SELECT and SELECT INTO statements executed against the tables that stores the PAN data can be done via the simple filter, but this filter will not allow excluding specific user from audition for these specific operations only
To ensure that specific users will be audited in general, but excluded from auditing only if they execute the SELECT and SELECT INTO statements, the advanced auditing filter has to be used
This particular filter used here in example will allow auditing for select or select into access on specified tables in specified databases and also for excluding the users that are permitted to execute these operations. In this example, RECHIE-PC\ReChie, RECHIE-PC\Void and MAKINA are excluded from auditing for both select or select into, as they are trusted users regarding the access to the PAN data
To ensure that any such access is detected timely it is advisable to define the High priority alert which will be triggered each time when non trusted user access the PAN data using the following alert condition
6.4.4 Removal of test data and accounts before production systems become active
The essence of this PCI DSS requirement is that all test data and login/user accounts should be completely removed from the production SQL Server before if becomes active, as this could give away info about the applications functionality. ApexSQL Audit can track all delete, truncate and drop operations to ensure that the appropriate removal of data is performed, as well as the auditing of all drop login and drop user events to ensure that all accounts used during the testing were removed and that all data and account removals are properly documented for any further inspection and/or approval
To match this PCI DSS requirement, the following auditing filter has to be set for SQL Server and for any SQL Server database used during the testing
To ensure the final approval that this requirement, required for granting approval for production server to became active, is fulfilled, the appropriate reports have to be inspected to verify that all data and user accounts are removed
It is recommended to create separate custom reports for inspecting user accounts removal and for inspecting the data removal
The following filter can be used for creating custom report for verifying test user accounts removal
And this custom report should be created for verifying test data removal
In the second part of this article it will be described how to comply with the following PCI DSS requirements:
- Requirement 7 – Restrict access to cardholder data by business need to
- Requirement 8: Assign a unique ID to each person with computer access
This article is part of a series
December 11, 2015
