Securing Credit Card Data Through SQL Server 2008

If your organization uses SQL Server to store or process credit card information, you’re probably aware that credit card fraud is a growing problem. These days, the theft of your physical credit by a robber is nothing compared to the massive data breach of credit card information that occurs regularly. Malicious hackers have penetrated the electronic records of large organizations and acquired or exposed the credit card data of hundreds of millions of accounts. Data theft on this scale has cost these organizations—and the card providers—millions of dollars in lawsuits, fines, restitution for losses, and the expense of notifying account holders.

To minimize the risk of storing credit card data on computers, major card providers American Express, Discover Financial Services, JCB, MasterCard Worldwide, and Visa have created the Payment Card Industry Security Standards Council (PCISSC), a self-policing industry association. In turn, the PCISSC has created the PCI Data Security Standard (PCI DSS) to guide merchants and service providers in safeguarding their credit card data.

You may wonder whether businesses other than large banks and financial institutions have to pay attention to PCI DSS. The answer is that any businesses that uses or stores customer credit card data must become compliant with the PCI DSS. All organizations that accept credit cards have direct mandates to comply with the standards as part of their business arrangements with the major credit card companies. In addition, such organizations must also do business only with other compliant organizations. This is a powerful incentive for organizations to become compliant if they provide services to businesses that accept credit cards.

For many small merchants, the solution is rather simple: Outsource credit card transactions to a known compliant service provider, and retain no credit card information whatsoever. However, compliance with PCI DSS is necessary for businesses that do handle credit card data for their own customer transactions or that provide services—such as processing or storage—that involve credit card data.

PCI Data Security Requirements

The PCISSC requires security of “cardholder data,” which it defines as the Primary Account Number (PAN) of the credit card together with any of the following data:

This is the information that your organization must protect.

The PCI DSS includes the following 12 data security requirements that organizations must satisfy to comply with association standards:

Some of these requirements involve network and operational procedures that do not directly affect the database. These are items 1, 5, 9, 11, and 12. However, the remaining requirements clearly apply to databases on which credit card data is stored or processed.

In the remainder of this article, you will learn how to apply the database-related PCI DSS data security requirements to your SQL Server database.

Individual Requirements: A Closer Look

The 12 PCI DSS requirements can seem fairly vague. For example, Requirement 3 directs organizations to “protect stored cardholder data.” Most organizations probably believe that they are already doing this. To help organizations better understand the requirements, the PCISSC has issued “Navigating PCI DSS: Understanding the Intent of the Requirements," a document that explains the details of each requirement. For example, Requirement 3 actually consists of 20 subsections that describe how to satisfy the requirement. Some of these subsections are simple. For example, 3.2.2 states the following: “Do not store the card validation code or value (three-digit or four-digit number printed on the front or back of a payment card) used to verify card-not-present transactions.” Other subsections are quite complicated. For example, 3.1 states the following, in part: “Develop a data retention and disposal policy.” Clearly, this single statement could result in the creation of entirely new organizational policies, actions, and departments.

Although you can perform all the work to achieve PCI DSS compliance, an independent audit is required to verify that your organization is, in fact, compliant. At some point, you must engage the services of a Qualified Security Assessor (QSA) to audit your organization according to the 12 requirements and their specific subsections. For this reason, you should make sure that you thoroughly document all the work that you do to satisfy the PCI DSS, and you should also document your operational procedures.

Requirement 2: Removing Defaults

Requirement 2 specifies removing default aspects of computer systems, such as guest accounts. Malicious hackers often use default passwords, settings, and configurations to gain access to data or to further enhance their credentials. By removing defaults, you deny malicious hackers easy access to the data.

For example, one subsection of Requirement 2 specifically mentions changing vendor-supplied passwords. When you install SQL Server 2008, there are no default passwords; you must enter a new password when you create accounts. Additionally, SQL Server does not permit easy-to-guess passwords, such as the name of the computer, the login name, null or empty passwords, or specific low-security passwords such as "admin," "administrator," "password," "sa," or "sysadmin."

Another subsection of Requirement 2 specifies disabling all unnecessary and insecure services and protocols. By default, many SQL Server 2008 features are not active. These include the following features:

