Greetings!
I’m trying to track down the version of the JDBC version for MSSQL that is packaged with Ignition 8.1.13. I know the connector is not touched during upgrades and this is a fresh installation.
Checking Google and the forums didn’t turn up much other than checking the manifest but that didn’t unfortunately turn up much.
# unzip -p sqljdbc4.jar META-INF/MANIFEST.MF | grep version
Name: com/microsoft/sqlserver/jdbc/JDBCType$UpdaterConversion.class
Name: com/microsoft/sqlserver/jdbc/JDBCType$SetterConversion.class
Name: com/microsoft/sqlserver/jdbc/SSType$GetterConversion.class
I’m trying to figure out which connection properties are supported and that depends on the version. I would venture to guess it’s pretty new but just trying to confirm.
Why I’m asking this is I’m trying to configure a DB connection from a Linux host running RHEL 8.5/Ignition 8.1.13 to a Domain joined MSSQL box running Windows without much success. The trace just indicates a failed username/password but I suspect it’s probably because the Linux host isn’t joined on the domain and there might be an “extra connection string” I need to play with so I can authenticate to the MSSQL using AD credentials instead of Local SQL Auth. Local SQL auth works without any issues
I checked this page for any information:
https://docs.inductiveautomation.com/display/DOC81/JDBC+Drivers+and+Translators
Here is the list of connection properties I was browsing:
If anyone has any pointers - that would be fantastic.
Thanks!
~Jordan
For a fresh install I think it’s version 7.2.1. Take a look at the JAR file for the driver in the $IGNITION/user-lib/jdbc folder.
Ah, if you really have a sqljdbc4.jar version there's no embedded version info but it's safe to say it's ancient and you should probably upgrade unless you have connections to old versions of SQL Server you need to maintain.
Hey Kevin - thanks for the response!
This is just a default installation for 8.1.13. Sounds like the included JAR is pretty old? Not what I would have expected!
We do have some older MSSQL systems, but I have zero issues trying to get it an upgrade (in a lower environment of course).
To get AD Auth setup for MSSQL, has it generally required a JDBC upgrade?
Thanks!
~Jordan
No, not really.
What version of the JDBC driver you need depends on the version of Java (11 for Ignition 8+) and the version of SQL Server you are connecting to.
See the compatibility matrix from Microsoft here: Support matrix - JDBC Driver for SQL Server | Microsoft Learn
Also if by AD auth you're talking about integrated security or whatever I don't think that works at all on Linux. On Windows there are extra steps where you need to put a Microsoft-provided DLL into the right location. See https://support.inductiveautomation.com/hc/en-us/articles/360047131512-Microsoft-SQL-Server-MSSQL-Connection-Guide-for-Ignition
Also I’m seeing version 9.4.0 in the 8.1.13 linux zip file:
Did you restore a .gwbk? For deliberate compatibility reasons, we put your JDBC drivers into the .gwbk and replace the ones on a gateway when you restore.
No restore - this is a fresh and empty installation I’m playing with using the .run installer.
Looks like the zip lists the actual version, that was very helpful, thank you!
Ignition user auth will be configured via LDAPS to AD. I’m just playing with DB connections at the moment. I was thinking I could connect in ignition to MSSQL using mydomain\mssqluser or mssqluser@mydomain.com.
Configured plenty of Linux based apps with LDAPS to AD with zero issues.
I’m also the furthest from being a MSSQL expert which in this case doesn’t help too much.
Appreciate the discussion here!
Thanks!
~Jordan
I believe you would need to set up a Windows user in the db as well (can also be a group), if you haven’t already done so.
It may also help to know what version of SQL Server you’re using.
Hey Jordan,
The user is already present (hence my confusion on why it doesn’t work). Which at this point I’m pretty confident is a user error on my part. I suspect I need to add something else to “Extra Connection Properties” in Ignition perhaps.
Version of the DB is MSSQL Server 2019 CU8 (15.0.4073.23).
