Jboss: Configuring Connection to MS SQL Server 2005

1. Delete JBOSS_HOME/server/default/deploy/hsqldb-ds.xml

2. Copy JBOSS_HOME/docs/examples/jca/mssql-ds.xml to JBOSS_HOME/server/default/deploy.

3. In the new copy of mssql-ds.xml, I edit the connection-url, user-name and password elements to match my local SQL db instance:
from:
<jndi-name>MSSQLDS</jndi-name>
    <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=MyDatabase</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <user-name>x</user-name>
    <password>y</password>
to:
    <jndi-name>DefaultDS</jndi-name>
    <connection-url>jdbc:sqlserver://172.16.9.198:1433;DatabaseName=TESTDATABASE</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <user-name>testuser</user-name>
    <password>testpassword</password>

notice that jdbc:microsoft:sqlserver was changed to jdbc:sqlserver

4. Delete the file JBOSS_HOME/server/default/deploy/jms/hsqldb-jdbc2-service.xml.

5. Copied JBOSS_HOME/docs/examples/jms/mssql-jdbc2-service.xml into the folder JBOSS_HOME/server/default/deploy/jms

6. In the new copy of mssql-jdbc2-service.xml, replace the string MSSQLDS with DefaultDS:
from:

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=MSSQLDS</depends>

To:

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>

8. Copy the MSSQL JDBC driver sqljdbc.jar to JBOSS_HOME/server/default/lib.

download the driver here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en

Resources:

http://community.jboss.org/thread/121618

http://docs.jboss.org/jbossas/docs/Installation_And_Getting_Started_Guide/5/html/Using_other_Databases.html#d0e3905

http://community.jboss.org/message/190717