JDBC Gateway Server

Question

Question

David Satorres · Aug 11, 2020

Hello all,

I'm trying to setup the JDBC Gateway Server so customers can connect to IRIS remotely using JDBC and not ODBC. But I'm facing a problem connecting, as our system department tells me IRIS is using the loopback address (127.0.0.1) and that makes remote systems cannot connect to port 53773 (the default port for that).

So, I would like to change this 127.0.0.1 host for the hostname but I cannot see where to do it:

Anyone with experience here to help me? :-)

Thanks a lot

Discussion (15)2

Log in or sign up to continue

Robert Cemper · Aug 11, 2020

Exactly!

here is the example Defining a JDBC Connection URL

   jdbc:IRIS://<host>:<port>/<namespace>

where the parameters are defined as follows:

host — IP address or Fully Qualified Domain Name (FQDN). For example, both 127.0.0.1 and localhost indicate the local machine.
port — TCP port number on which the InterSystems IRIS SuperServer is listening. The default is 51773 (or the first available number higher than that if more than one instance of InterSystems IRIS is installed — see DefaultPort in the Configuration Parameter File Reference).
namespace — InterSystems IRIS namespace to be accessed.

1 0

Alexander Koblov · Aug 11, 2020

David,

JDBC Gateway is used when IRIS needs to connect to 3rd party database via JDBC.

If you or your customers need to connect to IRIS via JDBC -- then JDBC Gateway is not needed. Just use superserver port.

2 0

score 1 · Answer 1 · 2020-08-11T07:02:16-04:00

It's not clear to me what you try:

"..so customers can connect to IRIS remotely using JDBC and not ODBC...."

So they need the related Java-Libraries to connect. see Establishing JDBC Connections

While the setting in your jpg. relate to the SQL-JDBC Gateway that allows access to other DBs over JDBC.
You have also a JAVA gateway to run java out of IRIS See Using the Java Gateway.

score 0 · Answer 2 · 2020-08-11T07:44:56-04:00

Hi Robert,

Maybe I didn't make myself clear enough... customers can't reach the port:

$ telnet xxxxxxx.com 53773
Trying 172.23.2.84...
telnet: connect to address 172.23.2.84: Connection refused
telnet: Unable to connect to remote host: Connection refused

When I ask my Systems&Network department they say that IRIS is only "listening" to localhost (127.0.0.1), and that's the reason we cannot reach the port.

My understanding is that IRIS is bound to l0 interface instead to eth0. Am I completely wrong here?

score 3 · Answer 3 · 2020-08-11T08:27:41-04:00

AHH! that sounds quite different.

First is your super server port really 53773 ?
Default is 51773. While 53773 might be a gateway port for reverse connection.
you can check in SMP System > Configuration > Memory and Startup

Otherwise
I suspect telnet as a protocol might be blocked by some firewall by principle.
from a terminal prompt in IRIS you might try this:

USER>set tcp="|TCP|7000" USER>open tcp:(:7000) use tcp read req#15

So you create a TCP listener on port 7000 hanging around
Then try to connect from an external server by telnet to port 7000 on your server
As soon as you send 15 or more characters the READ will complete and you see the content in variable req

As you describe I assume this will fail. Indicating that some firewalls or similar blocks access.
Most likely the port is blocked.

In addition netstat -a from system prompt

or user>$netstat -a from IRIS terminal shows ALL listeners on your system

score 0 · Answer 4 · 2020-08-11T08:20:44-04:00

Eduard Lebedyuk · Aug 11, 2020

By default IRIS listens on all interfaces.

Are you able to access SMP from a remote machine?

0 0

score 0 · Answer 5 · 2020-08-11T09:03:42-04:00

Hi,

Yes, no problem with SMP. Do I understand from your message than even if the JDBC gateway servers is set up at port 53773 users need to point their JDBC to 51773 (superserver port?)

score 1 · Answer 6 · 2020-08-11T09:21:00-04:00

The gateway server is mainly taking care of Logging and tracing using 53773 as a backward link.
So your network experts are right.

score 0 · Answer 7 · 2020-08-11T09:42:54-04:00

Ok then, I understand :-) JDBC works out of the box then, I can stop this JDBC gateway server right? And the users will be taken from the standard users database, exactly the same as when they access via ODBC. Am I correct?

Wow, what a big misunderstanding from my part :D

score 2 · Answer 8 · 2020-08-11T09:47:24-04:00

You are right.
but if the docs were that clear and resistant to misinterpretation,
this forum would be rather poor.

your question helps others to understand.

keep asking!

score 0 · Answer 9 · 2021-10-27T08:03:00-04:00

@Alexander Koblov, can you clarify the relationship between the JDBC Gateway and the SQL Gateway Connection option for JDBC? Is the JDBC Gateway the same thing as the %JDBC.Server located under External Language Servers in IRIS 2021.1, since there is no "JDBC Gateway" menu option in the Management Console?

I'm currently attempting to set up a JDBC SQL Gateway connection for MSSQL Server and am unable to get the jre16 (or jre8) driver to load. OpenJDK 1.8 is installed, JAVA_HOME is set, $JAVA_HOME/bin is in the path, and IRIS has been restarted. We're running IRIS/HealthConnect on RHEL 8.4. I noticed that when clicking the "Test" button in the connection configuration page that the %JDBC.Server is started before getting the error "Connection failed. The driver cannot be loaded" so there appears to be some relationship between the two, but even with logging turned on I'm not seeing any reference to the MSSQL driver's failure to load.

score 0 · Answer 10 · 2021-10-27T08:11:32-04:00

SQL Gateway uses %JDBC.Server to connect to 3rd party databases via JDBC.

SQL Gateway connection is the database connection settings.
%JDBC.Server settings are the settings for the java process that handles these connections.

"The driver cannot be loaded" most likely means that you specified wrong path to the jar file with the MSSQL driver. Or IRIS process does not have permission to access this jar file.

Try also running two following commands from the terminal. They might give some hint on what's wrong

do $system.SQLGateway.TestConnection("ConnectionName")
zwrite %objlasterror

score 0 · Answer 11 · 2021-10-27T09:22:40-04:00

Eduard Lebedyuk · Oct 27, 2021

I just like to add that SQL Gateway is an abstraction over both JDBC and ODBC connections.

0 0

score 0 · Answer 12 · 2021-10-27T10:09:17-04:00

I've gone over the driver configuration dozens of times, verifying the driver classname, classpath, permissions and tried many different variations of properties. The result from the IRIS terminal test:

USER>d $system.SQLGateway.TestConnection("SqlServerJDBC")

Connection failed:

USER>zw %objlasterror
%objlasterror="0 "_$lb($lb(5023,$c(0),,,,,,,,$lb(,"USER",$lb("e^TestJConnection+15^%apiGTW^1","e^TestConnection+25^%apiGTW^1","e^zTestConnection+1^%SYSTEM.SQLGateway.1^1","d^^^0"))))/* ERROR #5023: Remote Gateway Error:  */

Doesn't seem to tell me much ...

score 0 · Answer 13 · 2021-10-27T10:15:09-04:00

Looks like I've gotten past the "Driver could not be loaded issue." I'm now getting:

Remote JDBC error: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Certificates do not conform to algorithm constraints".

This appears to be a certificate issue on the SQL Server side, and neither property trustServerCertificate=true nor encrypt=false has any effect.