If your installation of SQL Server requires any of these features, you can use Management Studio or the sp_configure system stored procedure to enable them. Similarly, if there are features that you know are unnecessary, you can use Management Studio or sp_configure to disable them.

SQL Server provides another important feature that is disabled by default. During the application setup, you must select one of two authentication modes for the Database Engine: Windows Authentication mode or mixed mode. If you select Windows Authentication mode, the sa account is disabled by default.

The new SQL Server 2008 installations also do not include the BUILTINAdministrators local Windows group in the sysadmin fixed server role. And if any of your processes or code require access to the BUILTINAdministrators group, you must explicitly grant permission to log in to SQL Server. The fact that an account is a member of the Administrators group does not automatically grant that account access to SQL Server.

Using Windows Authentication to connect to SQL Server has several advantages for security. For example, by using Windows Authentication, you don’t have to worry about user credentials passing over the network during authentication. Potential attackers who monitor the network for user names or passwords will not find anything. Additionally, users must satisfy the security policies that have been established for Windows Authentication, including minimum password lengths, password complexity requirements, mandatory password expiration times, and lockout after a specified number of incorrect login attempts.

Windows Authentication also links a SQL Server identity to an Active Directory account. Using Active Directory Domain Services for access management enables SQL Server to enforce password policy from a central location, disable accounts, and maintain consistent user identities across all servers.

If SQL Server does use Windows Authentication, it can support the Kerberos authentication protocol through the use of the Windows Security Support Provider Interface. Kerberos provides mutual client-server authentication by using encrypted authentication tickets. These tickets allow a logged-on user to access any service without logging on again. It is a best practice to use Kerberos authentication to connect to SQL Server.

Requirement 3: Protecting Cardholder Data

Requirement 3 specifies protecting cardholder data in various ways. Some of this is as simple as not keeping cardholder data longer than necessary. But most of the subsections of Requirement 3 deal with encryption of data. SQL Server 2008 offers several different features that help you encrypt data. These include the following features:

Listing 1 uses the commands and functions shown in Table 1 to encrypt and decrypt the Sample database by using a certificate that is installed on the CertificateServer server. (Also, see “Securing Your SQL Server Environment.”)

USE master;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = '';GOCREATE CERTIFICATE CertificateServer WITH SUBJECT = 'Sample Certificate'GOUSE SampleGOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_128ENCRYPTION BY SERVER CERTIFICATE CertificateServerGOALTER DATABASE SampleSET ENCRYPTION ONGO

Another provision of Requirement 3 is to make sure that the whole PAN is not visible. The standard procedure is to mask the PAN when it is displayed, except for either the first six digits or the last four digits. This is a task that any database (including SQL Server) can perform. One way to achieve this result is to create a field specifically to view the PAN. To do this, you can combine the first six digits, a string of symbols, and the last four digits. The resulting field is then used whenever a display is required.

Requirement 4: Encrypt cardholder data across public networks

Requirement 4 mainly involves protecting cardholder data that travels across public networks, including the Internet and wireless technologies. SQL Server has features to help meet this requirement, including support for Secure Sockets Layer (SSL) encryption. To use SSL encryption, you must install a certificate on the server by using the Microsoft Management Console (MMC) snap-in.

Requirement 6: Develop secure applications

This one seems obvious, right? Most of Requirement 6 involves establishing processes for secure application development, including industry best practices, testing, validation, the use of security patches, the separation of development and production environments, and the use of change control procedures. Web applications have their own set of rules. This creates a structure of secure development that has SQL Server as its target, not as its tool.

It is especially important that you apply all security patches to your installation. Surveys indicate that few database administrators ever apply security patches. However, PCI standards require that you apply critical vendor-supplied security patches within a month of their release.

Requirement 7: Restrict access to cardholder data

On a fundamental level, you should locate your database in an internal network zone that is not accessible from untrusted networks. Additionally, one of the fundamental principles for securing cardholder data is to limit who has access to it. There are, in reality, very few roles in an organization that absolutely require access to cardholder data. Restricting who is given the privilege of accessing cardholder data has two steps: defining the cardholder data in such a way that it can be referenced separately from any other kind of data, and defining roles in such a way that access is denied by default and is granted only explicitly.

This type of approach is known by several related terms:

