Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

After upgrade JDBC from 6.4 to 12.8, Application failed to connect its database (AWS RDS MSSQL) #2595

Open
Jutamat007te opened this issue Jan 29, 2025 · 7 comments
Open

After upgrade JDBC from 6.4 to 12.8, Application failed to connect its database (AWS RDS MSSQL) #2595

Jutamat007te opened this issue Jan 29, 2025 · 7 comments
Assignees
@machavan
Labels
Waiting for Response Waiting for a reply from the original poster, or affiliated party

Comments

@Jutamat007te
Copy link

Driver version

JDBC 12.8

SQL Server version

Database : AWS RDS MS SQL 2019 Standard edition , Always On

Client Operating System

RHEL 8.8

JAVA/JVM version

JVM 11

Table schema

Provide the table schema to repro the issue.

Problem description

Provide full details of the problem.

Expected behavior

Application , Java application running with Apache Tomcat 9.0.97
Database : AWS RDS MS SQL 2019 Standard edition , Always On
JDBC : 12.8
Symptom : when restart the application , there is following error and the application shutdown due to cannot connect to its database
Interim solution : adding transparentNetworkIPResolution=true or multiSubnetFailover=true into JDBC connection properties
However in Microsoft JDBC knowledge base Setting the connection properties - JDBC Driver for SQL Server | Microsoft Learn.

it mentioned, that " Beginning with Microsoft JDBC Driver 6.0 for SQL Server, it's no longer required to set multiSubnetFailover to "true" to connect to an availability group listener. A new property, transparentNetworkIPResolution, which is enabled by default, provides the detection of and connection to the (currently) active server.

JDBC trace logs

Loading JCIFS configuration from: /app/AppSystems/config/jcifs.properties
Loading JNQ configuration from: /app/AppSystems/config/jnq.properties
java.lang.RuntimeException: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the
host listener.mydatabase-mssqldb-rds.mx6ldguybgep.eu-west-2.rds.amazonaws.com, port xxxx
has failed. Error: "connect timed out. Verify the connection properties.
Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.appanywhere.ga.data.access.DataAccessLayerFactory.initDataSource(DataAccessLayerFactory.java:83)
at com.appanywhere.ga.data.access.DataAccessLayerFactory.initSequenceDataSource(DataAccessLayerFactory.java:76)
at com.appanywhere.ga.data.access.DataAccessLayerFactory.(DataAccessLayerFactory.java:58)
at com.appanywhere.ga.core.system.DataFactory.initialize(DataFactory.java:219)
at com.appanywhere.commons.stream.MemoizingSupplier.get(MemoizingSupplier.java:92)
at com.appanywhere.ga.core.system.DataFactory.getConnection(DataFactory.java:143)
at com.appanywhere.ga.core.system.DataFactory.getConnection(DataFactory.java:131)
at com.appanywhere.ga.core.settings.SettingsController.getSettingsAsMap(SettingsController.java:544)
at com.appanywhere.ga.core.settings.SettingsAPI.getAllSettingsAsMap(SettingsAPI.java:50)
at com.appanywhere.dpa.DPASettings.loadSettings(DPASettings.java:1532)
at com.appanywhere.dpa.DPASettings.load(DPASettings.java:766)
at com.appanywhere.commons.stream.MemoizingSupplier.get(MemoizingSupplier.java:92)
at com.appanywhere.dpa.DPASettings.initialize(DPASettings.java:772)
at com.appanywhere.startup.TomcatStarter.start(TomcatStarter.java:328)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.base/java.lang.reflect.Method.invoke(Unknown Source)
at com.appanywhere.bootstrap.AppAnywhereBootstrap.main(AppAnywhereBootstrap.java:82)
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the host
listener.mydatabase-mssqldb-rds.mx6ldguybgep.eu-west-2.rds.amazonaws.com, port xxxx has failed.
Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP
connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
... 14 more
Application was shut down.
@github-project-automation github-project-automation bot moved this to To be triaged in MSSQL JDBC Jan 29, 2025
@machavan machavan self-assigned this Jan 29, 2025
@machavan machavan added the Under Investigation Used for issues under investigation label Jan 29, 2025
@machavan
Copy link
Contributor

Hi @Jutamat007te We are looking into this. Could you provide following information?

  1. What is the authentication method being used?
  2. Are you using/specifying failoverPartner configuration?

@Jutamat007te
Copy link
Author

Jutamat007te commented Jan 29, 2025 via email •
edited
Loading

@machavan
Copy link
Contributor

Hi @Jutamat007te

transparentNetworkIPResolution default value is true.

However, there are following conditions under which it is set to false.

		// Turn off TNIR for FedAuth if user did not set TNIR explicitly
		if (!userSetTNIR && (!authenticationString.equalsIgnoreCase(SqlAuthentication.NotSpecified.toString())
				|| null != accessTokenInByte)) {
			transparentNetworkIPResolution = false;
		}


                // transparentNetworkIPResolution is ignored if multiSubnetFailover or DBMirroring is true and user did
                // not set TNIR explicitly
                if ((multiSubnetFailover || null != failOverPartnerPropertyValue) && !userSetTNIR) {
                    transparentNetworkIPResolution = false;
                }

  1. If Federated Authentication is being used and user has not explcitly set transparentNetworkIPResolution to true

  2. If (multiSubnetFailover is set to true or failOverPartner is not specified) and user has not explcitly set transparentNetworkIPResolution to true

Based on your input, it looks like transparentNetworkIPResolution may be getting turned OFF as a result of setting multiSubnetFailover to true. Can you please check if it is being set to true in your connection string ?

@Jutamat007te
Copy link
Author

Jutamat007te commented Jan 29, 2025 via email •
edited
Loading

@machavan
Copy link
Contributor

Hi, @Jutamat007te

Can you please share driver traces to investigate this further ?

Below are instructions to enable driver tracing: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

Please enable FINEST level of tracing.

@Jutamat007te
Copy link
Author

Jutamat007te commented Jan 29, 2025 via email •
edited
Loading

@machavan machavan added Waiting for Response Waiting for a reply from the original poster, or affiliated party and removed Under Investigation Used for issues under investigation labels Jan 29, 2025
@machavan
Copy link
Contributor

machavan commented Jan 30, 2025 •
edited
Loading

@Jutamat007te While you are working with the app team to get traces, can you share what all configurations were present in the connection string while the issue was reproduced?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@machavan machavan

Labels
Waiting for Response Waiting for a reply from the original poster, or affiliated party
Projects
MSSQL JDBC
Status: To be triaged
Milestone
No milestone
Development

No branches or pull requests
2 participants
@machavan @Jutamat007te