[JBoss-user] Setting up an MSSQL datasource on JBoss3

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Setting up an MSSQL datasource on JBoss3Hi.

I have run into a brick wall trying to setup MS SQLSever 2000 as my DB for
JBoss3.0. I cant see what l am missing and am looking for help from people
who have MS SQLServer 2000 working with JBoss 3.0.

I have JBoss 3.0 and MS SQLServer 2000 running on my workstation. Im running
Java 1.3.0_02.

Where should the Microsoft SQLServer JDBC jars (mssqlserver.jar, msutil.jar
and msbase.jar) go? (There seem to be various options. JBOSS_HOME\lib,
JBOSS_HOME\lib\ext, JBOSS_HOME\server\all\lib. I have been working on
JBOSS_HOME\server\all\lib since the hsql jars seem to be there.)

I have no problems setting up ths hssql server so I have been comparing
hsqldb-service.xml with mssql-service.xml.

(Note: I do not have both files in the JBOSS_HOME\server\all\deploy
directory - its either hsqldb-service.xml or mssql-service.xml. When l start
JBoss (run -c all) with hsqldb-service.xml l have a DefaultDS entry in the
JNDI java: namespace. When l start JBoss with mssql-service.xml l do not
have a DefaultDS entry.)

Here is my mssql-service.xml file (without the comments)

--- mssql-service.xml
(start) ----------------------------------------------------
<server>
   <mbean
code="org.jboss.resource.connectionmanager.LocalTxConnectionManager"
name="jboss.jca:service=LocalTxCM,name=MSSQLDS">

    <depends>jboss.jca:service=RARDeployer</depends>
    <depends optional-attribute-name="ManagedConnectionFactoryName">

      <!--embedded mbean-->
      <mbean code="org.jboss.resource.connectionmanager.RARDeployment"
name="jboss.jca:service=LocalTxDS,name=MSSQLDS">

        <attribute name="JndiName">DefaultDS</attribute>

        <attribute name="ManagedConnectionFactoryProperties">
          <properties>
            <config-property>
              <config-property-name>ConnectionURL</config-property-name>
              <config-property-type>java.lang.String</config-property-type>

<config-property-value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseNam
e=dbLeave;SelectMethod=cursor</config-property-value>
            </config-property>
            <config-property>
              <config-property-name>DriverClass</config-property-name>
              <config-property-type>java.lang.String</config-property-type>

<config-property-value>com.microsoft.jdbc.sqlserver.SQLServerDriver</config-
property-value>
            </config-property>
            <config-property>
              <config-property-name>UserName</config-property-name>
              <config-property-type>java.lang.String</config-property-type>
              <config-property-value>sa</config-property-value>
            </config-property>
            <config-property>
              <config-property-name>Password</config-property-name>
              <config-property-type>java.lang.String</config-property-type>
              <config-property-value>secret</config-property-value>
            </config-property>
          </properties>
        </attribute>

        <depends
optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,n
ame=Minerva JDBC LocalTransaction ResourceAdapter</depends>
      </mbean>
    </depends>
    <depends optional-attribute-name="ManagedConnectionPool">

      <!--embedded mbean-->
      <mbean
code="org.jboss.resource.connectionmanager.JBossManagedConnectionPool"
name="jboss.jca:service=LocalTxPool,name=MSSQLDS">
        <attribute name="MinSize">0</attribute>
        <attribute name="MaxSize">50</attribute>
        <attribute name="BlockingTimeoutMillis">5000</attribute>
        <attribute name="IdleTimeoutMinutes">15</attribute>

        <attribute name="Criteria">ByContainer</attribute>
      </mbean>
    </depends>

    <depends
optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedCo
nnectionManager</depends>
    <!-- Include a login module configuration named MSSQLDbRealm.
         Update your login-conf.xml, here is an example for a
         ConfiguredIdentityLoginModule:
    <application-policy name = "MSSQLDbRealm">
       <authentication>
          <login-module code =
"org.jboss.resource.security.ConfiguredIdentityLoginModule" flag =
"required">
             <module-option name = "principal">sa</module-option>
             <module-option name = "userName">sa</module-option>
             <module-option name = "password">secret</module-option>
             <module-option name =
"managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MSSQLDS</mod
ule-option>
          </login-module>
       </authentication>
    </application-policy>
    <!--<attribute name="SecurityDomainJndiName">MSSQLDbRealm</attribute>-->

    <depends
optional-attribute-name="JaasSecurityManagerService">jboss.security:name=Jaa
sSecurityManager</depends>

    <attribute
name="TransactionManager">java:/TransactionManager</attribute>

  </mbean>
</server>
--- mssql-service.xml
(end) ----------------------------------------------------

