Secure Your Data: Configuring SQL Server 2016 for FIPS 140-2 Compliance

Table of Contents

This article provides a comprehensive guide on configuring SQL Server 2016 and later versions to operate in FIPS 140-2 compliant mode. Understanding and implementing FIPS 140-2 compliance is crucial for organizations that need to adhere to federal security standards, particularly in the United States and Canada. This guide will walk you through the necessary steps and considerations to ensure your SQL Server environment meets these stringent security requirements. By following these instructions, you can enhance the security posture of your data and maintain compliance with industry best practices and regulations.

What is FIPS?¶

FIPS stands for Federal Information Processing Standard. It is a set of publicly announced standards developed by two prominent government bodies:

• The National Institute of Standards and Technology (NIST) in the United States. NIST plays a critical role in developing technology standards and guidelines to enhance U.S. security and innovation.
• The Communications Security Establishment (CSE) in Canada. CSE is responsible for providing the Canadian government with information technology security and foreign signals intelligence.

FIPS standards are not merely suggestions; they are often mandated or highly recommended for use in federal government-operated IT systems in both the United States and Canada. These standards are designed to ensure a consistent and robust level of security across government agencies and organizations that interact with the government. Compliance with FIPS standards demonstrates a commitment to protecting sensitive information and maintaining a secure IT infrastructure.

What is FIPS 140-2?¶

FIPS 140-2, officially titled “Security Requirements for Cryptographic Modules,” is a specific standard within the broader FIPS framework. It focuses on the security requirements for cryptographic modules – the building blocks that implement cryptographic algorithms. This standard is essential for ensuring the confidentiality and integrity of sensitive data.

FIPS 140-2 meticulously specifies:

• Approved encryption algorithms: It dictates which encryption algorithms are deemed secure enough for use in compliant systems. These algorithms are rigorously tested and validated to meet specific security criteria.
• Approved hashing algorithms: Similarly, it outlines the approved hashing algorithms that can be used for data integrity and authentication purposes.
• Key generation and management: Crucially, FIPS 140-2 provides guidelines on how encryption keys should be generated, managed, and protected throughout their lifecycle. Proper key management is paramount to the effectiveness of any cryptographic system.

It is important to understand the distinction between FIPS 140-2 certified and FIPS 140-2 compliant. While algorithms themselves can be certified, and certain hardware and software modules can achieve certification, systems and applications are generally considered to be FIPS 140-2 compliant when they correctly utilize certified modules and algorithms.

FIPS 140-2 Compliance vs. Certification¶

In the context of FIPS 140-2, the difference between being “compliant” and “certified” is significant. SQL Server 2016 and later versions can be configured to be FIPS 140-2 compliant. This means that when properly configured, SQL Server can operate in a manner that adheres to the requirements of the FIPS 140-2 standard.

To achieve FIPS 140-2 compliance, SQL Server must run on an operating system that is either FIPS 140-2 certified or provides cryptographic modules that are certified. The operating systems themselves, such as Windows Server 2012 and later, and Windows 8 and later, undergo rigorous testing and validation to achieve FIPS 140-2 certification for their cryptographic modules.

The key distinction lies in the validation process. Simply using an algorithm listed in FIPS 140-2’s approved lists is insufficient. You must utilize a certified instance of that algorithm. Certification implies that the specific implementation of the algorithm has been government-validated through testing and verification by an approved evaluation laboratory, either in the U.S. or Canada.

Operating systems like Windows Server and Windows client versions include certified instances of all allowed algorithms. When an application, like SQL Server, calls upon these algorithms through the operating system’s cryptographic APIs, it is ensured that it is using the government-validated, certified instance. This is a cornerstone of achieving FIPS 140-2 compliance.

FIPS 140-2 Compliant Applications¶

The potential for FIPS 140-2 compliance extends to a wide range of applications. Any application that performs encryption or hashing and operates on a certified version of Windows can be configured to be FIPS 140-2 compliant. This compliance hinges on several critical factors:

• Utilizing certified algorithm instances: The application must exclusively use the certified instances of approved cryptographic algorithms provided by the underlying certified operating system. This typically means using the operating system’s cryptographic APIs like CryptoAPI or Cryptography Next Generation (CNG) in Windows.
• Adhering to key management requirements: FIPS 140-2 dictates specific requirements for key generation and key management. Applications must either leverage the Windows functions for key management or implement key management practices within the application itself that are compliant with FIPS 140-2. This includes secure key generation, storage, distribution, and destruction.

It’s important to note that FIPS 140-2 compliance is not always an all-encompassing, system-wide attribute. Areas within a FIPS-compliant application may exist where non-compliant algorithms or processes are enabled. For example, internal processes that remain within the system and certain types of external data that are intended for further encryption by a certified algorithm instance might be permitted even if they themselves are not strictly FIPS compliant. However, the core cryptographic operations that are critical for data security must utilize certified modules.

