I am migrating data from MySQL to MS SQL, and getting a “can’t copy unknown to unknown” error in the script console. Searching the forum, other users have cleared this with a JDBC driver upgrade.
I went for the latest one from the MS website (10.2.1.0) but after applying it, the connection to MS SQL was refused so I reverted to the previous one as it’s a live system. Looking into the error in the logs, searching forum again suggested to delete TLS 1 and 1.1 from the java.security file, however we are using Ignitoin 7.9.12 with Java 8.0.201 and it wasn’t present in the file.
Is there a compatibility list anywhere that will suggest the highest JDBC driver that will work with my Ignition and Java version?
I’m wondering if this might be a useful resource: Support matrix - JDBC Driver for SQL Server | Microsoft Docs
You didn’t mention the version of SQL Server that you’re using, but by the looks of it, there are some compatibility matrix choices for Database Engine version too. It looks like if you’re SQL Server 2012 or higher, the one you tried (mssql-jdbc-10.2.1.jre8.jar ?) should have been able to work.
Sorry Kevin, it’s SQL 2016, thanks for the link checking now
Here’s the error from the logs when trying to use the 10.2.1.jre8 jar file:
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:fc9449bf-0781-43ae-a3c9-34932fe58ca4)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:261)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:159)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:254)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:142)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:88)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:73)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepQuery(GatewayDBUtilities.java:145)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:308)
at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:431)
at org.python.core.PyObject.__call__(PyObject.java:422)
at org.python.core.PyObject.__call__(PyObject.java:426)
at org.python.pycode._pyx238.f$0(:8)
at org.python.pycode._pyx238.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1275)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:636)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:603)
at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:88)
at java.util.TimerThread.mainLoop(Unknown Source)
at java.util.TimerThread.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: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:fc9449bf-0781-43ae-a3c9-34932fe58ca4
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3680)
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2113)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3204)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2833)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2671)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1640)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:936)
at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 27 common frames omitted
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.ssl.Alerts.getSSLException(Unknown Source)
at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)
at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)
at sun.security.ssl.Handshaker.processLoop(Unknown Source)
at sun.security.ssl.Handshaker.process_record(Unknown Source)
at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:2021)
... 36 common frames omitted
Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.validator.PKIXValidator.doBuild(Unknown Source)
at sun.security.validator.PKIXValidator.engineValidate(Unknown Source)
at sun.security.validator.Validator.validate(Unknown Source)
at sun.security.ssl.X509TrustManagerImpl.validate(Unknown Source)
at sun.security.ssl.X509TrustManagerImpl.checkTrusted(Unknown Source)
at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSChannel$HostNameOverrideX509TrustManager.checkServerTrusted(IOBuffer.java:1702)
at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(Unknown Source)
... 45 common frames omitted
Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.provider.certpath.SunCertPathBuilder.build(Unknown Source)
at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(Unknown Source)
at java.security.cert.CertPathBuilder.build(Unknown Source)
... 53 common frames omitted
Ahh, then you should be able to use the most recent version… Do make sure that once you’ve installed any new driver, that the old one has been properly removed from ${IGNITION_INSTALL_LOCATION}/user-lib/jdbc. There is logic that should handle swapping things out, so you should be okay, but you don’t want both in that directory.
Nope I didn’t check that, will have another go, thanks
Looks like it is failing on the TLS portion. I suppose you could add trustServerCertificate=true to the JDBC connection parameters to try and get past that–might be worth circling back on though at some point.
EDIT: another useful ref: Connecting with encryption - JDBC Driver for SQL Server | Microsoft Docs
1 Like
I think this is the key. The latest versions of the JDBC driver enable encryption by default, which means you need to have your certificate trust sorted out.
1 Like
Thanks both, yep that’s got that issue sorted with the DB connection on the newest JDBC driver.
Has also removed the “unknown to unknown” error.
Now I’m facing this which doesn’t make sense, as you can see the object exists I am selecting from it in the query browser:
