[Microsoft][SQLServer 2000 Driver for JDBC]Login has timed out

Question

There is a legacy web application built and running on java 6 (1.6.0_21) deployed on Apache tomcat 6.0.29 on a CentOS virtual machine which is connecting to two databases - a MySQL database (local) and a SQL Server 2000 database (remote). Everything was fine for years until just one day, a certain login to the SQL Server is getting a login has timed out error. Previously, this was intermittent since there were really times that there is a timeout when logging in but now it has become "permanent" with no changes both on the web application and the remote database server.

The web application calls several stored procedures on the SQL Server using these sets of codes:

Connection con = null;
PreparedStatement stmt = null;
String query = "exec StoredProcedureGoesHere ?,?";
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
DriverManager.setLoginTimeout(60);

con = DriverManager.getConnection(
          "jdbc:microsoft:sqlserver://host:port;databaseName=myDB;",
          "user", "pass");

if (con != null) {
    stmt = con.prepareStatement(query);
    ...set parameters of stmt
    stmt.close();
    con.close();
}

Let's say SP1 (stored procedure 1) is being called and executing successfully. However, for the connection part of SP2, I am get an error

[Microsoft][SQLServer 2000 Driver for JDBC]Login has timed out.

Now, when SP1 is again called, I will not receive any errors but there are no changes in SQL Server which was expected of SP1. I need to restart tomcat just to execute SP1 as expected. If SP2 runs again - I get the same error, the cycle continues and I need to restart tomcat again.

Here are what I've tried so far:

1. Created a test program with the same code as the web application. Copied it in the server, ran it, and both SP1 and SP2 are successful. Put a loop to run it multiple times with no issues. Thus confirming it is not a permission, firewall or network issue (although correct me if I am wrong)

2. The same test program was run in my local machine connected to the same network as web application server and SQL Server database server. Everything was okay so still not a network or permission issue.

3. Cleared the javaws cache (javaws -uninstall), deleted the contents of the tomcat/work directory and restarted the server. The effect was the same when restarting the server: first run of SP1 is successful - SP2 timeout - SP1 nothing happened.

4. Un-deployed - deployed the application war file to reload everything.

5. Restarted the server of the SQL Server

6. Extended the login timeout, query timeout and remote login timeout of the SQL server to 5 minutes even if previously, SP2 will complete in less than 10 seconds. No effect.

7. Recompiled all the stored procedures from the SQL Server

8. Deleted all the stored procedures and created them again

Of course, I'm still waiting for feedback if there were changes on the physical network connection, router upgrade/downgrade, etc. when the problem started becoming "permanent" if ever it has any impact. But as of the moment, I need inputs from you gurus for something else to try.

Answer 1

I have "patched" this issue and will tell you what happened (in case someone with the same problem comes across this question and gets frustrated because it was unanswered). I never really got to the root of the problem, especially since I was about to leave the company at the time this was asked. But what I did was to manually change the connection string in the code to include an extended timeout for query and login:

con = DriverManager.getConnection(
      "jdbc:microsoft:sqlserver://host:port;databaseName=myDB;queryTimeout=300;loginTimeout=300",
      "user", "pass");

Even though I'm sure that nothing changed ever since, and that it just started to "permanently" timeout, I just assumed it to be a server ageing concern (if there is such a thing) and probably also a lot of memory leaks since as I mentioned, I am running the exact same code on a test program but it was successful. But after extending the timeout, the problem did not occur again (up until I left the company.)