JBoss 5/6 Encrypting Data Source Passwords - Timeout

[oraclerob]

JBoss Gurus,

The following has us stumped.

We have successfully implemented <security-domain>EncryptDBPassword</security-domain> and it works great until the Tomcat times the session out (we think) using the <session-timeout>5</session-timeout> in the web.xml at the jbossweb.sar level. 

Once this happens, the whole connection pool is blown and we get the following in the stack trace:

[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null: org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES))

It fails on the following line in our code:

      try {

          InitialContext ic;

          ic = new InitialContext();

          try {

              ds = (DataSource)ic.lookup("java:/jdbc/MySQLDS");

             connection = ds.getConnection();

              ........

              ........

Now before someone tells me that we have our password wrong, we dont - as it all works great until the connections are idle. It seems to me that the EncryptDBPassword is only being picked up at ConnectionPool initialization.

I'm finding it hard to believe we are the only users ever to encounter this and it is the same on 5.1 and 6.1 and I think it is something we are not doing right!

One more bit of info is that we use the custom login auth:

WebAuthentication webAuthentication = new WebAuthentication();

Which requires access to the DB on Tomcat session timeout to validate the user.

Any help appreciated,

Cheers

Rob

[jaikiran]

Post the contents of your -ds.xml.

[oraclerob]

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml,v 1.3.2.1 2004/12/01 11:46:00 schrouf Exp $ -->

<!--  Datasource config for MySQL using 3.0.9 available from:

http://www.mysql.com/downloads/api-jdbc-stable.html

-->

<datasources>

  <local-tx-datasource>

    <jndi-name>jdbc/MySQLDS</jndi-name>

    <connection-url>jdbc:mysql://localhost/ipayby</connection-url>

    <driver-class>com.mysql.jdbc.Driver</driver-class>

    <min-pool-size>5</min-pool-size>

    <max-pool-size>50</max-pool-size>

    <idle-timeout-minutes>0</idle-timeout-minutes>

     <security-domain>EncryptDBPassword</security-domain>

        <metadata>

            <type-mapping>MySQL</type-mapping>

        </metadata>

  </local-tx-datasource>

</datasources>

[oraclerob]

Here is my login-config. Password obscured. Still awaiting some help as I can't believe this is a bug - as it is so fundemental. I think we are getting an "access denied" message because the password is not being decrypted properly when the session times out and has to log in again,  so a standard mysql message is thrown.

<application-policy name="EncryptDBPassword">

        <authentication>

            <login-module code="org.jboss.resource.security.SecureIdentityLoginModule" flag="required">

                <module-option name="username">root</module-option>

                <module-option name="password">xxxxxxxxxxxxxxxxx</module-option>

                <module-option name="managedConnectionFactoryName">jboss.jca:name=jdbc/MySQLDS,service=LocalTxCM</module-option>

            </login-module>

        </authentication>

    </application-policy>