Securing SQL Server: Implementing FIPS 140-2 Compliance for Enhanced Data Protection

Table of Contents

This article provides detailed instructions on Federal Information Processing Standard (FIPS) 140-2 and outlines the process for configuring and using Microsoft SQL Server 2008 in a FIPS 140-2-compliant operational mode. Adhering to FIPS 140-2 is crucial for organizations, particularly those interacting with United States and Canadian government agencies, requiring robust and validated cryptographic security. Understanding these requirements and how SQL Server interacts with the underlying operating system’s cryptographic capabilities is essential for achieving compliance.

It is important to clarify the terminology used throughout this article. The terms “FIPS 140-2-compliant,” “FIPS 140-2 compliance,” and “FIPS 140-2-compliant mode” are used here for clarity and specific definition within the context of SQL Server. These phrases are not formal government-defined terms. Governments in the United States and Canada validate cryptographic modules against standards like FIPS 140-2, rather than certifying an application’s “compliance” in a broad sense.

Within this document, “FIPS 140-2-compliant,” “FIPS 140-2 compliance,” and “FIPS 140-2-compliant mode” signify that SQL Server 2008 is configured and operating in a manner where it exclusively utilizes FIPS 140-2-validated instances of specified algorithms for encryption and hashing. This applies whenever encrypted or hashed data is imported into or exported from the SQL Server 2008 environment. Furthermore, operating in this mode implies that SQL Server 2008 manages cryptographic keys, including generation and storage, in a secure fashion that aligns with the stringent requirements imposed on FIPS 140-2-validated cryptographic modules.

What is FIPS¶

FIPS stands for Federal Information Processing Standards. These standards represent a set of publicly announced standards developed by the United States federal government. Specifically, FIPS standards are developed by two primary government bodies: the National Institute of Standards and Technology (NIST) in the United States and the Communications Security Establishment (CSE) in Canada. They serve as guidelines or mandates for cryptographic modules and security practices used within federal government IT systems in both countries.

The primary purpose of FIPS standards is to ensure that sensitive government data is protected using cryptography that has undergone rigorous testing and validation. This provides a baseline level of security and trust in the cryptographic solutions deployed across various government agencies and their contractors. Compliance with relevant FIPS standards, such as FIPS 140-2, is often a contractual requirement for entities doing business with these governments, particularly when handling sensitive unclassified information (SUI).

What is FIPS 140-2¶

FIPS 140-2 is a specific standard titled “Security Requirements for Cryptographic Modules.” It details the security requirements that cryptographic modules must meet for use in protecting sensitive information within federal government systems. The standard specifies approved cryptographic algorithms, hashing functions, methods for cryptographic key generation, and secure procedures for key management and storage. It provides four increasing levels of security (Level 1 through Level 4) to cover a wide range of potential applications and environments.

Crucially, FIPS 140-2 is not merely a list of approved algorithms; it mandates that the instances or implementations of these algorithms, packaged within a cryptographic module, must be formally validated by an accredited testing laboratory. This validation process involves comprehensive testing to ensure the module functions correctly, uses approved algorithms as specified, and securely manages cryptographic keys. Therefore, simply using an algorithm from the approved list is insufficient; the specific software or hardware module performing the cryptographic operation must be FIPS 140-2 validated.

Configuring SQL Server 2008 to run in what we define as a FIPS 140-2-compliant mode requires it to operate on an operating system that utilizes a FIPS 140-2-validated Cryptographic Service Provider (CSP) or equivalent cryptographic module. Operating systems such as Windows Server 2008, Windows Vista, Windows Server 2003, and Windows XP contain cryptographic modules, specifically their CryptoAPI implementations, that have undergone the required laboratory testing and government validation. By relying on these validated OS components, SQL Server can ensure its cryptographic operations meet the FIPS 140-2 requirements when configured correctly.

What applications can be FIPS 140-2-compliant¶

Any application that performs encryption or hashing operations and runs on an operating system with a FIPS 140-2-validated Cryptographic Service Provider can potentially achieve compliance. The key requirement for such applications is that they must exclusively use the validated instances of approved algorithms provided by the underlying validated cryptographic module for operations involving data protection during import or export. Furthermore, these applications must also adhere to FIPS 140-2’s requirements for cryptographic key generation and management, either by leveraging the key management functions provided by the Windows CryptoAPI or by implementing their own secure key management processes that meet the standard.

