setting up MS SQL Server as a data source with JBoss

[Tony Evans]

Hi,
I am having a few problems trying to set up MS SQL Server as a data source with JBoss.
I am following the instructions in the Using MS SQL Server with JBoss documentation.
As I have j2sdk1.4.0_03 installed I do not have to download a JDBC driver. What I have is the dll file C:\j2sdk1.4.0_03\jre\bin\JdbcOdbc.dll which I guess links to my jdbc drivers, so I need to add JdbcOdbc.dll to my JBOSS file which deals with JDBC drivers.
The problem is that I do not know what this file is called. According to the docs it is called jboss.jcml but that file does not exist.
I am using version jboss-3.2.0RC2_tomcat.4.1.18.
I would be grateful if anyone can tell me what the file is know called.
Thanks Tony

[Tony Evans]

Further investigation has revealed the following files in the examples directory:
mssql-ds.xml
mssql-service.xml
mssql-xa-ds.xml
mssql-xa-service.xml
I guess the way forward is to copy these into the JBOSS server directory and modify them for my use.
I am not sure where they should be copied to, I guess it should be the

C:\jboss-3.2.0RC2_tomcat.4.1.18\server\default\conf directory, as this contains all the xml files that are needed at start-up.
Am I correct in this thanks for any help.
Cheers Tony

[Darryl A. J. Staflund]

Hi there,
The only configuration file you'll need to work with (I believe) is mssql-ds.xml. Just modify it and place it in the deploy directory of your default server instance. For instance, if you're using 'default', place the file in '<jboss>/server/default/deploy/'.
The mssql-server.xml will work as well, though I think it's included for 3.0.x backward compatbility. As for 'xa-ds.xml' and 'xa-service.xml', I haven't used them so I can't speak about them :-)
BTW, the '-ds.xml' files are new to JBoss 3.2 so there's not a lot of documentation out on them that I have been able to scope out. That's why I speak conditionally about them.
I can tell you that I've recently switched from 'postgresql-service.xml' to 'postgresql-ds.xml' in my own code and have suffered so serious effects :-)
Cheers,
Darryl

[Darryl A. J. Staflund]

BTW, you'll also want to place the JDBC driver in the deploy directory as well. If you're working with a DDL, I think you'll have to use a JDBC-ODBC bridge to work with the database and, I have to apologize, I've never used one of these things so I can't help you configure it.
I do know that there are JDBC drivers for MSSQL 2000, however, so look around for them. I believe Microsoft has one available for download.
Darryl

[Tony Evans]

Hi Darryl
Thanks very much your mail was a great source of help, and has saved me a lot of time.
What I did was I modified the mssql-ds.xml , downloaded the mssqlserver.jar and put both in my
C:\jboss-3.2.0RC2_tomcat.4.1.18\server\default\deploy directory.
I still have a problem with the following warning message :
10:25:00,453 WARN [LocalTxConnectionManager] Please change your datasource setup to use TransactionManagerService with object name jboss.tm:service=Transaction ManagerService
10:25:00,453 WARN [LocalTxConnectionManager] instead of TransactionManager with value java:/TransactionManager
But hopfully will be able to solve it.
Thanks again for your help.
Tony

[Craig Demyanovich]

Tony,
The messages about the transaction manager service can supposedly be ignored, according to posts on the jboss-user mailing list. Changes have been made for RC3 that should address this problem. In fact, RC3 became available yesterday. Download it and give it a try. Do you have any other questions about data source configuration?
Craig

[Bernie Ferguson]

I recently tried the same thing using JBOSS 3.2 and Microsoft SQL Server. I originally copied only the mssqlserver.jar into the deploy directory and received a class not found error for Microsoft's JDBC base class. I went back and copied the msbase.jar and the msutil.jar to the deploy directory and everything works fine. No warnings either.

[Prashant Danda]

Is it possible to perform connection pooling for this datastore in JBoss.
Any link or code sample in the databasename-ds.xml file would be helpful, the connection pool should it be specified in this file or where to implement it ?
Thanks
-Prashant.

[A Oliveira]

Hi! I was wondering how you can set up MSSQL as the default DBMS? I tried by changing the login-config.xml file and adding a new <application-policy> element but it didn't work.
Is this even necessary?

[suvasis mukherjee]

I followed the following step to get MS-SQL to work:
how to configure MS SQL server in jboss?
1. delete the hsql*-service.xml file in the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\deploy
directory.
2. copy the mssql-service.xml file from the C:\jboss\jboss-3.0.6_tomcat-4.1.18\docs\examples\jca directory
into C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\deploy
3. change the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\config
a) standardjaws.xml
<jaws>
<datasource>java:/MSSQLDS</datasource>
<type-mapping>MS SQLSERVER2000</type-mapping>
<debug>false</debug>

b) standardjbosscmp-jdbc.xml files.
<defaults>
<datasource>java:/MSSQLDS</datasource>
<datasource-mapping>MS SQLSERVER2000</datasource-mapping>

4. in the mssql-service.xml following changes:
<attribute name="ManagedConnectionFactoryProperties">
<properties>
<config-property name="ConnectionURL" type="java.lang.String">jdbc:microsoft:sqlserver://68.165.1.253:1450;DatabaseName=CABIN</config-property>
<config-property name="DriverClass" type="java.lang.String">com.microsoft.jdbc.sqlserver.SQLServerDriver</config-property>
<!--set these only if you want only default logins, not through JAAS-->
<config-property name="UserName" type="java.lang.String">mina</config-property>
<config-property name="Password" type="java.lang.String">sm4321</config-property>
</properties>
</attribute>
......
and also
<depends optional-attribute-name="ManagedConnectionPool">
<!--embedded mbean-->
<mbean code="org.jboss.resource.connectionmanager.JBossManagedConnectionPool" name="jboss.jca:service=XaTxPool,name=MSSQLDS">

5. in the C:\jboss\jboss-3.0.6_tomcat-4.1.18\server\default\config dir
change the login-config.xml file
<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">user</module-option>
<module-option name = "password">passwd</module-option>
<module-option name = "managedConnectionFactoryName">jboss.jca:service=XaTxCM,name=MSSQLDS</module-option>
</login-module>
</authentication>
</application-policy>

[Nguyen Quoc Nguyen]

thank alot