SQL Express 2005 database connection ideas?

Transistor · September 4, 2023, 4:27pm

I have two machines running SQL Express 2005 behind their FactoryTalk View Studio HMIs. The machines are software validated (medical device manufacturing environment) so I really don't want to mess with the configuration. When the machines arrived about 15 years ago I got the vendor to create piped connections with read-only access to the database and was able to create some very useful PHP web pages on one of our servers to create web reports on producion, recipes, machine settings, etc.

I'd like to migrate my PHP applications to Ignition but have hit the TLS / security speed bump while trying to create a database connection. We've tried the usual approaches such as SQL Database became faulty after upgradation from V8.1.13 to V8.1.19 - #5 by avaughn but haven't suceeded.

I found an IBM article, https://www.ibm.com/support/pages/version-jdbc-driver-used-sql-server-2005-should-be-changed-best-performance, which seems to suggest that it's just a matter of installing the relevant JDBC driver via Config | Databases | Drivers but since nobody suggested this approach in previous discussions I'd be surprised if it's that easy.

Can anyone suggest why that would or would not work?

Many thanks.

lrose · September 4, 2023, 5:31pm

I would be surprised if it isn’t that easy. The harder part might be finding the relevant JDBC driver.

avaughn · September 4, 2023, 5:44pm

I suspect it would be a combination of installing the correct driver and re-enabling TLS v1.0 and v1.1. As mentioned in the KBA from the other thread it’s often just a matter of upgrading the driver to connect to newer versions of SQL server over TLS v1.2.

Kevin.Herron · September 4, 2023, 6:02pm

Have you tried adding encrypt=false to the connection string?

I'm not sure it will work if the server has a certificate configured, but worth a shot.

Transistor · September 4, 2023, 6:37pm

Yes, but there was no joy with that. I checked a few other non-Ignition threads, etc., but no joy yet.

Should that be older?

avaughn · September 4, 2023, 7:04pm

I was referencing connecting to SQL Server instances that use TLS v1.2 (I think 2008 rc2 and later). Frequently the only change needed in Ignition is a newer driver.

In your case since it’s a version of SQL Server prior to that you may need to downgrade the driver to something that supports that version and then also modify the security file to allow TLS v1.0 and/or v1.1.

It could also be something different though, could you upload a copy of the full faulted message you’re seeing?

Transistor · September 4, 2023, 7:09pm

Thank you. The error message (which contains the evocative words "Unexpected rethrowing") is:

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: "Unexpected rethrowing".)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:656)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:534)
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:300)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:253)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1096)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:587)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Unexpected rethrowing".
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2887)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1881)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2452)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2103)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1950)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1162)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:735)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:357)
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:103)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:652)
	... 12 more
