Trying to connect MS SQL Server 9 (2005) from IRIS Community through JDBC

Question

Question

Joseph Tsang · Aug 20, 2023

#JDBC #SQL #InterSystems IRIS #InterSystems IRIS for Health

I cannot get any further hint from the error message in red in this screenshot. Is there any clue or details about the connection failure that I could look for?

Product version: IRIS 2022.3

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.3 (Build 606U) Mon Jan 30 2023 09:15:36 EST

Discussion (4)2

Log in or sign up to continue

score 0 · Answer 1 · 2023-08-21T05:31:37-04:00

You can enable SQL Gateway Logging.
Also make sure that the necessary JDK/JRE version is installed and configured correctly.

For simplicity, I would check the connection outside of IRIS, for example through DbVisualizer/DBeaver/SQuirreL/etc.

score 0 · Answer 2 · 2023-08-24T06:24:58-04:00

Thanks, Vitality. I got these error messages captured in the log file of the SQL Gateway Logging. It's good to see something new now.

Invalid Message Count (452); expected: -1 got: 1. Server returned: 19801. Closing Connection

score 0 · Answer 3 · 2023-08-21T10:17:49-04:00

Double check that the JDBC 12.2.0 driver has the appropriate permissions, and is still compatible with SQL 2005. It doesn't look like that driver is compatible anymore with 2005.

Microsoft JDBC Driver Support Matrix

Might need to use the jTDS driver but it has it own flaws..jTDS Driver

You can try testing the connection via Terminal..https://docs.intersystems.com/iris20232/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&CLASSNAME=%25SYSTEM.SQLGateway by getting the connection and using TestConnection() to see if it will give you a more verbose output or errors to variables.

score 0 · Answer 4 · 2023-08-24T06:35:44-04:00

Hi Scott,

I tried to test the connection to MS SQL Server 2005 with JDBC by a third-party SQL tool SQuirrel. Having the same connection string set up as the one in IRIS, using the same JDBC jar file, yet with a recent JDK (openjdk version "18.0.2" 2022-07-19), I was able to connect to the database of the target MS SQL Server instance.

Now I'm trying to figure out whether the JDK version made the differences. I didn't mention that in order to make it work, I had to modify the file java.security according to some posts (https://stackoverflow.com/questions/69623611/how-do-i-allow-java-client-...). But this was done before I switched JDK so that should be independent.