Getting MS SQL Server 2005 to work with JBoss

I spend a good amount of time last night trying to get JBoss to connect with MS SQL Server 2005 as a datasource.

The first thing you should do is to download the required software. Assuming you already have JBoss and MS SQL Server 2005 (MSSQL2005) already installed, what you will need to download is the JDBC driver for MSSQL2005. After you have downloaded the JDBC driver package, extract the files within and look for the jar file that contains the JDBC implementation classes that you require. The filename is sqljdbc.jar and you should place this file in %JBOSS_HOME%\server\deploy\%SERVER_CONFIG%\lib.

Next, create a datasource configuration file for MSSQL2005. I used the sample file from %JBOSS_HOME%\doc\examples\jca\mssql-ds.xml as a base. This configuration file was originally written for MSSQL2000, so you may require some tweaking for this to work. This is what my mssql-ds.xml contains:

<datasources>
  <local-tx-datasource>
    <jndi-name>MSSQLDS</jndi-name>
    <connection-url>
     jdbc:sqlserver://localhost:4570;DatabaseName=testdb
   </connection-url>
    <driver-class>
     com.microsoft.sqlserver.jdbc.SQLServerDriver
    </driver-class>
    <user-name>sa</user-name>
    <password>***</password>
        <metadata>
            <type-mapping>MS SQLSERVER2000</type-mapping>
        </metadata>
    </local-tx-datasource>
</datasources>

Take note of the regions marked blue. (1) The protocol should be "jdbc:sqlserver" and not "jdbc:microsoft:sqlserver" used in the example file. (2) Be very careful about which port number you use. It is not necessarily true that the MSSQL2005 would use the default number 1433. For security, it appears that MSSQL2005 would use a dynamic port. You can of course disable dynamic ports and set a static port number to listen to. Please check the MSSQL2005 documentation for more information how to do this. (3) The driver class is wrong. The original configuration file sets the driver class to com.microsoft.jdbc.sqlserver.SQLServerDriver.

Please note: You may also need to run the SQL Server Surface Area Configuration utility to enable MSSQL2005 to listen over TCP/IP.

Once the configuration file is completed, copy the file to %JBOSS_HOME%\server\deploy\%SERVER_CONFIG%\deploy, which would automatically register this datasource in the JNDI registry. Thereafter, you can obtain the datasource and connection, example:

    Context ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup ("java:MSSQLDS");
    Connection conn = ds.getConnection();

I hope this information will be useful for those trying to get MSSQL2005 to work JBoss. Happy Coding!