Driver version or jar name

6.2.1.jre8

SQL Server version

SQL Server 2012

Client operating system

CentOS 7

Java/JVM version

java version "1.8.0_91"

Table schema

N/A

Problem description

In our setup, SQL Server will issue TCP keepalives on idle connections to eagerly detect potentially bad connections. Unfortunately this behavior sometimes results in a good connection be flagged as bad if the keepalives do not reach the other host. We've seen instances where SQL Server will terminate the connection, but our application hosts think the connections are still good. (Presumably the keepalives are getting dropped.) When we then attempt to use the connection it hangs. SQL Server will silently drop the packets without responding with a TCP reset. Eventually, we reach the TCP packet retransmitted limit (aka tcp_retries2).

Attempting to set the Statement#setQueryTimeout only works if the server responds with data. If the server is not responding, then the timeout is essentially ignored.

Expected behavior and actual behavior

I believe the timeout should trigger the Statement to terminate immediately, preferably with an exception of some kind.

Currently the code will hang until the socket times out via the retransmitted limit being reached.

Repro code

Here is a simple Java class to execute:

  public static void main(String[] args) throws Exception {
    DriverManager.class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    Connection connection = DriverManager.getConnection("jdbc:sqlserver://localhost;databaseName=mydatabase", "sa", "Password");
    System.out.println("Cause the TCP connection to be terminated from SQL Server side then hit enter.");
    System.in.read();
    Statement statement = connection.createStatement();
    statement.setQueryTimeout(1);

    try {
      statement.execute("WAITFOR DELAY '00:00:3'; SELECT 1");
    } catch (SQLException e) {
      e.printStackTrace();
    }
    System.out.println("Complete");
  }

Here's how I terminate the TCP connection from the app host. Basically don't respond to the TCP keepalives:

# Get the port.
ss -tn | grep 1433

# Drop the packets.
sudo iptables -I INPUT 1 -p tcp --dport <paste-port-number-here> -j DROP

# Wait for a bit...
# Remove the iptables rule once the connection is terminated by SQL Server.
sudo iptables -D INPUT 1

To determine when SQL Server terminates the connection, I use this query:

select * from sys.dm_exec_sessions
where host_name = 'localhost'