AOAG listener connect via SSH Tunnel. setTransparentNetworkIPResolution doesn't seem to work.

[fivetran-darrenlien]

Question

I have a JDBC client trying to connect to an AOAG source via a listener URI inside a SSH tunneling. The listener DNS return 2 A records - one being the active and the other inactive. My connection is intermittently because sometime listener resolves to the active IP and sometime the inactive IP. I tried setting the setTransparentNetworkIPResolution property for my SqlServerDataSource, but it still doesn't work. Anyone know how to connect via the listener inside a SSH tunnel?

https://learn.microsoft.com/en-us/sql/connect/jdbc/jdbc-driver-support-for-high-availability-disaster-recovery?view=sql-server-ver16

Relevant Issues and Pull Requests

[barryw-mssql]

Hi fivetran-darrenlien,

At this point from your description it is not clear what the issue could be. The tunnel should not be an issue, if it is properly configured. It is possible it could be a driver configuration issue or it could be a networking/SSH Tunnel issue. Have you been able to confirm that you can ping the SQL Server using the SSH tunnel? If that works can you use telnet to test if you can access the specified SQL Server port (i.e. telnet

<port_number>)? If telnet connects, there are no network/SSH issues and the specified port is open and the driver should connect.

If you are still seeing issues, could you please provide driver logs that capture the failure?

Thx,
Barry

[fivetran-darrenlien]

Hi @barryw-mssql

Thank for replying. We know the ssh tunnel is not the issue because we connect when not use the listener via the ssh tunnel 100% of the time. On the ssh tunnel server, we can also do a dns lookup for the listener and it shows 2 A records as expected. Yes, we can ping the server fine. The connection connected intermittently via the listener. It's random. If you make 5 connections, sometime you get 1 or 2 that is ok. I will try to get some logs. Are you expect some driver logs and how do I enable it?

[barryw-mssql]

Please follow these instructions for the JDBC driver logging - https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16

It is recommended that you always inspect the driver logs when there is an issue as there will likely be information in the logs that will help you or the JDBC driver team diagnose the issue

[Jeffery-Wasty]

Hi @fivetran-darrenlien,

We'll be moving forward with closing this issue in the next few days if there is no further reply. If you are still experiencing the above issue, please include driver logs as mentioned above.

[David-Engel]

I would try using multiSubnetFailover=true. That should result in a faster successful connection than TNIR when there are multiple IP addresses and not all of them are active.