Summary

A recent update to Java 8 (8u291), which also applies to Java 11.0.11 and above, has disabled TLS 1.0 and 1.1. This has resulted in cases of Ignition's connection to Microsoft SQL Server (MSSQL) databases faulting.  The fix is to either configure MSSQL server to use TLS1.2 (preferred but more involved) or to re-enable the less secure TLS1.0 and TLS1.1 by  removing "TLSv1" and/or "TLSv1.1" from the jdk.tls.disabledAlgorithms security property in the java.security configuration file.  Ideally this is something that should be handled by your company's IT department.  The difference between the two options is essentially a trade-off between more security with more setup vs less security with an easier setup.

The Error

The faulted MSSQL DB connection will have an error message that looks like this, if it's related to this TLS issue discussed in this article:

java.sql.SQLException: Cannot create PoolableConnectionFactory (The driver could not establish 
a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. 
Error: "No appropriate protocol (protocol is disabled or cipher suites are inappropriate)".)

To Fix

You will need to either enable TLS1.2 on the SQL Server or re-enable TLS1.0 and TLS1.1 on the Ignition Gateway.

Option 1: Enable TLS1.2 on the SQL Server

Our recommendation is to enable TLS1.2 on the SQL Server since it's more secure. The process to do this can be found from Microsoft, and is based on the version of SQL Server you're using. (Check relevant links below for more details from Microsoft's documentation). 

** If TLS1.2 is already enabled on SQL Server or you're not sure check the update driver section below.

Option 2: Re-enable TLS V1 and TLS V1.1 on the Ignition Gateway

The following option is less secure, but requires less setup. These steps are very similar on Windows, Mac, and Linux, the key difference is the location of the Java install directory on each operating system.

1. Launch the Notepad application as an Administrator, or run any text editor with admin privileges.
2. For Ignition 8+: Open the java.security file, which is within the Ignition install directory on these versions of Ignition. Within the Ignition install directory you'll want to navigate to the following folder to find the security file "\lib\runtime\jre-win\conf\security". There's a link below in Relevant Links that shows default install directories per OS. For example, if Ignition is installed in the default directory on windows the file would be located here:
   

   C:\Program Files\Inductive Automation\Ignition\lib\runtime\jre-win\conf\security

   For Ignition 7.9 and earlier: Open the java.security file located in the Java install directory. This location is dependent on what implementation of Java is installed, but a common default Java installation directory is shown below. You will need to do this for the Java install directory that the Ignition Gateway is using to run.

   C:\Program Files\Java\jreX.X.X_XXX\lib\security

3. Remove TLS V1 and TLS V1.1 from the list of jdk.tls.disabledAlgorithms (see screenshots below)
4. Restart the Gateway Service for the changes to take effect

Before:

After:

Update Driver:

If TLS1.2 has already been enabled on your SQL Server instance and you're still seeing errors regarding the cipher suite, you will likely need to upgrade your JDBC Driver. This is especially likely if you recently upgraded from an older version of Ignition as the driver is not automatically updated (See here for more detail). Your current driver version can be seen in the following directory: {installDirectory}/user-lib/jdbc

Where to Acquire New Driver:

• Beginning with Ignition 8.1.8, updated JDBC drivers are bundled with the install and can be pulled from {installDirectory}/user-lib/jdbc-bundled.
  
  • Use the mssql-jdbc-9.4.0.jre11.jarfile from this directory. 
• If you're running an Ignition version before 8.1.8, you'll need to download the updated driver from Microsoft (Download here).  
  
  • Download the zip version of the JDBC Driver.
  • Extract the mssql-jdbc-{driver_version}.jre{java_version}.jar file.

Important Notes About the Driver Version:

• Using the Compatibility Matrix, ensure you download a JDBC driver that's compatible with your version of SQL Server.
• Starting with JDBC 10.2, TLS encryption is enabled by default (Release Notes). You must perform one of the following actions when using version 10.2+.
  1. Configure an SSL certificate on your SQL Server Instance and add the certificate to your Ignition Gateway. 
     • Certificate Location: {installDirectory}\Ignition\data\certificates\supplemental
  2. Add the trustServerCertificate=true connection setting to the Extra Connection Properties of your Faulted Database connection.

How to Upgrade:

1. Once the correct JDBC Driver version is acquired, navigate to your Gateway's Webpage. 
2. Under the Config section click on Databases > Drivers. 
3. Click the Edit button for the Microsoft SQLServer driver
4. Pass in the new driver to the JAR File(s) property.
   1. Click the Choose File button
   2. Navigate to the driver, and click Open.
5. Click the Save Changes button at the bottom of the page

Relevant Links:

TLS 1.2 Support for Microsoft SQL Server:

https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe 

Java 8 Release Changes, the fourth bullet point of the 291 release notes in particular:

https://www.java.com/en/download/help/release_changes.html

SQL Server JDBC Downloads:

https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 

Release Notes for SQL Server JDBC Driver:

https://docs.microsoft.com/en-us/sql/connect/jdbc/release-notes-for-the-jdbc-driver?view=sql-server-ver16 

JDBC Driver Support Matrix:

https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver16 

https://docs.inductiveautomation.com/display/DOC81/Installing+and+Upgrading+Ignition#InstallingandUpgradingIgnition-IgnitionDefaultInstallationDirectories