SQL Server 2016 and FIPS 140-2 Compliance¶

It is crucial to understand that SQL Server 2016 and later versions are not inherently FIPS 140-2 compliant out-of-the-box. Instead, they possess the capability to be configured and run in a FIPS 140-2 compliant manner. This distinction is vital for ensuring that organizations take the necessary steps to enable compliance.

SQL Server achieves potential FIPS 140-2 compliance by:

• Configuration: It can be configured to operate in a mode where it exclusively uses FIPS 140-2 certified algorithm instances. This configuration is primarily driven by the operating system’s FIPS mode setting.
• CryptoAPI and CNG usage: When configured for FIPS compliance and when cryptographic operations are required, SQL Server relies on CryptoAPI (Cryptographic Application Programming Interface) or CNG (Cryptography Next Generation) provided by Windows. These APIs ensure that SQL Server utilizes the certified cryptographic modules of the operating system for encryption and hashing whenever FIPS 140-2 compliance is necessary.

Therefore, achieving FIPS 140-2 compliance with SQL Server is a deliberate configuration process that involves setting up both the operating system and verifying the SQL Server environment.

Configuring SQL Server 2016 for FIPS 140-2 Compliance¶

To configure SQL Server 2016 and later versions for FIPS 140-2 compliance, a series of steps must be followed, focusing on both the operating system and SQL Server configurations.

Operating System Requirements¶

The foundational step is to ensure that SQL Server 2016 or a later version is installed on a host operating system that is certified for FIPS 140-2. This typically means using one of the following Windows operating systems:

• Windows Server 2012 and later versions: This includes Windows Server 2012, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019, Windows Server 2022, and subsequent versions.
• Windows 8 and later client versions: This includes Windows 8, Windows 8.1, Windows 10, Windows 11, and subsequent versions.

Microsoft provides detailed lists of FIPS 140-validated cryptographic modules used by various Windows clients and servers on their documentation website. It is essential to verify that the specific Windows version you are using is indeed FIPS 140-2 certified.

Windows System Configuration¶

Enabling FIPS mode in Windows is a critical step. It’s important to note that FIPS mode must be enabled before SQL Server is started. SQL Server reads the FIPS mode setting during its startup process. To enable FIPS mode in Windows, follow these steps as a Windows system administrator:

1. Click Start.
2. Select Control Panel.
3. Choose Administrative Tools. (You might need to switch the Control Panel view to “Large icons” to easily find Administrative Tools).
4. Open Local Security Policy. The Local Security Settings window will appear.
5. In the navigation pane on the left, navigate to Local Policies > Security Options.
6. In the right-hand pane, locate and double-click the policy setting: System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing.
7. In the dialog box that appears, select the Enabled radio button.
8. Click Apply.
9. Click OK.
10. Close the Local Security Settings window.

After completing these steps, the Windows operating system will be running in FIPS mode. Any applications, including SQL Server, that rely on the operating system’s cryptographic services will now be restricted to using only FIPS-approved algorithms and certified cryptographic modules.

SQL Server Service Verification¶

Once FIPS mode is enabled in Windows and SQL Server is restarted, it’s essential to verify that SQL Server has detected and is operating in FIPS mode. SQL Server provides indicators to confirm this.

When the SQL Server service starts (particularly when an endpoint is configured for features like Service Broker or Database Mirroring) and detects that FIPS mode is enabled at the operating system level, SQL Server will log specific messages.

Check the SQL Server Error Log: You should find the following message logged in the SQL Server error log:

Service Broker transport is running in FIPS compliance mode.

Check the Windows Event Log: Additionally, you might find a similar message logged in the Windows event log:

Database Mirroring transport is running in FIPS compliance mode.

The presence of these messages in both the SQL Server error log and the Windows event log definitively confirms that SQL Server has successfully recognized and is running in FIPS compliance mode. Regularly checking these logs after enabling FIPS mode and restarting SQL Server is a crucial step in ensuring ongoing compliance.

Operating SQL Server 2016 in FIPS 140-2 Compliant Mode¶

When SQL Server 2016 and later versions are operating in FIPS 140-2 compliant mode (with FIPS mode enabled in Windows), their behavior changes in specific ways to ensure adherence to the standard:

• System-Controlled Encryption and Hashing: In areas where users or administrators do not have a choice about whether encryption or hashing is used, and how it is implemented (e.g., internal system processes, certain network communications), SQL Server will automatically operate in FIPS 140-2 compliance. This means SQL Server will internally utilize CryptoAPI or CNG in Windows and will exclusively use the certified instances of FIPS-approved algorithms. Users do not need to take any specific action in these areas; compliance is enforced by the system.

