PCI DSS 32 and SQL Server – By Grant Carter Part 1: Networking and Data

PCI DSS 3.2 and SQL Server  - Part 1

Terms Used in This Post

  • DMZ – Demilitarized zone often referred to as a perimeter network
  • TLS – Transport Layer Security
  • SSL – Secure Sockets Layer
  • PAN – Primary Account Number
  • PIN – Personal Identification Number
  • EKM – Extensible Key Managment

Build & Maintain Secure Networks & Systems

Requirement 1: Install & maintain firewall configuration to protect cardholder data.

SQL Servers do listen to networks for communication initiated by clients or other SQL Server systems.  It is important that SQL Servers receive traffic from systems that should communicate with them and that other traffic is blocked by firewall devices and software.

SQL Server’s lists of ports are fully documented at https://msdn.microsoft.com/en-us/library/cc646023.aspx.  Please consult this document when formulating a plan to build secure networks so that only the appropriate ports have to be opened in the creation of the network architecture.

In general, follow the recommendations outlined in the Securing SQL Server article found at https://msdn.microsoft.com/en-us/library/bb283235.aspx

The list of items to meet this requirement include:

  • Only servers and clients that should communicate are allowed to communicate. Other systems should be blocked.

o   Only the ports necessary to operate the SQL Server should be opened.

o   Firewall rules need to be approved and tested before being implemented.

o   Network diagrams should identify all connections between the PCI environment and other networks.

o   Identify and document all data flow from the PCI SQL Server to other systems and networks.

o   Firewalls should exist at each internet connection and between any DMZ an the internal network

o   All personnel who maintain network security for the SQL Server needs to be identified and their role for maintaining network security documented and tested.

o   Business justification for the use of services, protocols, & ports should be documented.  If any of these items be deemed insecure but necessary to run the business, the risks associated with using them needs to be clearly documented.

o   Review firewall rules every 6 months.  Should rules need to be adjusted or updated, any documentation should be modified to reflect the adjustment.

o   Review and monitor operational logs for unauthorized access and respond immediately should unauthorized access take place.

  • Restrict connections between the PCI data environment and untrusted networks.

o   Deny all traffic that isn’t related to the processing of PCI data and only allow traffic that is necessary for data processing.

o   Secure any firewall configuration file so that unauthorized sources can’t examine the network configuration.

o   Synchronize configurations with what is running or active on router and firewall devices.  Sometimes configuration changes are made but are not actually implemented to run on the device.

o   Install perimeter firewalls between all wireless networks and the PCI data environment.

  • Do not allow public access to the public internet for any system in the cardholder environment.

o   Implement a DMZ to limit inbound traffic to only allow authorized services, protocols, and ports

o   Limit internet traffic to IP address inside the DMZ.

o   Implement anti-spoofing technology.

o   Outbound traffic from the PCI environment to the internet should be blocked

o   All components that store PCI data should be located internally and not in a DMZ or untrusted network.

o   Do not disclose private IP addresses or SQL Server port numbers.

o   Consider hiding the SQL Server instance from discovery via the SQL Browser service.  See https://msdn.microsoft.com/en-us/library/ms179327.aspx.

  • Install firewall software on any portable computing device including employee, vendor, or company owned devices.
  • All security policies and standard operating procedures should be well documented and are available to all people who are responsible for designing and operating PCI systems.


Requirement 2: Do not use vendor supplied defaults for system passwords and other security parameters

SQL Server is a very secure platform for storing and retrieving information.  However, SQL Server is only as secure as the accounts that have access the data are.  If a malicious actor is able to gain access via a jeopardized account, then that actor will be able to extract and misuse the data if that account has the correct privilege.  Password management and policies are important to providing a data storage environment.

  • Always change vendor supplied passwords and defaults.

o   Consider renaming the sa account to something other than sa.  See Disabling or Renaming the Built-in sa Account in https://technet.microsoft.com/en-us/library/cc966485.aspx

o   Change the sa password often to something extremely complex.

o   If possible, disable the sa account and use Windows authentication if at all possible.

o   Review all vendor provided accounts to ensure they follow a password policy and that the password has been changed to something that is not the default service account.

o   For wireless environments connected to or transmitting PCI data, any vendor default must be changed

  • Develop configuration standards for all components that make up the PCI system.

o   Consider implementing well known secure industry configuration standards as part of your corporate standard.

o   Segregate functions between different services so that multiple services with mixed levels of security will not have to exist on the same infrastructure.

o   Enable only necessary services, protocols, and ports that are required to process PCI data.

o   Turn off any unnecessary services, scripts, or features to reduce the surface area.

o   Implement additional security features for any service, protocol, or port that is deemed insecure.

o   Configure security parameters to prevent misuse, either accidental or intentional.

  • Encrypt all non-console communication using strong cryptography protocols

o   SQL Server communication should not include TLS 1.0 or below.  TLS 1.2 is fully supported in SQL Server 2008 SP4 and greater.  Any SQL Server service older than SQL Server 2008 is now unsupported and TLS 1.2 support does not exist.  Please see https://blogs.msdn.microsoft.com/markweberblog/2015/12/01/sql-server-support-for-pci-dss-3-1/ for full details on the TLS 1.2 implementation for SQL Server.

o   Maintain an inventory of system components that are in scope for PCI compliance.

o   Security policies and procedures for managing all security parameters should be documented, used, and known by all parties who design and manage the PCI systems

o   Hosting providers that provide shared environments on the same infrastructure must protect each client’s PCI data and not allow for unsecure configurations which could allow one client’s data be exposed to another client.  An example of the might be something like using the same encryption key for multiple clients or allowing the same user to access both client’s data.

Protect Cardholder Data