Implied in these definitions is the idea that privileges are granted not to people but to roles. The access that is required for each role in an organization is defined, and only those people who have a specific role receive the access that is associated with that role. If a person changes roles, their access also changes. This approach is called role-based access control (RBAC).

SQL Server offers a wide range of securables that you can specify, including databases, application roles, objects, roles, schemas, types, and users. For those securables, SQL Server has a very granular system of granting permissions. By using SQL Server, you can define specific actions that roles can perform, including selecting, modifying, executing, viewing, and controlling securables. You can use SQL Server Management Studio to define column permissions by role, and apply this feature to securing the specific columns that hold credit card information.

In SQL Server 2008 and in SQL Server 2005, signed modules help establish the security of execution by using digital certificates while maintaining simplicity of structure and not requiring extensive knowledge by users. A module can be to a stored procedure, a function, a trigger, or an assembly. For example, access to view V might be mediated by stored procedure S. Instead of giving users direct permission to V, you can use a signed module to allow access through S. To do this, you create a digital certificate, and then grant that certificate permission to access the table in V. Then, you sign S by using the digital certificate. Finally, you create a role that has permission to execute S. Thus, any user who has that role can execute S and use a SELECT statement in V. In this way, signed modules support segregation of duties, which is part of Requirement 7.

Note that this method lets users gain access to V without granting them permissions to V. Additionally, users do not have to know the structure of the arrangement between S, V, and the digital certificate. The ability to hide this relationship is an added security feature. At the same time, accessing V does not require any extra knowledge on the part of the user.

Requirement 8: Unique IDs

Using Windows Authentication together with SQL Server 2008 provides additional and significant security. This is certainly true for satisfying Requirement 8, which includes twenty-one specific rules about how to manage logon credentials. Windows Authentication supports unique IDs for SQL Server. The correct configuration of Windows Authentication can satisfy the following specific requirements for your SQL Server installation:

Clearly, using Windows Authentication can help secure SQL Server for PCI compliance.

Requirement 10: Track access to cardholder data

Requirement 10 specifies tracking any access to cardholder data and maintaining logs of all such access. In addition, those same logs must be safeguarded to prevent anyone from falsifying records to hide illegal access.

SQL Server 2008 includes several features that provide or support such tracking capabilities. We have already discussed the use of RBAC to limit and define access. The use of RBAC also enables administrators to track and monitor what actions those roles – and, by extension, the people who fill those roles – are performing.

Additionally, SQL Server Audit enables the creation of automatic logging of defined events to a specified location. The events can include actions at the server level (including login events and changes to management), at the database level, and in the auditing process itself. SQL Server Audit also defines “audit action groups.” These consist of actions that are conceptually related and that can be tracked as a single entity. For example, the SUCCESSFUL_LOGIN_GROUP audit action group consists of several actions that are connected to successful logins to SQL Server, such as new connections or reused connections from a connection pool. On the database level, you can audit such actions as delete, execute, insert, receive, select, and update as performed on objects such as tables, views, and stored procedures. You can use either SQL Server Management Studio or T-SQL to create audits. The list of actions, action groups, and objects is extensive. For more information, see the Related Articles box.

The results of audit actions are sent to a specified target, such as a file, the Windows Security event log, or the Windows Application event log. The Windows Security event log is the preferred target because access to the Windows Application event log is possible for any authenticated user.

If you decide to use a file as a target, you should engage the following security measures to restrict access to this file:

You can also enable SQL Server C2 auditing. C2 is an NCSC security rating for software. C2 auditing monitors the actions of users who have administrative privileges, and it restricts access to logs. You can enable C2 auditing by using SQL Server Management Studio or the sqlcmd command line utility.

SQL Server Policy-Based Management is an additional tool that helps administrators define and enforce rules for servers running SQL Server. The Policy-Based Management feature replaces many features of the Surface Area Configuration tool. Policies can enforce naming conventions in a database, or configuration options on multiple servers. This helps maintain consistency across servers. Policy-Based Management can also help maintain PCI-compliant configurations.

Taking Security Seriously

Keep in mind that it is important to carefully record the processes that your organization uses to achieve compliance. Your records can help a Qualified Security Assessor verify all the steps that have been performed.

Achieving PCI DSS compliance is a complex process for an organization to achieve. Fortunately, SQL Server provides many features to help you achieve compliance. The result will be enhanced security for your entire organization.