PCI Compliance for SQL Server DBAs

The Payment Card Industry Data Security Standard (PCI DSS, or just PCI) is a security regulations standard related to payment cardholder and card payment information security. Any company or institution that uses and stores such information is obligated to comply with the PCI standard.

The PCI DSS general requirements are divided into several categories:

  • “Build and Maintain a Secure Network
    • 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
    • Use and regularly update anti-virus software on all systems commonly affected by malware
    • Develop and maintain secure systems and applications
  • Implement Strong Access Control Measures
    • Restrict access to cardholder data by business need-to-know
    • Assign a unique ID to each person with computer access
    • 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” [1]

Having the PCI requirements in place does not guarantee data’s safety although compliance requirements are met. The PCI standard specifies only what the requirements are, but not how to achieve them.

General recommendations

The following general recommendations are related to actions that should be taken for accomplishing PCI compliance.

  • Set up a firewall – “Establish firewall and router configuration standards that include the following:
    • A formal process for approving and testing all network connections and changes to the firewall and router configurations
    • Current network diagram with all connections to cardholder data, including any wireless networks
    • Requirements for a firewall at each Internet connection and between any demilitarized zone (DMZ) and the internal network zone
    • Description of groups, roles, and responsibilities for logical management of network components
    • Documentation and business justification for use of all services, protocols, and ports allowed, including documentation of security features implemented for those protocols considered to be insecure
    • Requirement to review firewall and router rule sets at least every six months” [1]

    The payment application must be implemented into a secure network environment. The application must not interfere with use of devices, applications, or configurations required for PCI DSS compliance (for example, with anti-virus protection, firewall configurations, or any other device, application, or configuration required for PCI DSS compliance).

  • Change default passwords – do not use vendor-supplied defaults for system passwords and other security parameters in SQL Server. If the payment application generates or manages authentication credentials, test the application to verify whether default application passwords are enforced. If this turns out to be the case, change the default behavior of the password policy
  • Use encrypted transmission – “Encrypt all non-console administrative access using strong cryptography. Use technologies such as SSH, VPN, or SSL/TLS for web-based management and other non-console administrative access” [1]
  • PIN/card verification code – After payment / credit card authorization, do not store the personal identification number (PIN) or the encrypted PIN block; this also includes the card verification code (the 3-digit or 4-digit number printed on the back of the payment card). If you store any of these, you will fail the compliance. However, you can store some information about the credit card holder and credit card itself, as explained in the PCI DSS Applicability Information section of Payment Card Industry (PCI) Data Security Standard. Sensitive data in this context are:
    • Card Verification Value
    • PIN or the encrypted PIN Block
    • The full contents of the Magnetic Stripe
  • Use anti-virus software solutions with regular updates. Ensure the anti-virus solutions are able to detect, remove, and protect against all known and potential types of malicious software. Verify that all security devices and software required by PCI DSS (e.g. firewalls and anti-virus software) were implemented on all systems possibly affected by malicious software
  • “Develop and maintain secure systems and applications – Ensure that all system components and software are protected from known vulnerabilities by having the latest vendor-supplied security patches installed. Install critical security patches within one month of release
    Note: An organization may consider applying a risk-based approach to prioritize their patch installations”
  • Use a unique ID for each person with computer access –any access to PCs, servers, and databases with payment applications must require a unique user ID and secure authentication
  • Restrict physical access to databases holding cardholder data – “Use appropriate facility entry controls to limit and monitor physical access to systems in the cardholder data environment” [1]
  • Monitor and track all network resources and databases accessed via network – set up audit trails for all system components to reconstruct individual accesses to cardholder data, or even actions performed by users with admin privileges
  • Periodically test security, systems, and any environment processes (e.g. wireless access points, significant internal and external environment changes, etc.)
  • Create and enforce an information security policy that affects all card holder data users, and make it explicitly approved by authorized parties
  • “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]. Keep PCI related logs separate from application activity logs – this will make audit history maintenance easier, while keeping non-compliance related logs out of the audit history process

Additional notes on how to comply with PCI

It’s recommended to keep non-production systems related to the database that stores card data (any processing or transmission systems) safe as well. For example, it’s not uncommon that malicious software on a non-production system provides access to the production system using internal network structure.

Determine how to handle access authorization on SQL Server. There are two authentication modes used for SQL Server access: Windows authentication and mixed mode (enables both Windows authentication and SQL Server authentication). Windows authentication is a way less vulnerable to malicious attacks (e.g. brute-force), as it provides the Account Lockout Policy feature. On the other hand, SQL Server authentication is natively alert-less in terms of attack attempts, and what’s even worse, SQL Server is very responsive when validating authentication attempts – except logged failed logins, practically no other indicators exist.

Recommendations for auditing/tracking on SQL Server

In order to fulfill PCI compliance regulations for SQL Server, it is advisable to audit the following events:

  • All accesses to the cardholder data
  • All actions taken by any individual with root or administrative privileges
  • Access to all audit trails
  • Invalid access attempts
  • Use of identification and authentication mechanisms
  • Creation and deletion of system-level objects

ApexSQL Audit, a SQL Server auditing and compliance tool can help you accomplish these recommendations in order to comply with PCI regulations. It provides a wide range of options for auditing SQL Server activities, monitors security permissions, and login and password changes. ApexSQL Audit provides auditing of multiple SQL Server instances from a central location, with remotely accessible web based reports.

To start auditing SQL Server instances, their databases, and objects with ApexSQL Audit:

  1. Start the ApexSQL Audit GUI
  2. Select the SQL Server instances you need to audit
  3. For each instance, check appropriate filtering options in order to audit server level objects changes and security events
  4. Similarly, in the Databases section, check the databases, operation types and objects to audit
  5. Click Apply to apply the filter settings

Selected SQL Server events will be tracked and the audited information will be saved in the central repository database for all audited SQL Server instances. The ApexSQL Audit reporting module offers more than 30 common built-in reports that provide information that helps meeting PCI compliance requests

To report all individual accesses to the cardholder data, use the Access history report. Any suspicious access listed in the reports must be investigated (e.g. access by an uncommonly used application)

All actions performed by any individual with root or administrative privileges can be reported using the Advanced report, while specifying SQL Server admin logins in the Login filter field

Generated report will provide all activities associated with admin logins

To report all accesses to audit trails (the central repository database), use the Audit settings history and the Audit integrity checks reports

Use of identification and authentication mechanisms are reported within the Logon activity history report

Note that additional security related reports are also available, such as the Permissions changes and Unauthorized access reports.

Finally, to report creation, modification, and deletion of system-level objects, use the DDL History report.

To comply with PCI, you must create and maintain a safe environment, and keep your SQL Server instances, databases, and objects changes audited. To ensure that no treats for card holder and credit card data exist, use ApexSQL Audit to track security related events on SQL Server instances, databases and objects per PCI regulation requirements

[1] Payment Card Industry (PCI) Data Security Standard

Useful resources
Get Ready for PCI 3.0
Payment Application Data Security Standard
PCI DSS Reporting Instructions
Lifecycle for Changes to PCI DSS and PA-DSS


November 22, 2013