Requirement 3: Protect stored cardholder data

One of the most critical jobs of a database engine is to protect the data stored in the database.  Not only is access control important, but protecting the data in such a way that sensitive data is protected to prevent unauthorized access to the data while it is on disk is extremely important.   The PCI DSS standard provides a lot of guidance in this area.

  • Keep card holder data storage to a minimum by implementing data retention and disposal policies and procedures.

o   Keep the data only as long as you need for business or legal requirements.   Many data breaches steal data from records that provide no business value.  By limiting the amount of data stored, should a data breach occur, the scope of the breach will be limited and exposure reduced.

o   Develop processes for data purges.  Test those processes and ensure they purge all non-essential data in a timely fashion.

o   PCI DSS requires a quarterly process to identify and purge data that exceeds the retention period defined by business or legal requirements.   All locations that can hold cardholder data must be considered n these processes.   This includes databases, database backups, log files, application files, and application logs.

  • Do not store sensitive authentication data after authorization (even if encrypted). All data that performs payment card authentication can be full contents of any track (from the magnetic strip), cardholders name, expiration date, card validation codes, pins or other data that can be used to aid in the misuse of card data is prohibited in a PCI DSS compliant system.   All authentication procedures must be reviewed periodically and validated that no authentication data is being stored.  The only exception to the rule about storing authentication data is for card issuers.  Issuing services can store this data as long as they have a business justification and have validated systems that store that data securely.

o   Ensure databases that must be PCI compliant do not store this data

o   Validate processes that handle this data do not store this data in log files, application files, or databases

  • Mask the personal account number (PAN) with the following requirements

o   The first 6 digits

o   The last 4 digits

o   Anyone with access to the unmasked data must have a business requirement to do so.

o   PANs must be masked for screen display, printed paper, and electronic transmission such as email or text messaging.

o   SQL Server’s dynamic masking feature aids in meeting this masking requirement.  Also application design using views can be utilized to mask data from people who should not have access to this data.

o   The PAN must be rendered unreadable anywhere it is stored.

o   Document and implement procedures to protect keys used to secure stored cardholder data against disclosure and misuse

  • Encryption keys and certificates used to encrypt data must be backed up and protected independently of the data. See https://msdn.microsoft.com/en-us/library/bb964742.aspx for more details on how to protect encrypted columns and database in the event of recovery.
  • All keys should be stored independently of databases files and database backup files.
  • Access to keys and key backups should only be allowed to individuals who have a need for them.
  • Periodic key rotation policies should be followed.
  • If a key should become jeopardized, regenerate the encryption key immediately
  • Consider the use of an extensible key management solution (EKM) for key management. https://msdn.microsoft.com/en-us/library/bb895340.aspx

o   Document the details of all cryptographic implementations: Include protocols, algorithms, and EKM solutions.  This detail is a best practice until January 31, 2018, but to be PCI compliant after that, this step is required.

o   One or more of the following must be used to store private keys used in PCI systems

  • Protect private keys used with PCI data with key encrypting keys that are as strong or stronger than the key protecting the card hold data
  • Store in a secure cryptographic module such as a hardware security module
  • At least 2 full length key components or key shares.

o   Keep keys in the fewest number of possible locations to reduce the risk of exposing keys.

o   All key management processes must be documented and validated

  • Validate encryption algorithms and protocols used to encrypt data must meet strength requirements
  • Validate key distribution processes meet requirements
  • Validate encryption solutions store the keys securely with protections against unauthorized access.
  • Document and validate key expirations periods and key rotation practices are in use.
  • Document processes for the replacement of keys that are weak or compromised. Validate the processes are followed.  SQL Server keys can be regenerated as part of a rotation strategy.
  • Key management processes should not accept and use keys from unauthorized sources. Only authorized processes to generate and use keys should be accepted.

o   Processes and policies that are in place to protected PCI data must be documented and used by all parties.  Any updates to these policies or processes must be known by all parties immediately when those changes are implemented.

While not called out specifically in the PCI specification, it is best practice to periodically test the procedures for managing encryption.  This includes

  • Performing regular disaster recovery drills to test that the encryption management processes work. 
  • Document the name of the individuals performing the test along with any test output as validation that the testing was successful.

Requirement 4: Encrypt the transmission of cardholder data across open, public networks

  • Use strong cryptographic and security protocols for the transmission of PCI data across public networks.

o   By June 30, 2018 all applications using SSL or early TLS versions must cease.

o   Before June 30, 2018 all applications using SSL or early TLS versions must have risk mitigation plans in place.

o   SQL Server now supports TLS 1.2 -  https://blogs.msdn.microsoft.com/markweberblog/2015/12/01/sql-server-support-for-pci-dss-3-1/

o   Use trusted keys from trusted sources

o   Use strong protocols and algorithms for keys.

  • Do not send unencrypted PANs across using messaging technologies such as email.
  • Ensure all security policies and operational procedures related to the transmission of PCI data are documented and in use by all individuals who handle PCI data. Ensure updates policies or procedures become known by all individuals who handle PIC data



Completely Off Topic:

Obscure Item of History

John Tyler (B. March 29, 1790 D. January 18, 1862), the 10th president of the United States (April 4, 1841- March 4, 1845) has two grandsons that are still alive today.  John Tyler had a son when he was 63 years old.   Tyler’s son Lyon Gardiner Tyler (1853-1935) fathered children born in the 1920’s when he was over 70 years old.  Two of his sons, Harrison Ruffin Tyler and his brother Lyon Tyler are still living today making them living grandsons of a man who was president in the early 1840’s.


Grant Carter is a Senior Premier Field Engineer for Microsoft based in Boise, Idaho.

Email: grant.carter@microsoft.com

Skip to main content