It is important to note that in certain specific scenarios, FIPS 140-2 compliance may still be maintained even if a non-compliant algorithm or process is involved internally. This is typically permissible if the data encrypted using the non-compliant method remains strictly within the application’s boundaries and is never exported in that form. Alternatively, the data may be “wrapped” or further encrypted using a FIPS-compliant algorithm before being exported, thereby ensuring that any data leaving the application boundary is protected according to the standard. Applications must be designed or configured carefully to ensure such internal exceptions do not compromise overall compliance requirements, especially concerning data exchange.

Does this mean that SQL Server 2008 is always FIPS 140-2-compliant¶

No, SQL Server 2008 is not inherently FIPS 140-2-compliant out-of-the-box in all configurations. Instead, SQL Server 2008 is capable of being configured and operated in what we define as a FIPS 140-2-compliant mode. This state is achieved through a specific combination of the underlying operating system’s configuration and how SQL Server itself is set up and used. Merely installing SQL Server 2008 on a compatible operating system is not sufficient; the operating system must be explicitly configured to enforce FIPS compliance system-wide.

To successfully run SQL Server 2008 in FIPS 140-2-compliant mode, certain preconditions must be met, primarily related to the operating environment.

• Operating System Requirements: SQL Server 2008 must be installed on a version of the Windows operating system that contains FIPS 140-2-validated cryptographic modules. Compatible operating systems include Windows Server 2008, Windows Vista, Windows Server 2003, and Windows XP. These specific versions of Windows have CryptoAPI implementations that have passed the necessary validation tests required by NIST and CSE.
• Windows System Administration Requirements: The FIPS mode must be enabled at the operating system level before the SQL Server 2008 service is started. This is critical because SQL Server checks the operating system’s FIPS mode setting during its startup sequence. If the FIPS setting is enabled in Windows when SQL Server starts, it will then operate in its FIPS-compliant mode.

The steps to enable FIPS mode at the operating system level vary slightly depending on the Windows version:

• For Windows Server 2008 and Windows Vista:

  1. Log on to the computer using an account with administrative credentials. This ensures you have the necessary permissions to modify system-wide security policies.
  2. Open the Local Group Policy Editor. If you are using Windows Server 2008, click Start, click Run, type gpedit.msc, and then press ENTER. If you are using a Windows Vista-based computer, click Start, type gpedit.msc in the Start Search box, and then press ENTER.
  3. Navigate through the policy tree in the Local Group Policy Editor. Expand the Computer Configuration node, then double-click Windows Settings.
  4. Under Windows Settings, double-click Security Settings.
  5. Within the Security Settings node, double-click Local Policies, and then select Security Options.
  6. In the details pane on the right, locate and double-click the policy setting named System cryptography: Use FIPS-compliant algorithms for encryption, hashing, and signing.
  7. In the dialog box that appears for this policy setting, select the Enabled option. Click OK to apply the change and close the dialog box.
  8. Close the Local Group Policy Editor. Restarting the computer is often required for this policy change to take full effect system-wide before starting SQL Server.

• For Windows Server 2003 and Windows XP:

  1. Log on to the computer using an account with administrative credentials, ensuring sufficient privileges.
  2. Open the Group Policy window. Click Start, click Run, type gpedit.msc, and then press ENTER.
  3. In the Group Policy window, navigate the policy structure. Double-click Windows Settings under the Computer Configuration node, and then double-click Security Settings.
  4. Under the Security Settings node, double-click Local Policies, and then select Security Options.
  5. In the details pane on the right side of the window, find and double-click the policy setting labeled System cryptography: Use FIPS-compliant algorithms for encryption, hashing, and signing.
  6. In the dialog box for the policy, click the Enabled option button. Click OK to confirm the change and close the dialog box.
  7. Close the Group Policy window. A system restart is typically needed for the policy change to be fully active before starting the SQL Server service.

SQL Server 2008 administrator notes¶

When managing a SQL Server 2008 instance intended to operate in FIPS 140-2-compliant mode, administrators should be aware of several key behaviors and configuration requirements. These points are crucial for verifying the operational state and ensuring that specific features interact correctly within the FIPS-enforced environment.