Here is what l added to login-config.xml

--- login-config.xml
(start) ----------------------------------------------------
...
<application-policy name = "MSSQLDbRealm">
           <authentication>
              <login-module code =
"org.jboss.resource.security.ConfiguredIdentityLoginModule"
                 flag = "required">
                 <module-option name = "principal">sa</module-option>
                 <module-option name = "userName">sa</module-option>
                 <module-option name = "password">secret</module-option>
                 <module-option name =
"managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MSSQLDS</mod
ule-option>
              </login-module>
           </authentication>
    </application-policy>
...
--- login-config.xml
(end) ----------------------------------------------------

As far as l can tell this is all pretty much the same as the hsql setup in
hsqldb-service.xml and login-config.xml.

Here is the section of server.log reporting on the deployment of
mssql-service.xml.

---server.log (start) ----------------------------------------------------
...
2002-07-05 16:13:39,466 INFO  [org.jboss.deployment.MainDeployer] Starting
deployment of package:
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,466 DEBUG [org.jboss.deployment.MainDeployer] Starting
deployment (init step) of package at:
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,466 DEBUG [org.jboss.mx.loading.UnifiedClassLoader] New
jmx UCL with url
file:/C:/install/JBoss/jboss-3.0.0/server/all/tmp/deploy/server/all/deploy/m
ssql-service.xml/68.mssql-service.xml
2002-07-05 16:13:39,482 DEBUG [org.jboss.mx.loading.UnifiedLoaderRepository]
UnifiedLoaderRepository adding
org.jboss.mx.loading.UnifiedClassLoader@6db33c{
url=file:/C:/install/JBoss/jboss-3.0.0/server/all/tmp/deploy/server/all/depl
oy/mssql-service.xml/68.mssql-service.xml }
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.MainDeployer] using
deployer org.jboss.deployment.SARDeployer@6e70c7
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.SARDeployer] about to
copy 0 local directories
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.MainDeployer] found 0
subpackages of
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.MainDeployer] Watching
new file:
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.MainDeployer] create
step for deployment
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.SARDeployer] Deploying
SAR, create step: url
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,482 DEBUG [org.jboss.deployment.SARDeployer] Registering
service UCL=jmx.loading:UCL=7189308
2002-07-05 16:13:39,482 DEBUG [org.jboss.mx.loading.UnifiedLoaderRepository]
UnifiedLoaderRepository skipping duplicate
org.jboss.mx.loading.UnifiedClassLoader@6db33c{
url=file:/C:/install/JBoss/jboss-3.0.0/server/all/tmp/deploy/server/all/depl
oy/mssql-service.xml/68.mssql-service.xml }
2002-07-05 16:13:39,482 DEBUG [org.jboss.system.ServiceCreator] About to
create bean: jboss.jca:service=LocalTxCM,name=MSSQLDS
2002-07-05 16:13:39,482 DEBUG [org.jboss.system.ServiceCreator] code:
org.jboss.resource.connectionmanager.LocalTxConnectionManager
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceCreator] Created
bean: jboss.jca:service=LocalTxCM,name=MSSQLDS
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxCM,name=MSSQLDS depends on
jboss.jca:service=RARDeployer
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceConfigurator]
considering <anonymous> with object name jboss.jca:service=RARDeployer
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceCreator] About to
create bean: jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceCreator] code:
org.jboss.resource.connectionmanager.RARDeployment
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceCreator] Created
bean: jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,529 DEBUG [org.jboss.system.ServiceConfigurator]
JndiName set to DefaultDS in jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
ManagedConnectionFactoryProperties set to <properties>
            <config-property>
              <config-property-name>ConnectionURL</config-property-name>
              <config-property-type>java.lang.String</config-property-type>

<config-property-value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseNam
e=dbLeave;SelectMethod=cursor</config-property-value>
            </config-property>
            <config-property>
              <config-property-name>DriverClass</config-property-name>
              <config-property-type>java.lang.String</config-property-type>

<config-property-value>com.microsoft.jdbc.sqlserver.SQLServerDriver</config-
property-value>
            </config-property>
            <config-property>
              <config-property-name>UserName</config-property-name>
              <config-property-type>java.lang.String</config-property-type>
              <config-property-value>sa</config-property-value>
            </config-property>
            <config-property>
              <config-property-name>Password</config-property-name>
              <config-property-type>java.lang.String</config-property-type>
              <config-property-value>secret</config-property-value>
            </config-property>
          </properties> in jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxDS,name=MSSQLDS depends on
