Hi All
I’ve been trying to set up a database connection for MSSQL database which is running on an MSSQL 2019 server and end up having faulted connection. I checked the URL, db name, and the user credentials it looks right. I’ve tried logging in with the same credentials on the server management studio and it works.
Is the JDBC driver that comes in as default compatible with the 2019 server version or is it only for 2016 version? Or do I need some special switch to make it work?
Cheers
Sam
I ran into a similar issue recently - I believe it was something to do with a change in TLS versions used by Ignition (not my forte so I can’t speak much to specifics). I believe it has to do with this update.
Check out the latest stable JDBC driver on github, under releases on the right hand side. Latest stable is 9.2.1 at time of writing - you’ll want the jre11 version.
Hi Cory
Thanks for the response, I tried creating a new driver using the jre11 file from the github repo but still no luck.
I would try updating the original MSSQL driver rather than creating a new one.
If that doesn’t work, you’ll want to post any specific stack traces/errors from the gateway status logs. Also, what version of Ignition are you on?
I updated the existing driver and that didn’t help either. I’m running Ignition 8.1.5.
Thanks for your help, I’ll keep digging
You need a few things:
This has to be put in in inductive automation/ignition/lib
Then in our case you have to make sure the service account which ignition is run as is in the allowed security permissions in MSSQL.
Finally make sure this text is in the extra connection properties, database name should be the actual one you’re using:
Nick
I followed the steps from the docs that @PGriffith shared for both scenario 1 and 2, and I’m still struggling.
I want to do a sanity check before poking too much into the firewalls here. So the actual Ignition gateway is hosted on an MS Azure server (which doesn’t have access to the MSSQL DB that I’m trying to connect) but I’m running an instance of the gateway on my VM which has access to the DB and trying to configure the DB through this instance. My question is that when we configure a DB connection from which device is the request being sent out, the device where the gateway is installed or the one where I’ve launched an instance and trying to configure? Thanks
All database connections happen from the gateway to the database. In the designer, or a Vision client, we automatically route the query you issue to the gateway, then return the results back. In Perspective, we run the query on the gateway directly, then ‘stream’ the results over a websocket to the actual session in the browser.
We recently had a similar issue. This was the work around from Inductive Automation. If you update Ignition this will need to be done again as the update will overwrite this.
Launch the Notepad application as an Administrator
Open the java.security file located in C:\Program Files\Inductive Automation\Ignition\lib\runtime\jre-win\conf\security
Remove TLS V1 and TLS V1.1 from the list of jdk.tls.disabledAlgorithms
Restart the Gateway Service
Java 8 Release Changes: https://www.java.com/en/download/help/release_changes.html
I had the same issue and this was the fix for me, its line 729 in the file Damion mentioned.
We also had to enable the "Include Schema in Table Name’ under the advanced properties to see the data correctly.
This hit me as well. Removing the TLS entries did the trick.
Does anyone know what specifically triggers it?
-Shane
