2 Replies Latest reply on Apr 4, 2005 3:03 AM by Tony Ditchfield

    Anomoly in DatabaseServerLoginModule

    Tony Ditchfield Newbie

      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>

        • 1. Re: Anomoly in DatabaseServerLoginModule
          Scott Stark Master

          And what is the java:/EDRMS-DS configuration?

          • 2. Re: Anomoly in DatabaseServerLoginModule
            Tony Ditchfield Newbie

            Contents as below of EDRMS-DS as requested

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

            <local-tx-datasource>
            <jndi-name>EDRMS-DS</jndi-name>
            <connection-url>jdbc:jtds:sqlserver://192.168.200.14:1433/edrmsstaging;prepareSQL=0;SendStringParametersAsUnicode=False;appName=MainUserConnection</connection-url>
            <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
            <user-name>edrms</user-name>
            *****


            <!-- sql to call when connection is created
            <new-connection-sql>nothing</new-connection-sql> -->

            <!-- sql to call on an existing pooled connection when it is obtained from pool
            <check-valid-connection-sql>nothing</check-valid-connection-sql>-->
            <min-pool-size>5</min-pool-size>
            <max-pool-size>100</max-pool-size>
            <blocking-timeout-millis>30000</blocking-timeout-millis>
            <idle-timeout-minutes>5</idle-timeout-minutes>


            </local-tx-datasource>