So, you have to enable FIPS 140-2 cryptography…

Perhaps you work in an industry that requires you to use FIPS-compliant encryption, or you work in government and are required to follow the Defense Information Systems Agency (DISA) Security Technical Implementation Guidelines (STIGs,) or perhaps your boss heard somewhere that FIPS is a good thing to have. Regardless, you have questions like the following:

• What is FIPS 140-2?
• How do I enable it in SQL Server?
• How will it impact my SQL Servers functioning, will I need to re-write things?
• Will it break anything in my SQL Server environment?

This article will try to answer those questions as well as providing information on where to find more information.

FIPS 140-2

First up, what exactly is FIPS 140-2? FIPS stands for “Federal Information Processing Standard”. 140-2 is a statement released specifying which encryption and hashing algorithms can be used if a piece of software such as an operating system or database application wants to be listed as being “FIPS 140-2 certified” or “FIPS 140-2 compliant.” SQL Server can be considered compliant IF the operating system on which it runs is certified and configured to enforce FIPS 140-2 compliance. Per Microsoft, this requires Windows to be Windows Server 2003 or newer, or Windows XP or newer (on the desktop.)

Enable FIPS

How would you enable FIPS in SQL Server? This is perhaps the easiest question. You don’t. Instead, you need to enable FIPS in the operating system. You can accomplish this by using the “Local Security Policy” MMC in the “Local Policies -> Security Options” section. Find the option “System cryptography: Use FIPS compliant algorithms for encryption, hashing, and signing.” Set this to Enabled and reboot. Or, if you’re in a domain environment, this setting may be controlled through Group Policy Objects, in which case you’ll need to speak with your Domain Administrator team about getting it changed.

According to Microsoft, when you enable that above setting, SQL Server will begin operating in FIPS compliant mode with no changes to start up parameters required. The potential impacts on SQL Server are minimal. You may have a small performance hit in some processes which allow less strong encryption. If you are using service broker with RC4 encryption, the service will not start until you configure it to use AES instead. Any areas in SQL that allow you to choose the encryption method to use, if a non-FIPS algorithm is selected, SQL Server will not use encryption at all.

As for other features you may be using in SQL, such as SSIS or SSRS, there are some potential impacts to watch out for. If you are using the SSIS option “UseEncryption” and have it set to True, you will get errors stating that the “available encryption is incompatible with FIPS compliance.” This will result in no encryption of the message process. You may also get a “System.InvalidOperationException” error when attempting to execute an SSIS package. This can be resolved by installing the most current Service Pack for SQL Server 2012, 2014, or 2016.

If you are using SSRS, now things get interesting. When you enable FIPS and have an SSRS instance (or instances,) SSRS may start throwing errors in the Report Manager screen or when calling a report from the Reports URL. The error may be an HTTP 500, a “System.InvalidOperationException,” or even just a blank screen. Resolving this requires modifying the web.config file for the SSRS instance. The web.config file can be found in “<system-drive>\Program Files\Microsoft SQL Server\MSRS<version>.<instance>\Reporting Services\ReportManager\” Obviously, before editing the file you should make a copy of it. The .config file is an XML document which can be edited in your text editor of choice. Look for the section <system.web> and add the following to that section (I add it on a new line immediately after the <system.web> to avoid potentially adding it in the wrong place)

<machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="3DES" decryption="3DES"/>

In my testing of this on an up-to-date SQL Server 2014 SSRS instance, I found I also needed to add this to the web.config in the ReportServer folder (“<system-drive>\Program Files\Microsoft SQL Server\MSRS<version>.<instance>\Reporting Services\ ReportServer \”) or reports called from there would fail with an error. After making the changes above, stop the SSRS instance and restart it. At this point, your reports should function normally.

References:

• FIPS 140-2 document: https://csrc.nist.gov/csrc/media/publications/fips/140/2/final/documents/fips1402.pdf
• DISA STIGs: https://iase.disa.mil/stigs/Pages/index.aspx
• Microsoft documentation:
  • https://support.microsoft.com/en-us/help/3141890/instructions-for-using-sql-server-2014-in-the-fips-140-2-compliant-mod
  • https://support.microsoft.com/en-us/help/2925865/error-when-you-execute-ssis-package-on-fips-enabled-windows
  • https://blogs.msdn.microsoft.com/dataaccesstechnologies/2015/07/16/report-manager-system-invalidoperationexception-this-implementation-is-not-part-of-the-windows-platform-fips-validated-cryptographic-algorithms/