Caused by: javax.net.ssl.SSLException: Unexpected rethrowing
	at java.base/sun.security.ssl.Alert.createSSLException(Unknown Source)
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
	at java.base/sun.security.ssl.TransportContext.fatal(Unknown Source)
	at java.base/sun.security.ssl.SSLTransport.decode(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketImpl.decode(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
	at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1799)
	... 21 more
Caused by: java.io.IOException: SQL Server did not return a response. The connection has been closed. ClientConnectionId:18d410eb-4779-49df-9335-6cdfbedbb137
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:790)
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:840)
	at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:833)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:1003)
	at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:993)
	at java.base/sun.security.ssl.SSLSocketInputRecord.read(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(Unknown Source)
	at java.base/sun.security.ssl.SSLSocketInputRecord.decode(Unknown Source)
	... 27 more

8.1.26 (b2023032308)
Azul Systems, Inc. 11.0.18

avaughn · September 4, 2023, 7:14pm

What version of the JDBC driver are you currently using?

Kevin.Herron · September 4, 2023, 7:26pm

You probably have to re-enable TLS 1.0/1.1 like @avaughn suggested earlier.

This post describes the general changes needed: TLS 1.0/1.1 changes in OpenJDK and Amazon Corretto | AWS Open Source Blog

The only difference is you'll be modifying the runtime on the Ignition Gateway wherever it's installed.

Transistor · September 4, 2023, 8:39pm

I can't find that list on the Ignition gateway (which is V8.1.26).
Where should I look?

If I go back up the Windows Explorer tree I find

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

IMPLEMENTOR="Azul Systems, Inc."
IMPLEMENTOR_VERSION="Zulu11.62+18-SA"
JAVA_VERSION="11.0.18"
JAVA_VERSION_DATE="2023-01-17"
LIBC="default"
MODULES="java.base com.azul.tooling java.management java.logging java.security.sasl java.naming jdk.jfr com.azul.crs.client java.compiler java.datatransfer java.xml java.prefs java.desktop java.instrument java.rmi java.management.rmi java.net.http java.scripting java.security.jgss java.transaction.xa java.sql java.sql.rowset java.xml.crypto java.se java.smartcardio jdk.accessibility jdk.charsets jdk.crypto.ec jdk.crypto.cryptoki jdk.crypto.mscapi jdk.dynalink jdk.httpserver jdk.internal.ed jdk.internal.le jdk.jdwp.agent jdk.jsobject jdk.localedata jdk.management jdk.management.agent jdk.management.jfr jdk.naming.dns jdk.naming.ldap jdk.naming.rmi jdk.net jdk.pack jdk.scripting.nashorn jdk.scripting.nashorn.shell jdk.sctp jdk.security.auth jdk.security.jgss jdk.unsupported jdk.xml.dom jdk.zipfs"
OS_ARCH="x86_64"
OS_NAME="Windows"
SOURCE=".:git:9430fae58165"

which suggests that it's Java version 11.0.18.

avaughn · September 4, 2023, 9:21pm

You’ll want to check C:\Program Files\Inductive Automation\Ignition\user-lib\jdbc

Transistor · September 4, 2023, 10:33pm

Ehm, what are we looking for?
user-lib_jdbc

Now what?
I've had a go at setting up a new driver but can't see how to force it to use the older .jar file.

pturmel · September 4, 2023, 10:46pm

Delete the one you don't want, and restart the gateway. You probably should fix the duplicate PostgreSQL driver while you're at it.

lrose · September 4, 2023, 10:56pm

You probably need an older driver than 7.2 for SQL 2005, based on the matrix.

Of particular interest is this section:

Driver Name	Driver Package Version	End of Mainstream Support
Microsoft SQL Server 2005 JDBC Driver	1.2	June 25, 2011
Microsoft SQL Server 2005 JDBC Driver	1.1	June 25, 2011
Microsoft SQL Server 2005 JDBC Driver	1.0	June 25, 2011

JordanCClark · September 6, 2023, 10:39am

I use jTDS for older versions

Transistor · September 6, 2023, 10:43pm

Cool.

Just install it via gateway | Config | Databases | Drivers | Create new JDBC driver?
Does giving it a unique name allow me to use it for certain database connections and use a more modern version for other database connections?

JordanCClark · September 7, 2023, 10:48am

Yes and yes.

Transistor · September 7, 2023, 10:07pm

Thank you so much for the extra trouble with the screenshots. I hadn't found any reference to jTDS in my previous searches and, if I had, I'm not sure that I could have worked out the configuration. It's also the first time I've set up a driver and so your HTML prompts are useful too. For anyone wanting the text to cut and paste in:

Property	Value
Classname	`net.sourceforge.jtds.jdbc.Driver`
URL format	`jdbc:jtds:sqlserver://hostname`
Connection property instructions	`Use <i>databaseName=YOUR_DATABASE</i> to specify the database to connect to.`

URL instructions (tricky to post in the table above):

With the three parameters (in bold) 
<ul style="list-style-type:none;margin-left:10px;">
<li><b>host</b>: The host name or IP address of the database server.
</li><li> <b>instanceName</b>: (optional) the instance to connect to on the host. 
If not specified, a connection to the default instance is made.</li>
<li> <b>port</b>: (optional) the port to connect to. The default is <b>1433</b>. If you are using the default, you can omit the port and the preceding ':'.</li></ul><br/>
For SQL Server, you specify the <i>database name</i> to connect to using the <code>databaseName</code> property in the <i>Extra Connection Properties</i>.

I am also relieved that I have a separate driver for the older database connections and the drivers should survive an Ignition upgrade (whereas some of the other suggested fixes to the security file get overwritten on each Ignition upgrade).

Thanks again.