Not returning correct password
I am using form authentication to authenticate users to a web application. This works with the UsersRolesLoginModule and property files but with the DatabaseServerLoginModule I get an invalid login password. I am using JBoss version "jboss-3.0.3_tomcat-4.1.12" with a MS SQL Server 2000 database (and also tried MySQL). I use a simple select statement:
SELECT secret FROM arch.dbo.principles WHERE loginid=?
If I change the table name, the database access user name or the application user name to an invalid name an error occurs so it is communicating with MSSQL and finding my user.
I have now inserted P6Spy and the result set returned appears to be the same as the SQL query:
1035886869528|-1||info||P6Spy successfully registered driver com.microsoft.jdbc.sqlserver.SQLServerDriver
1035886870519|80|0|statement|SELECT secret FROM arch.dbo.principles WHERE loginid=?|SELECT secret FROM arch.dbo.principles WHERE loginid='andy'
1035886870519|0|0|result|SELECT secret FROM arch.dbo.principles WHERE loginid=?|SELECT secret FROM arch.dbo.principles WHERE loginid='andy'
Here are the associated entries in my config files:
***************** Login Config ***********************
<application-policy name = "ARCHDomain">
<login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
<!--<module-option name="principal">jboss</module-option>-->
<!-- sql queries -->
<module-option name="principalsQuery">SELECT secret FROM arch.dbo.principles WHERE loginid=?</module-option>
<module-option name="rolesQuery">SELECT rolename, groupname FROM ((rolegroups INNER JOIN rolegroupmap ON rolegroups.id = rolegroupmap.groupid) INNER JOIN roles ON rolegroupmap.roleid = roles.id) INNER JOIN (principles INNER JOIN principlerolemap ON principles.id = principlerolemap.principalid) ON roles.id = principlerolemap.roleid WHERE (((principlerolemap.principalid)=principles.id) AND ((roles.id)=principlerolemap.roleid) AND ((rolegroupmap.roleid)=roles.id) AND ((rolegroups.id)=rolegroupmap.groupid) AND ((principles.loginid)=?))</module-option>
<module-option name="dsJndiName">java:/ARCHDS</module-option>
<!--<module-option name="hashAlgorithm">MD5</module-option>-->
<!--<module-option name="unauthenticatedIdentity">nobody</module-option>-->
<module-option name="managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=ARCHDS</module-option>
</login-module>
</application-policy>
********************* MSSQL Service ******************
<?xml version="1.0" encoding="UTF-8"?>
<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->
<!-- ======================================================================-->
<!-- New ConnectionManager setup for Microsoft SQL Server 2000 driver -->
<!-- You may download the latest Microsoft JDBC driver from *Microsoft* -->
<!-- http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/779/msdncompositedoc.xml&frame=true -->
<!-- Build jmx-api (build/build.sh all) and view for config documentation -->
<!-- ===================================================================== -->
<!--********************************************ARCHDB*************************************************-->
<!--NOTE: the application-policy name attribute must match SecurityDomainJndiName, and the
module-option name = "managedConnectionFactoryName"
must match the object name of the ConnectionManager you are configuring here.
-->
<!--ARCHDomain-->
<depends optional-attribute-name="ManagedConnectionFactoryName">
ARCHDS
<config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=arch</config-property>
<config-property name="DriverClass" type="java.lang.String">com.p6spy.engine.spy.P6SpyDriver</config-property>
<!--
<config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
-->
<config-property name="UserName" type="java.lang.String">jboss</config-property>
<config-property name="Password" type="java.lang.String">mssql</config-property>
<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper
<depends optional-attribute-name="ManagedConnectionPool">
0
50
5000
15
ByContainer
<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager
<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager
java:/TransactionManager
jboss.jca:service=RARDeployer
************************ Web XML ************************
<login-config>
<auth-method>FORM</auth-method>
<realm-name>ARCHDomain</realm-name>
<form-login-config>
<form-login-page>/public/login/login.jsp</form-login-page>
<form-error-page>/public/login/login_error.html</form-error-page>
</form-login-config>
</login-config>
********************** JBoss-Web *********************
<?xml version="1.0" encoding="UTF-8"?>
<jboss-web>
<security-domain>java:/jaas/ARCHDomain</security-domain>
</jboss-web>
Any help greatly appreciated.