What does PCI DSS mean for your SQL Server environment?

By:   |   Comments (5)   |   Related: > Auditing and Compliance


I just started working at a payments company and now everyone is talking about the prevention of data breaches and an upcoming PCI DSS audit. What is PCI DSS and what are the requirements from a SQL Server perspective?


The Payment Card Industry Data Security Standard (PCI DSS) applies to all entities involved in payment card processing who store, process, or transmit cardholder data or sensitive authentication data. In a nutshell, this standard applies to every company who works with credit card data. PCI DSS is maintained by the Payment Card Industry Security Standards Council (PCI SSC). The PCI SSC has been formed by American Express, Discover Financial Services, JCB International, MasterCard, and Visa Inc. The PCI DSS was originally released in 2004 and the latest version is 3.0 which was published in November 2013. The standard lists 12 requirements to secure the cardholder data and protect the sensitive data. The PCI DSS auditors perform onsite assessment annually including penetration testing, policy reviews and quarterly vulnerability scans on the network. There is a related standard which applies to software products: it is the Payment Application Data Security Standard (PA-DSS). PCI DSS compliance is mandated by both Visa and MasterCard.

Objectives and requirements

The documentation standard is 112 pages and defines 6 security control objectives. The table below summarizes the objectives of PCI DSS and the 12 main requirements:

Control objective Requirements
Build and Maintain a Secure Network and Systems 1. Install and maintain a firewall configuration to protect cardholder data
2. Do not use vendor-supplied defaults for system passwords and other security parameters
Protect Cardholder Data 3. Protect stored cardholder data
4. Encrypt transmission of cardholder data across open, public networks
Maintain a Vulnerability Management Program 5. Protect all systems against malware and regularly update anti-virus software or programs
6. Develop and maintain secure systems and applications
Implement Strong Access Control Measures 7. Restrict access to cardholder data by business need to know
8. Identify and authenticate access to system components
9. Restrict physical access to cardholder data
Regularly Monitor and Test Networks 10. Track and monitor all access to network resources and cardholder data
11. Regularly test security systems and processes
Maintain an Information Security Policy 12. Maintain a policy that addresses information security for all personnel

DBAs should focus on requirements 2, 3, 4, 6, 7, 8 and 10 as the other requirements should be addressed at the network or system level. Before we go deeper into the details, let's check what kind of data we should protect with extra attention!

Cardholder Data and Sensitive Data

According to the standard, sensitive data cannot be stored on the system. If sensitive data is received during a card transaction then it should be rendered unrecoverable upon completion of the authorization process. This group includes the following data elements:

  • Full track data: magnetic-stripe data or equivalent on a chip
  • CAV2/CVC2/CVV2/CID: this is the card security code which is usually on the back of the card
  • PINs/PIN blocks: the personal PIN code and the encrypted PIN block

Storage of other cardholder data is permitted, but it must be protected. The full card number (PAN) cannot be stored, it must be truncated (first 6 digits and last 4 digits can be stored), hashed or tokenized. Other elements of the cardholder data should also be secured if they are stored with the card number. The elements of the cardholder data include:

  • Primary Account Number (PAN): also known as card number, it is usually between 16-19 digits
  • Cardholder Name: the full name of the cardholder
  • Expiration Date: the date when the card expires in MMYY format
  • Service Code: a confidential code which tells if the card is chip capable and can be used for international transactions

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

Fortunately the SQL Server does not assign default passwords, but you should disable the sa account and verify that the BUILTIN\Administrators group is not assigned to the sysadmin role. You should make sure no unnecessary SQL Server features are enabled on the server. I would recommend using Windows authentication for enhanced login management.

PCI DSS Requirement 3: Protect stored cardholder data

It is recommended to use Transparent Data Encryption (TDE) or full disk encryption to protect the data in your database. The keys must be changed at least once a year and it is recommended to use Extensible Key Management with an external cryptographic provider such as Hardware Security Module (HSM). You should also check that the database does not store the full PAN (card number) clearly visible anywhere. If retrieval of the full PAN is required then there should be an external program or a stored procedure to decrypt the PAN, but access to that tool should be limited.

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

You should configure Secure Sockets Layer (SSL) encryption which requires a certificate.

PCI DSS Requirement 6: Develop and maintain secure systems and applications

You can address this requirement by applying all SQL Server security patches within a month of public release and follow a change control process. Also you should remove all test and developer accounts before a database goes into production.

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

The database should be an internal network zone which is accessible from only trusted networks. In requirement 3 I mentioned that any tool displaying the full card number should be placed under strong access control. The basic principle is that access should be denied for everyone and only those restricted users should be allowed to have access. Even so, the access should be limited to a minimum amount of data. You should define user groups for each business role and implement role-based access control. It is also possible to use module signing using the ADD SIGNATURE statement.

PCI DSS Requirement 8: Identify and authenticate access to system components

The best way is the use Windows authentication to enforce strong passwords, password change, etc. If the application has its own user names and passwords then the passwords should never be stored in clear text, they must be hashed or encrypted. The login changes should be audited.

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

You should track the logins to the SQL Server instance. Details of the login audit configuration can be found in this tip and there is also a tip about SYSADMIN login auditing. Also usage of the full PAN tool described in requirement 3 should be logged.

I hope that now you have a basic understanding of the PCI DSS requirements and you can prepare your SQL Server for a successful audit. My advice is start with reviewing the SQL Server Security Checklist and then collecting the list of tables, views and stored procedures which are used to process cardholder data.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tibor Nagy Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Sunday, August 23, 2020 - 10:12:04 PM - Thomas Back To Top (86347)
Great Information about dss compliance in regards to sql server. I will definetly look into these things when handling sql database. Definetly Bookmarked

Thursday, May 8, 2014 - 1:29:45 PM - Gaby A. Back To Top (30676)

We currently use a third party device to encrypt/decrypt data (.dll on SQL Server is loaded and calls the linux encrypt/decrypt boxes that are PCI certified).  If we use SQL Server Encryption, as sysadmins, we will have full decrypt/encrypt access to the tables if I recall, which would make PCI compliance  difficult, would it not?  This device we have (well known in industry) manages all permissions and to modify access, we have a split password to the admin account with another team, so danger of me decrypting a table would only be through collusion, a very difficult scenario.

Friday, April 25, 2014 - 5:41:08 PM - Tibor Nagy Back To Top (30524)


This is included in the PCI DSS v3.0 document in the best practices chapter on page 14:

"Review hardware and software technologies at least annually to confirm that they continue to be supported by the vendor and can meet the entity’s security requirements, including PCI DSS. If it is discovered that technologies are no longer supported by the vendor or cannot meet the entity’s security needs, the entity should prepare a remediation plan, up to and including replacement of the technology, as necessary."

Friday, April 25, 2014 - 1:11:00 PM - Randy Back To Top (30521)

I was wondering if anyone had seen anything in PCI that would require a business to only run PCI data on SQL versions that are still supported by Microsoft. AKA, PCI compliance would prevent them from keeping sensitive data on a sql 2000 server. Any help dealing with stubborn mgnt would be most appreciated.

Friday, April 25, 2014 - 12:40:15 AM - Gopalakrishnan Arthanarisamy Back To Top (30513)

Good One. Thanks Tibor Nagy.



get free sql tips
agree to terms