[FEEDBACK/BUG?] Database Connections faulted after upgrade to Ignition 8.0

Unexpected Behavior :
Database connections faulted after upgrading to Ignition 8. Connection to Local database is fine, network databases faulted.

Database Status message: “Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “Unexpected rethrowing”.)”

Expected Behavior:
Working database connections.

Steps:

  • Checked with other gateway backups and with new connections. Same issue.
  • Downgraded Ignition versions to verify connections worked in 7.9.9. They do.

Info:
Version: 8.0.0-beta0 (b2018111102)
Installed Java Versions: Oracle Java 8 Update 151
Database Connection Driver: Microsoft SQLServer JDBC Driver (4.0)

Any ideas on what is going on?

Hi bennuss11

It seems like ignition is no longer connecting to you database. It may be because, there is problem with your network not seeing the database. You could try creating a new connection to the database and see If you are able to connect.

Usually when you upgrade from 7.9.9 to 8.0 the connection should be retained. You can also try substituting localhost on you connection with the ipaddress of your machine. In the edit connection settings e.g.jdbc:mysql://ipaddress:3306/test.
to see if this helps

We have also seen issues with older JDBC drivers not working properly with Java 11 that the Ignition gateway is now packaged with. Please try updating the driver in Ignition to the jre10 version of the 7.0 driver available from Microsoft. This has corrected all issues we have run into so far.

Well, I have tried a few of your suggestions, and I have learned a few things. In checking the SQL versions of the databases I am trying to connect to, the versions I cannot connect to are SQL Server 2000. (Why we are still using SQL 2000 is a whole different question). So far the others I have tried are all version 2012+, which all work fine.

@ggross, since the 7.0 driver does not support SQL version earlier than SQL Server 2008, i do not believe it will not solve my issue.

Any ideas on how to connect to SQL versions older than 2008?

Might be approaching an unsupportable configuration according the support matrix: https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-2017

1 Like

I just restored a backup from one of my 7.9 gateways to 8. I was using MSSQL JDBC 6.2 and that was copied as part of the backup, but of course that was not compatible. Can their be a warning/upgrade message or alert to notify during restore (prob best on the commissioning check) that the installed JDBC driver is known not to work?

We resolved the same problem with SQL 2005 following this help

Maybe it can help you

Hello Andrea can you tel us
what change you have make exactly, and the version of JDBC you use
Whe have the same issue between SQL server 2005 and ignition 8.0

HI

As described in the link, I just modified the java.security file in the JRE Install folder and remove the value: 3DES_EDE_CBC from the jdk.tls.disabledAlgorithms in the file:

Program Files\Java"JRE Version"\lib\security.

I have used the version of the Jdbc driver included in Ignition V8

Andrea

1 Like

With Ignition 8, that “java.security” file is located at “C:\Program Files\Inductive Automation\Ignition\lib\runtime\jre-win\conf\security” on a Windows computer.

1 Like

Thanks Andrea, it is working for me.

what I’ve done is modify the java.security file in the C:\Program Files\Inductive Automation\Ignition folder and remove the value: 3DES_EDE_CBC from the jdk.tls.disabledAlgorithms in the file

Then restart the Ignition gateway

2 posts were split to a new topic: Unable to connect to SQL Server 2000

summarizing after some test

With Ignition 8.x you should delete the 3DES_EDE_CBC and the TLSv1 values from java.security file.
Instead if you need use the integrated security of Windows for connect to the database SQL server you should also download the mssql-jdbc_auth-x.x.x.x64.dll ( from microsoft web site as described in the help manual) and renamed it to sqljdbc_auth.dll and copy this dll in C:\Program Files\Inductive Automation\Ignition\lib\

Hi,

I updated the MSSQL driver (jre11) as explained in the IA docs (JDBC Drivers and Translators - Ignition User Manual 8.0 - Ignition Documentation)
I also removed the 3DES_EDE_CBC from the jdk.tls.disabledAlgorithms in the file and restarted Ignition.
image

But after upgrading I’m getting the following error:

Could somebody help me find the solution to this problem?
I’m using Ignition 8.0.4 and MSSQL 2019.
They are installed on separate machines and are installed on Linux.

It seems to only be the case when restoring a gateway backup.
If I copy all the resources to a fresh gateway (same version) and create a new database connection then the problem doesn’t seem to appear.

The JDBC driver is stored in the gateway backup. So restoring the gateway backup replaces your newer JDBC driver with an older one. Re-upgrade the JDBC driver after restoring the backup.

Hi Phil,

I did re-upgrade the JDBC driver after restoring the backup.
But still no luck.

The error you’re seeing means the server certificate is not trusted by Ignition. You’ll need to add it to the “supplemental certificates” folder in the Ignition Gateway so it gets imported to the trust store.

Adding Security Certificates into KeyStores - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

Hi Kevin,

Do you have a little more background on this?
How do I get this certificate for example?
Or should I contact support on this?

I don’t know how to get it exactly… it’s going to be something you get out of a configuration tool or something for SQL Server. Presumably the server has a self-signed certificate right now, which is why Ignition doesn’t trust it by default.

Support may be able to help or they may end up just being your partner in Googling.

edit: I think you can also bypass certificate validation by adding trustServerCertificate=true to the JDBC connection parameters.

1 Like

Adding this to the JDBC connection parameters worked for me.