All
Currently deploying JBOSS 4.0.1 and 4.0.1 SP1 on SMP test rigs and testing for thruput.
We are using the DatabaseServerLoginModule to authenticate users (login-config.xml snippet below)

DB engine MSSQL 2000 sp3a. JDBC driver JTDS 0.9.1 (although tested with MSSQL jdbc sp3 and got same results)

A couple of issues really:
1. We seem to lapse into auto commit mode for the duration of the login thusly:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET IMPLICIT_TRANSACTIONS OFF
SET QUOTED_IDENTIFIER ON
SET TEXTSIZE 2147483647
select passwd from principal where username= '000000017709'
select Roles, 'Roles' from principalrole where username= '000000017709'

No commit is ever issued.
In any case I would expect this query to have a commit from a pooled implicit connection since we are using implicit transactions.....
The example above shows us not using implicit transactions of course. Unfortunately, I cannot seem to find anywhere to undo this behaviour so we do use implicit transactions.
All other actions handled within the aplication are as per std ie implicit transactions and always a transaction commit once unit of works finished.

2. We always seem to use the same SPID on SQL Server. This behaviour is only apparent on login. If the test users go and do other actions in the application, then all is pooled as expected. Unfortunately since ALL logins seem to use the same spid, we see horrendous queueing beginning to build up. The test im doing is not too heavy with 100 concurrent connections logging in/out with 250ms delay. Im using 3 playback clients to test the load. Again using these clients all 'post' login activities select pooled conections spids as I would expect

Im obviously doing something wrong but cannot seem to find out what....

<application-policy name = "edrms">

<login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required" >
<module-option name="dsJndiName">java:/EDRMS-DS</module-option>
<module-option name="principalsQuery">select passwd from principal where username=?</module-option>
<module-option name="rolesQuery">select Roles, 'Roles' from principalrole where username=?</module-option>
</login-module>

</application-policy>