• User-Controlled Encryption Options: In areas where users have a choice about whether to enable encryption (e.g., connection encryption, data encryption features), SQL Server’s behavior is designed to maintain FIPS 140-2 compliance. In these scenarios, SQL Server will either:

  • Enable only FIPS 140-2 compliant encryption: If compliant encryption options are available for a particular feature, SQL Server will allow and potentially default to those options.
  • Disable encryption entirely: If no FIPS 140-2 compliant encryption method is available for a user-selectable encryption feature, SQL Server might prevent the user from enabling any encryption at all for that feature to avoid non-compliant configurations.

• Important Considerations for Developers: For software developers who write custom code that interacts with SQL Server and involves encryption or hashing (e.g., using stored procedures, CLR integration, custom applications connecting to SQL Server), it is paramount to understand their responsibilities for maintaining FIPS 140-2 compliance. Developers must be explicitly instructed to:

  • Use only CryptoAPI or CNG: Ensure that all custom cryptographic code utilizes the Windows CryptoAPI or CNG to access cryptographic functions. This is essential to leverage the certified cryptographic modules of the operating system.
  • Specify FIPS 140-2 approved algorithms: Developers must carefully select and specify only those cryptographic algorithms that are explicitly approved by FIPS 140-2. Refer to the official NIST list of approved algorithms in Annexes A, C, and D of the Cryptographic Module Validation Program documentation available on the NIST website. Using non-approved algorithms will violate FIPS 140-2 compliance.

By adhering to these principles, organizations can ensure that SQL Server 2016 and later versions operate in a consistently FIPS 140-2 compliant manner across both system-managed and user-configurable aspects of cryptographic operations.

Effects of Running SQL Server 2016 in FIPS 140-2 Compliant Mode¶

Operating SQL Server 2016 or later versions in FIPS 140-2 compliant mode has several potential effects, primarily related to performance and feature behavior:

• Potential Performance Impact: Using stronger, FIPS-approved encryption algorithms may have a slight performance impact compared to using less robust encryption algorithms that might be allowed when not operating in FIPS 140-2 compliant mode. This performance difference is generally expected to be minor for most workloads. The increased security provided by FIPS-compliant algorithms typically outweighs the minimal performance overhead. However, in extremely performance-sensitive applications, it’s advisable to conduct thorough testing to quantify any potential performance impact.

• SSIS (SQL Server Integration Services) Encryption Limitations: When using SSIS packages and attempting to enable encryption (e.g., setting UseEncryption=True for SSIS package execution or data transfers), you might encounter errors indicating incompatibility with FIPS compliance. Specifically, SSIS encryption options that are not FIPS 140-2 compliant will be disallowed. In such cases, SSIS might operate without encryption for certain processes to maintain FIPS compliance. It’s important to review SSIS package configurations and potentially adjust encryption settings to ensure compatibility with FIPS mode.

• DTS (Data Transformation Services) Legacy Encryption: When utilizing legacy DTS packages (an older data integration technology predating SSIS), encryption behavior is different. Encryption in DTS is generally not FIPS 140-2 compliant. Furthermore, DTS does not inherently check for FIPS mode being enabled in Windows. Therefore, if you are using DTS and need to maintain FIPS compliance, it becomes the user’s responsibility to explicitly select no encryption for DTS processes to remain compliant. Relying on DTS encryption in a FIPS-compliant environment is discouraged.

• Minimal Overall Impact: In most practical scenarios, because the majority of SQL Server 2016 and later versions’ encryption and hashing processes are already designed to be FIPS 140-2 compliant by default, enabling full FIPS compliance (by turning on FIPS mode in Windows) will have little to no noticeable effect on the normal usage or performance of the SQL Server application. The underlying cryptographic operations are largely already aligned with FIPS 140-2 principles.

In summary, while there might be minor performance considerations and specific feature limitations related to encryption in SSIS and DTS, the overall impact of running SQL Server 2016 and later in FIPS 140-2 compliant mode is typically minimal. The enhanced security and regulatory compliance benefits usually far outweigh any minor adjustments or considerations.

Further Information on FIPS 140-2¶

For those seeking to delve deeper into the specifics of FIPS 140-2 and related documentation, the primary resource is the Cryptographic Module Validation Program (CMVP) website hosted by NIST.

Specifically, for comprehensive information on FIPS 140-2 standards and documents, refer to the CMVP FIPS 140-2 Standards and Documents section on the NIST website. This section provides access to the official FIPS 140-2 standard document, implementation guidance, and related publications. It is the authoritative source for understanding the detailed requirements and validation processes of FIPS 140-2.

[External Video on FIPS 140-2 (Example - Replace with actual relevant video if found)]

While Microsoft provides support information for their products, it is important to note that for detailed technical support and interpretation of FIPS 140-2 standards themselves, consulting the official NIST documentation and potentially engaging with FIPS 140-2 experts or certified laboratories is recommended.

Do you have any experiences configuring SQL Server for FIPS compliance? Share your insights and questions in the comments below!