• Verification of FIPS Mode: Once the operating system’s FIPS mode is enabled and SQL Server 2008 is started, the service will detect the FIPS setting. To confirm that SQL Server is running in FIPS mode, administrators should check the SQL Server error log. A specific message is logged indicating that the Service Broker transport is operating in FIPS compliance mode.

  Service Broker transport is running in FIPS compliance mode

  Additionally, for instances utilizing database mirroring, a similar message may be recorded in the Windows Application log, confirming that the Database Mirroring transport is also adhering to FIPS requirements.

  Database Mirroring transport is running in FIPS compliance mode

  Locating these messages in the respective logs provides positive confirmation that the SQL Server instance has successfully initiated in FIPS-compliant mode and is using the validated cryptographic modules for these components.

• Service Broker Encryption: In a FIPS-enabled environment, securing communications between services using Service Broker relies on FIPS-validated algorithms. If dialog security is configured for Service Broker endpoints while FIPS mode is active, the encryption process will specifically use the F FIPS-certified instance of the Advanced Encryption Standard (AES). Conversely, if FIPS mode is disabled at the OS level, Service Broker might default to using the RC4 algorithm for encryption, which is not FIPS-compliant.

• Configuring Service Broker Endpoints: When configuring a Service Broker endpoint in SQL Server 2008, especially when operating in FIPS mode, it is mandatory to explicitly specify AES as the encryption algorithm. Attempting to configure a Service Broker endpoint to use RC4 when the underlying Windows operating system is enforcing FIPS mode will result in an error. This error prevents the transport layer of the Service Broker endpoint from starting, effectively blocking non-compliant communication attempts. Therefore, ensuring endpoints are configured with ENCRYPTION = REQUIRED ALGORITHM AES or similar appropriate settings is vital for Service Broker functionality in a FIPS-compliant environment.

How does SQL Server 2008 operate in FIPS 140-2-compliant mode¶

When the Windows operating system’s FIPS mode is enabled, SQL Server 2008 adjusts its behavior regarding cryptographic operations to align with the compliance requirements. The impact varies depending on whether SQL Server is performing internal operations or user-driven encryption/hashing.

• If FIPS mode is activated in Windows, and SQL Server performs internal encryption or hashing operations where the user or administrator does not have direct control over the choice of algorithm (e.g., Service Broker transport security, Database Mirroring encryption), SQL Server 2008 will automatically operate in FIPS 140-2-compliant mode for these functions. In such cases, SQL Server relies on the Windows CryptoAPI and is restricted to using only the validated instances of algorithms provided by the underlying FIPS-validated cryptographic modules.
• If FIPS mode is on, and a user or developer has the option to specify encryption or hashing for certain data or processes (e.g., encrypting columns, using ENCRYPTBYPASSPHRASE), SQL Server 2008 will enforce the use of FIPS 140-2-compliant encryption methods. This means that only algorithms deemed acceptable under FIPS 140-2 and available through the validated CryptoAPI instances will be permitted. Attempts to use non-compliant algorithms or methods may result in errors.

Important information for developers:

Developers writing applications or scripts that interact with SQL Server 2008 and perform encryption or hashing operations must adhere strictly to FIPS 140-2 guidelines when the server is running in compliance mode. All custom encryption or hashing logic should leverage the Windows CryptoAPI to ensure validated algorithm instances are used. Developers must limit their algorithm choices to those specifically allowed by FIPS 140-2 for the relevant purpose. For encryption, this primarily includes Triple Data Encryption Standard (3DES) and Advanced Encryption Standard (AES). For hashing, SHA-1 is permitted, although stronger SHA-2 variants (SHA-256, SHA-512) are also validated and often preferred.

SQL Server 2008 provides specific keywords that map to these FIPS 140-2-validated algorithms when used with built-in encryption functions. Developers should use the following keywords:

• DESX: When specified, SQL Server 2008 actually uses a validated instance of Three-key triple DES.
• Triple-DES: This keyword refers to Two-key triple DES, another validated 3DES variant.
• TRIPLE_DES_3KEY: Explicitly specifies the use of Three-key triple DES.
• TRIPLE_DES_2KEY: Explicitly specifies the use of Two-key triple DES.

