Meeting PCI compliance requirements with SQL Server

What is PCI?

The Payment Card Industry Data Security Standard (PCI DSS, or just PCI) is an information security standard that protects cardholder and card payment information. The PCI DSS general requirements are designed to ensure a secure, monitored network, protect cardholder and transaction data, provide vulnerability management, strong access control measures, and maintain an information security policy

Who has to comply with PCI?

PCI applies to all entities that hold, process, or pass cardholder and card transaction information. All organizations, merchants and service providers, including federal agencies that accept credit and debit cards, process, store, or transmit cardholder data, are required to meet PCI compliance regulations

What does PCI ensure?

Security requirements of PCI compliance ensure confidentiality, integrity, and availability of credit card information. It provides safe handling of cardholder information, thus protecting sensitive card information and preventing card frauds. It also detects security incidents and gives instructions for an appropriate reaction

How is PCI checked?

Validation of PCI compliance is performed annually. It can be assessed internally or externally, depending on the number of card transactions the entity processes per year. The entities that handle a large number of transactions must have their compliance checked and verified by an external independent auditor – a Qualified Security Assessor (QSA), through annual on-site assessments. The entities that handle a smaller number of transactions can self-certificate using the Self-Assessment Questionnaire (SAQ)

ApexSQL Audit helps with PCI compliance as it:

  • Automatically monitors events on a database to make sure compliance requests are met
  • Provides accurate and relevant reports for internal and external compliance reviews
  • Provides reports that discover threats and security incidents

How to audit a SQL Server instance

ApexSQL Audit is a SQL Server auditing tool that complies with auditing regulations. It provides a variety of options for auditing SQL Server activities, ensures adherence to compliance policies, and monitors SQL Server security by tracking permission, login and password changes. Multiple SQL Server instances are audited from a central location


To verify the PCI compliance, the entity must create compliance reports regularly and submit them to the acquiring bank and card brands the entity does business with. Every payment card brand (American Express, MasterCard, Visa, etc.) has its own program for compliance

All entities must submit an annual report. Depending on the number of transactions, some entities are required to submit quarterly reports. The reports must be completed by an Approved Scanning Vendor or Qualified Security Assessor approved by PCI Security Standards Council

The reports ApexSQL Audit provides for PCI compliance auditing

PCI Requirement 6: Develop and maintain secure systems and applications

6.3.1 Removal of custom application accounts, user IDs, and passwords before applications become active or are released to customers*

ApexSQL Audit reports that cover this requirement:

The Complete audit trail and DDL History reports show when a user was created, modified or deleted from a database

See deleted users

PCI Requirement 7: Restrict access to cardholder data by business need to know

7.1 Limit access to system components and cardholder data to only those individuals whose job requires such access. Access limitations must include the following:

7.1.1 Restriction of access rights to privileged user IDs to least privileges necessary to perform job responsibilities*

The Logon activity history report shows who and when has attempted to logon to the SQL Server instance along with the time, logon attempt status and machine name

Logon Activity History

The Permission changes report shows who, when and how changed the user’s permissions. Any unexpected and unapproved permission changes must be investigated

Permission Changes Report

The Unauthorized access report shows failed login attempts, when non-existing logins or wrong passwords are used

Unauthorized Access

7.2 Establish an access control system for systems components with multiple users that restricts access based on a user’s need to know, and is set to “deny all” unless specifically allowed*

The Access history report can help, as it shows who and when accessed database objects

PCI Requirement 8: Assign a unique ID to each person with computer access

8.5.1 Control addition, deletion, and modification of user IDs, credentials, and other identifier objects.

8.5.3 Set passwords for first-time use and resets to a unique value for each user and change immediately after the first use.*

The DDL history report shows the new users created on a database, who and when created them

The Permission changes report shows the changes on user logins, such as password changes and login renames

The Security configuration history report shows changes on logins, users, and roles. Monitoring these changes is necessary, as any unauthorized security change can lead to sensitive data leak and failure to comply with PCI

Security Configuration History

8.5.8 – Do not use group, shared, or generic accounts and passwords.*

The Logon activity history report shows the logins used to access the database. The list will indicate whether the logins used are generic or not

8.5.9 Change user passwords at least every 90 days.*

The DDL history report shows password changes, as ALTER LOGIN <login_name> WITH PASSWORD

8.5.13 – Limit repeated access attempts by locking out the user ID after not more than six attempts.*

The Unauthorized access report shows each unsuccessful login attempt

8.5.16 – Authenticate all access to any database containing cardholder data. This includes access by applications, administrators, and all other users.*

The reports that show relevant records are Access history, DML history and DDL history. The first report shows who and when has accessed the database objects, the second one shows who and when has performed a DML operation, and the third one shows the same for DDL operations.

The Complete audit trail report shows all events that happened on all audited SQL Server instances

Complete Audit Trail

PCI Requirement 10 -Track and monitor all access to network resources and cardholder data

This requirement directly addresses security of electronic cardholder and transaction records, and network security

10.1 Establish a process for linking all access to system components (especially access done with administrative privileges such as root) to each individual user.*

In other words – it’s advised to have audit trails enabled and active. Once the auditing is set properly, it shouldn’t be changed without a documented request. The Audit settings history report shows the auditing setting changes. Unexpected changes should be investigated, as they can lead to incomplete audit trails, and thus violate compliance requests

Audit Settings History report

10.2 Implement automated audit trails for all system components to reconstruct the following events:

10.2.1 All individual accesses to cardholder data*

The reports that show adequate records are Logon activity history and Access history

10.2.2 All actions taken by any individual with root or administrative privileges*

The Access history report shows who accessed what, when and what changes were made

10.2.4 Invalid logical access attempts*

The Unauthorized access report shows this information

Unauthorized access report

10.2.6 Initialization of the audit logs*

Not only clearing of audit logs, but any modification of the audit logs will be reported as potential tempering. The Data integrity verification option in ApexSQL Audit GUI provides full details on any potential tampering

10.2.7 Creation and deletion of system-level objects*

The DDL History report shows the information about all created and deleted objects.

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*

All of the above audit trail entries are captured by ApexSQL Audit

10.5 Secure audit trails so they cannot be altered

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

All audit records are saved in the ApexSQL Audit central repository database on a SQL Server instance. Access to the database and reports are available only to the users who are granted sufficient permissions

10.5.2 Protect audit trail files from unauthorized modifications.*

ApexSQL Audit uses a tamper-evident central repository. Any modification of the audited data is reported

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

All trail information is stored in the ApexSQL Audit central repository database. The data cannot be accessed or altered unless a user has enough privileges on the database and its tables. Even if the records are altered, it’s difficult to keep it unnoticed; any change of the audit trail will be detected and reported as tampering

Requirement 12: Maintain a policy that addresses information security for all personnel

12.2 – Develop daily operational security procedures that are consistent with requirements in this specification (for example, user account maintenance procedures, and log review procedures)

12.5.2 Monitor and analyze security alerts and information, and distribute to appropriate personnel

12.5.4 Administer user accounts, including additions, deletions, and modifications

12.5.5 Monitor and control all access to data*

ApexSQL Audit reports listed above can be filtered to show only daily activity and thus provide all the information requested by daily operational security procedures

Compliance with PCI is a must for every company that handles credit card information. Use ApexSQL Audit to have a complete audit trail of your SQL Server instances and databases, and have the reports ready for PCI compliance assessors

* PCI Security Standards Council Payment Card Industry (PCI) Data Security Standard

September 28, 2013