# Ms sql jdbc 7.9

**URL:** <https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [June 9, 2022, 12:25pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540 "2022-06-09T12:25:37Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [June 9, 2022, 12:25pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/1 "2022-06-09T12:25:37Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [June 9, 2022, 1:35pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/2 "2022-06-09T13:35:51Z")

</div>

I’m wondering if this might be a useful resource: [Support matrix - JDBC Driver for SQL Server | Microsoft Docs](https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver16#java-and-jdbc-specification-support)

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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [June 9, 2022, 1:36pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/3 "2022-06-09T13:36:42Z")

</div>

Sorry Kevin, it’s SQL 2016, thanks for the link checking now

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [June 9, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/4 "2022-06-09T13:41:05Z")

</div>

Here’s the error from the logs when trying to use the 10.2.1.jre8 jar file:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [June 9, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/5 "2022-06-09T13:41:31Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [June 9, 2022, 1:41pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/6 "2022-06-09T13:41:56Z")

</div>

Nope I didn’t check that, will have another go, thanks

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [June 9, 2022, 1:44pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/7 "2022-06-09T13:44:17Z")

</div>

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](https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2016#configuring-the-connection)

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [June 9, 2022, 1:50pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/8 "2022-06-09T13:50:03Z")

</div>

> [@kcollins](#):
>
> 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.

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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [June 9, 2022, 2:14pm UTC](https://forum.inductiveautomation.com/t/ms-sql-jdbc-7-9/60540/9 "2022-06-09T14:14:03Z")

</div>

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:

 ![db copy](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/e/febf6a1a86ea4320e42fa31dc17be0a86abf9495.png)