Note: It is a common point of confusion that selecting DESX in SQL Server 2005 or SQL Server 2008 does not implement the original DESX algorithm. Instead, both versions were designed to use a validated instance of Three-key triple DES when DESX is specified, ensuring compliance where needed.

Important information for developers using EKM:

SQL Server 2008 introduced the Enterprise Key Management (EKM) feature, allowing cryptographic keys to be managed outside the SQL Server process, typically within a separate hardware security module (HSM). When operating SQL Server 2008 in FIPS 140-2-compliant mode and utilizing EKM, the external cryptographic module (the HSM) itself must meet FIPS 140-2 requirements. One of the following conditions must be satisfied:

• The external cryptographic module (HSM) used for key management and operations must be FIPS 140-2 validated as a complete module.
• If the external module performs cryptographic operations, any algorithms it uses that are involved in encrypting or decrypting data being imported or exported to or from SQL Server must be FIPS 140-2 validated instances. Developers must ensure that only these validated instances provided by the external module are invoked for FIPS-required operations.

Furthermore, any data passed between SQL Server and the external cryptographic module that requires protection must itself be handled securely. If this data needs to be encrypted or decrypted by the external module for FIPS 140-2-based import/export scenarios, the communication channel and the data itself must be protected using a FIPS 140-2-validated instance of an encryption algorithm.

What is the effect of running SQL Server 2008 in FIPS 140-2-compliant mode¶

Enabling FIPS 140-2 compliance in SQL Server 2008 through the Windows operating system setting has several practical effects on the database environment and its applications. These effects range from potential performance nuances to restrictions on the use of certain features or algorithms.

• Performance Considerations: Using stronger, FIPS-validated encryption algorithms, such as AES or Triple DES, may introduce a minor performance overhead compared to less secure algorithms like RC4, which might be used when FIPS mode is disabled. This effect is generally small and localized to those processes where less strong encryption would otherwise have been an option. For most standard database operations, the performance impact of FIPS mode is negligible as many core cryptographic functions already rely on the underlying Windows CryptoAPI, which is optimized.
• SQL Server Integration Services (SSIS): When FIPS mode is enabled, the default package encryption option in SSIS, which uses RC4 (mapped to UseEncryption=True), is incompatible with FIPS compliance. Attempting to execute an SSIS package configured with this default encryption will generate an error message indicating that the available encryption is not FIPS-compliant and is disallowed. This effectively means that the message process within the SSIS package will not be encrypted if it relies on this non-compliant method. To secure sensitive data in SSIS packages under FIPS compliance, alternative methods like encrypting specific sensitive properties or using Windows storage protection must be employed.
• Legacy Data Transformation Services (DTS): Legacy Data Transformation Services (DTS), often used with older SQL Server versions and sometimes still present in environments upgraded to SQL Server 2008, does not check or adhere to the Windows FIPS mode setting. Therefore, using encryption features within DTS packages is not FIPS 140-2 compliant, even if the operating system has FIPS mode enabled. For environments requiring FIPS compliance, migration from DTS to SSIS (and using FIPS-compliant SSIS security methods) or other compliant data movement strategies is recommended.
• General Impact: The majority of SQL Server 2008’s built-in encryption and hashing operations, particularly those related to internal system functions like database encryption (TDE), cell-level encryption, and hashing for security purposes, are designed to use the underlying Windows CryptoAPI. When Windows FIPS mode is turned on, CryptoAPI enforces the use of FIPS 140-2-validated cryptographic modules. Consequently, if you are already utilizing these standard SQL Server security features, running the application in FIPS 140-2-compliant mode by enabling the Windows setting will likely have little to no disruptive effect on their functionality or performance, as they were already utilizing FIPS-compliant mechanisms indirectly. The primary impact is on features or custom code that might attempt to use non-compliant algorithms.

Using third-party products or custom code that performs cryptographic operations within or alongside SQL Server 2008 requires careful review when FIPS compliance is mandated. Any such components must also utilize FIPS 140-2-validated cryptographic modules and only approved algorithms to maintain the overall compliant state of the environment. The responsibility for ensuring third-party components and custom applications meet FIPS requirements rests with the implementer.

Have you implemented FIPS compliance for your SQL Server instances? What challenges or successes have you encountered? Share your thoughts and experiences in the comments below!