jboss.jca:service=RARDeployment,name=Minerva JDBC LocalTransaction
ResourceAdapter
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
considering OldRarDeployment with object name
jboss.jca:service=RARDeployment,name=Minerva JDBC LocalTransaction
ResourceAdapter
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxCM,name=MSSQLDS depends on
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
considering ManagedConnectionFactoryName with object name
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceCreator] About to
create bean: jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceCreator] code:
org.jboss.resource.connectionmanager.JBossManagedConnectionPool
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceCreator] Created
bean: jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator] MinSize
set to 0 in jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator] MaxSize
set to 50 in jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
BlockingTimeoutMillis set to 5000 in
jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
IdleTimeoutMinutes set to 15 in jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
Criteria set to ByContainer in jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxCM,name=MSSQLDS depends on
jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
considering ManagedConnectionPool with object name
jboss.jca:service=LocalTxPool,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxCM,name=MSSQLDS depends on
jboss.jca:service=CachedConnectionManager
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
considering CachedConnectionManager with object name
jboss.jca:service=CachedConnectionManager
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] recording
that jboss.jca:service=LocalTxCM,name=MSSQLDS depends on
jboss.security:name=JaasSecurityManager
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
considering JaasSecurityManagerService with object name
jboss.security:name=JaasSecurityManager
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceConfigurator]
TransactionManager set to java:/TransactionManager in
jboss.jca:service=LocalTxCM,name=MSSQLDS
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] waiting
in create jboss.jca:service=LocalTxCM,name=MSSQLDS waiting on
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,545 WARN  [org.jboss.system.ServiceController]
jboss.jca:service=LocalTxDS,name=MSSQLDS does not implement any Service
methods
2002-07-05 16:13:39,545 DEBUG [org.jboss.system.ServiceController] waiting
in create jboss.jca:service=LocalTxDS,name=MSSQLDS waiting on
jboss.jca:service=RARDeployment,name=Minerva JDBC LocalTransaction
ResourceAdapter
2002-07-05 16:13:39,545 INFO
[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Creating
2002-07-05 16:13:39,560 INFO
[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Created
2002-07-05 16:13:39,560 DEBUG [org.jboss.system.ServiceController] waiting
in create jboss.jca:service=LocalTxCM,name=MSSQLDS waiting on
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,560 DEBUG [org.jboss.deployment.MainDeployer] Done with
create step of deploying mssql-service.xml
2002-07-05 16:13:39,560 DEBUG [org.jboss.deployment.MainDeployer] start step
for deployment
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,560 DEBUG [org.jboss.deployment.SARDeployer] Deploying
SAR, start step: url
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,560 DEBUG [org.jboss.system.ServiceController] waiting
in start jboss.jca:service=LocalTxCM,name=MSSQLDS on
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,560 DEBUG [org.jboss.system.ServiceController] waiting
in start jboss.jca:service=LocalTxDS,name=MSSQLDS on
jboss.jca:service=RARDeployment,name=Minerva JDBC LocalTransaction
ResourceAdapter
2002-07-05 16:13:39,560 INFO
[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Starting
2002-07-05 16:13:39,576 INFO
[org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Started
2002-07-05 16:13:39,576 DEBUG [org.jboss.system.ServiceController] waiting
in start jboss.jca:service=LocalTxCM,name=MSSQLDS on
jboss.jca:service=LocalTxDS,name=MSSQLDS
2002-07-05 16:13:39,576 DEBUG [org.jboss.deployment.MainDeployer] Final
(start) deployment step successfully completed on package: mssql-service.xml
2002-07-05 16:13:39,576 INFO  [org.jboss.deployment.MainDeployer]
Successfully completed deployment of package:
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
2002-07-05 16:13:39,591 DEBUG
[org.jboss.deployment.scanner.URLDeploymentScanner] Watch URL for:
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml ->
file:/C:/install/JBoss/jboss-3.0.0/server/all/deploy/mssql-service.xml
...
--- server.log (end) ----------------------------------------------------

I dont know what a successful deploy looks like but there are no errors
reported and one log entry states "JndiName set to DefaultDS in
jboss.jca:service=LocalTxDS,name=MSSQLDS!" So l would expect to find
java:DefaultDS in JNDI. I have an EJB that lists the NameClassPairs returned
on the lookup of a Context. When l use the hsqldb-service.xml l see
DefaultDS in java:. When l use mssql-service.xml there is no DefaultDS
anywhere in the tree.

Thanks for reading this far. Here are the questions l would like answered.

Where should the MS SQLServer JDBC jars go?
Is the mssql-service.xml file correct?
Is the entry in login-config.xml correct?
Do l need the entry in login-config.xml in the first place? (I have only put
something there because there were matching entries in hsqldb-service.xml
and login-config.xml)
If everything is in the right place and if the xml files have no errors,
what havent l done?

Thanks in advance.