Given password did not match password used to create the PooledConnection when there is no change in password

[Vinoth Raja]

Hi,

Environment used:
  - Tomcat 7.0.57
  - Windows
  - Java 1.7
  - MS SQL 2008.

Issue:"Given password did not match password used to create the
PooledConnection"
          There is no change in the password from the day application went
live.But it happens when there is more request.

Background on the logic :
        We have five different business application deployed on tomcat.
There is different DB instance for each business.
        5 primary DB instances and 5 secondary DB instances.
        I have configured total 10 datasource in tomcat context.xml.
        username/password it passed in getConnection(username,password ) to
get the connection object.
        I have posted the query in stackoverflow and the recommendation was
to update the library.Not sure whether to update the higher version library.


Datasource and Application Logic:

I have configured 10 datasources like the below

<Resource auth="Container"
driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"
factory="org.apache.tomcat.dbcp.dbcp.cpdsadapter.DriverAdapterCPDS"
name="jdbc/CDBCPDS"
type="org.apache.tomcat.dbcp.dbcp.cpdsadapter.DriverAdapterCPDS"
url="jdbc:sqlserver://10.200.100.8:1433;DatabaseName=SNECDB"/>
        <Resource auth="Container"
dataSourceName="java:/comp/env/jdbc/CDBCPDS"
defaultMaxActive="20" defaultMaxIdle="5" defaultMaxWait="10000"
factory="org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSourceFactory"
logAbandoned="true" name="jdbc/CDB" removeAbandoned="true"
removeAbandonedTimeout="300" testOnBorrow="true" testWhileIdle="true"
  timeBetweenEvictionRunsMillis="60000"
type="org.apache.tomcat.dbcp.dbcp.datasources.PerUserPoolDataSource"
validationQuery="select 1"/>

Here is the java application which retrieves the connection object.

try {

dataSourceName = "jdbc/".concat(GetConfigurationDetails
.get("cmm.datasource.db2." + dnis));
userName = GetConfigurationDetails.get("cmm.username.db2");
password = GetConfigurationDetails.get("cmm.password.db2");
 if (objLogger.isDebugEnabled())
objLogger.debug("[" + ucid + "] dataSourceName: "
+ dataSourceName);

objDataSource = (DataSource) envContext.lookup(dataSourceName);
try {
connection = objDataSource.getConnection(userName, password);
} catch (SQLException e) {
objLogger.error("[" + ucid + "]"
+ "SQLException in connection manager: "
+ e.getMessage());
}

} catch (NamingException e) {
objLogger.info("[" + ucid + "]"
+ "NamingException in connection manager: "
+ e.getMessage());
e.printStackTrace();
}

Background on findings:

     - There was patch to fix "when changes his/her password, the entire
connection pool must be restarted for DBCP to recognize the new password".
It is not the case  with me as there is no change in the password.
    - Stack Overflow recommendation to update the library. Not sure whether
to use the library from tomcat 7.0.59
    - I have modified the Resource to have username/password details and
this issue doesn't happen.
      <Resource name="jdbc/SHPDB" auth="Container"
              type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
              url="jdbc:sqlserver://10.200.100.8:1433;DatabaseName=SHPDB"
              username="user" password="Password" defaultMaxActive="20"
defaultMaxIdle="5" defaultMaxWait="10000"

removeAbandonedTimeout="300" testOnBorrow="false" removeAbandoned="true"
logAbandoned="true"/>

Please advice whether there is any limit on the datasources to be
configured when we set the credentials from the application.There is no
issue when I have the credentials as part of the Resource. Please advice
whether the fix is already provided or it should be handled in different
way.

Thanks
Vinoth