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

[Jutamat007te]

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.

[machavan]

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]

Hi Chavan, See further information as below 1. What is the authentication method being used? * Application using basic authentication (username + password) 2. Are you using/specifying failoverPartner configuration? * We use AWS RDS MS SQL which fully managed service by AWS as this result DB failover is a built in service Regards,’ Jutamat Phothisitthisak (Kate) Service Manager.

[machavan]

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]

The provided error is before applying the interim solution by adding transparentNetworkIPResolution=true or multiSubnetFailover=true into JDBC connection, after adding, application able to connect to database without any issue However I wonder why we need to adding above properties since Microsoft claimed that in version up to 6, it will be added by default? Or I miss anything ? or in another hand, base on your response and from my understanding. It implied that the issue may cause by Application has not add bellowing line to true ? // Turn off TNIR for FedAuth if user did not set TNIR explicitly if (!userSetTNIR && (!authenticationString.equalsIgnoreCase(SqlAuthentication.NotSpecified.toString()) || null != accessTokenInByte)) { transparentNetworkIPResolution = false; >> Need to update to true } // transparentNetworkIPResolution is ignored if multiSubnetFailover or DBMirroring is true and user did // not set TNIR explicitly if ((multiSubnetFailover || null != failOverPartnerPropertyValue) && !userSetTNIR) { transparentNetworkIPResolution = false; >> Need to update to true } Jutamat Phothisitthisak (Kate) Service Manager – Enterprise Service From: Mahendra Chavan ***@***.***> Sent: Wednesday, January 29, 2025 2:45 PM To: microsoft/mssql-jdbc ***@***.***> Cc: Phothisitthisak, Jutamat ***@***.***>; Mention ***@***.***> Subject: Re: [microsoft/mssql-jdbc] After upgrade JDBC from 6.4 to 12.8, Application failed to connect its database (AWS RDS MSSQL) (Issue #2595) *** EXTERNAL EMAIL *** Hi @Jutamat007te<https://github.com/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 ?

…

------------------------------------------------------------------------------------------------------------

[machavan]

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]

Unfortunately, we not able to enable driver tracing in the code as we are not own the source code. This application developed by 3rd party, however I can share the ticket # and ask them to provide the further action/detail . Jutamat Phothisitthisak (Kate) Service Manager – Enterprise Service From: Mahendra Chavan ***@***.***> Sent: Wednesday, January 29, 2025 6:49 PM To: microsoft/mssql-jdbc ***@***.***> Cc: Phothisitthisak, Jutamat ***@***.***>; Mention ***@***.***> Subject: Re: [microsoft/mssql-jdbc] After upgrade JDBC from 6.4 to 12.8, Application failed to connect its database (AWS RDS MSSQL) (Issue #2595) *** EXTERNAL EMAIL *** Hi, @Jutamat007te<https://github.com/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<https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation> Please enable FINEST level of tracing.

…

------------------------------------------------------------------------------------------------------------

[machavan]

@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?

[Jutamat007te]

from the database configuration in application Admin GUI, there are 4 fields to configure

1. JDBC field which contains follows : connection string

jdbc:sqlserver://mydatabase-mssqldb-rds.mx6ldguybgep.eu-west-2.rds.amazonaws.com;
databaseName=myDBName;portNumber=9432;instanceName=myInstanceName;
applicationName=AppAnywhere;encrypt=false

2. Username : AppAdmin_User
3. Passwod : DB password
4. Database pool : 30000

Once we save, above value will use for DB connection