# SQL Express 2005 database connection ideas? **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [September 4, 2023, 4:27pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728 "2023-09-04T16:27:42Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 4, 2023, 4:27pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/1 "2023-09-04T16:27:42Z")
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](https://forum.inductiveautomation.com/t/sql-database-became-faulty-after-upgradation-from-v8-1-13-to-v8-1-19/63277/5) 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](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. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [September 4, 2023, 5:31pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/2 "2023-09-04T17:31:24Z")
I would be surprised if it isn’t that easy. The harder part might be finding the relevant JDBC driver. ---
**Author:** ![avaughn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/avaughn/32/38972_2.png) [@avaughn](https://forum.inductiveautomation.com/u/avaughn)\ **Post date:** [September 4, 2023, 5:44pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/3 "2023-09-04T17:44:08Z")
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. ---
**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:** [September 4, 2023, 6:02pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/4 "2023-09-04T18:02:04Z")
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. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 4, 2023, 6:37pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/5 "2023-09-04T18:37:25Z")
> [@Kevin.Herron](#): > > encrypt=false Yes, but there was no joy with that. I checked a few other non-Ignition threads, etc., but no joy yet. > [@avaughn](#): > > ... just a matter of upgrading the driver to connect to _newer_ versions of SQL server over TLS v1.2. Should that be _older_? ---
**Author:** ![avaughn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/avaughn/32/38972_2.png) [@avaughn](https://forum.inductiveautomation.com/u/avaughn)\ **Post date:** [September 4, 2023, 7:04pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/6 "2023-09-04T19:04:23Z")
> [@Transistor](#): > > Should that be _older_? 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? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 4, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/7 "2023-09-04T19:09:33Z")
Thank you. The error message (which contains the evocative words "Unexpected rethrowing") is: ```auto 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 ``` ---
**Author:** ![avaughn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/avaughn/32/38972_2.png) [@avaughn](https://forum.inductiveautomation.com/u/avaughn)\ **Post date:** [September 4, 2023, 7:14pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/8 "2023-09-04T19:14:49Z")
What version of the JDBC driver are you currently using? ---
**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:** [September 4, 2023, 7:26pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/9 "2023-09-04T19:26:29Z")
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](https://aws.amazon.com/blogs/opensource/tls-1-0-1-1-changes-in-openjdk-and-amazon-corretto/) The only difference is you'll be modifying the runtime on the Ignition Gateway wherever it's installed. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 4, 2023, 8:39pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/10 "2023-09-04T20:39:28Z")
> [@avaughn](#): > > What version of the JDBC driver 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** > > ```auto > 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. ---
**Author:** ![avaughn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/avaughn/32/38972_2.png) [@avaughn](https://forum.inductiveautomation.com/u/avaughn)\ **Post date:** [September 4, 2023, 9:21pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/11 "2023-09-04T21:21:36Z")
You’ll want to check `C:\Program Files\Inductive Automation\Ignition\user-lib\jdbc` ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 4, 2023, 10:33pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/12 "2023-09-04T22:33:43Z")
Ehm, what are we looking for? ![user-lib_jdbc](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/e/dee66486e9b58aeaae68bab8dd17bc1d865c2bb7.png) 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 4, 2023, 10:46pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/13 "2023-09-04T22:46:50Z")
Delete the one you don't want, and restart the gateway. You probably should fix the duplicate PostgreSQL driver while you're at it. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [September 4, 2023, 10:56pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/14 "2023-09-04T22:56:44Z")
You probably need an older driver than 7.2 for SQL 2005, based on the matrix. > **[Support matrix - JDBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver16#java-and-jdbc-specification-support)** > > This page contains the support matrix and support life-cycle policy for the Microsoft JDBC Driver for SQL Server. 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 | ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [September 6, 2023, 10:39am UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/15 "2023-09-06T10:39:22Z")
I use jTDS for older versions > **[jTDS - SQL Server and Sybase JDBC driver](https://sourceforge.net/projects/jtds/)** > > Download jTDS - SQL Server and Sybase JDBC driver for free. Open source JDBC 3.0 type 4 driver for Microsoft SQL Server (6.5 up to 2012) and Sybase ASE. jTDS is a complete implementation of the JDBC 3.0 spec and the fastest JDBC driver for MS SQL... ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 6, 2023, 10:43pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/16 "2023-09-06T22:43:51Z")
Cool. 1. Just install it via gateway | Config | Databases | Drivers | Create new JDBC driver? 2. 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? ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [September 7, 2023, 10:48am UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/17 "2023-09-07T10:48:03Z")
Yes and yes. 🙂 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/d/fd537307521d68d81171e21a64e5a0adad9786fa.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/c/4c648da8cac6702a4aa4514306782c0e01ac6161.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/d/1d088939526197ab6a0a5538eb5a0e6498d78043.png) ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [September 7, 2023, 10:07pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/18 "2023-09-07T22:07:13Z")
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 databaseName=YOUR_DATABASE to specify the database to connect to.` | URL instructions (tricky to post in the table above): ```auto With the three parameters (in bold)
For SQL Server, you specify the database name to connect to using the databaseName property in the Extra Connection Properties. ``` 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. ---
**Author:** ![Aaron\_Hoffman](https://avatars.discourse-cdn.com/v4/letter/a/ea5d25/32.png) [@Aaron\_Hoffman](https://forum.inductiveautomation.com/u/Aaron_Hoffman)\ **Post date:** [February 21, 2025, 10:57pm UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/19 "2025-02-21T22:57:46Z")
[https://jtds.sourceforge.net/](https://jtds.sourceforge.net/) I know this is old, but this post helped me connect to MSSQL Server 2000... ---
**Author:** ![Tom\_Beard](https://avatars.discourse-cdn.com/v4/letter/t/ecb155/32.png) [@Tom\_Beard](https://forum.inductiveautomation.com/u/Tom_Beard)\ **Post date:** [August 6, 2026, 3:06am UTC](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728/20 "2026-08-06T03:06:23Z")
Heads up for any future visitors. When using the jTDS driver in 8.3 you can't run a prep update/delete/insert without specifying a parameter. If you are not checking for a returned result, it fails silently ```python # This returns 0 and does not delete sDeleteSQL = "DELETE FROM TestTable" iResult = system.db.runPrepUpdate(sDeleteSQL, [], database="JTDS") # This works and returns qty rows deleted sDeleteSQL = "DELETE FROM TestTable WHERE '1'=?" iResult = system.db.runPrepUpdate(sDeleteSQL, ['1'], database="JTDS") ``` [Next page](https://forum.inductiveautomation.com/t/sql-express-2005-database-connection-ideas/78728.md?page=2)