How to set a datasource connection to  MSSQL Server

Version 8

    This is for MSSQL 2008 but I believe this will work at least for 2005 as it is based on the driver's capabilities\

     

    • Install the driver sqljdbc4.jar in [SERVER]\modules\com\microsoft\main
    • add under the same directory module.xml with content

    {code:xml}

    <module xmlns="urn:jboss:module:1.1" name="com.microsoft">

      <resources>

        <resource-root path="sqljdbc4.jar"/>

      </resources>

      <dependencies>

        <module name="javax.api"/>

        <module name="javax.transaction.api"/>

      </dependencies>

    </module>

    {code}

     

    • add to the <subsystem xmlns="urn:jboss:domain:datasources:1.1"> as follows

    {code:xml}<datasources>

                    <datasource jndi-name="java:/MSSQLDS" pool-name="MSSQLDS">

                        <connection-url>jdbc:sqlserver://SERVER_NAME:SERVER_PORT;databaseName=DB_NAME</connection-url>

                        <driver>sqlserver2008</driver>

                        <security>

                          <user-name>USERNAME</user-name>

                          <password>PASSWORD</password>

                        </security>

                        <pool>

                            <min-pool-size>5</min-pool-size>

                            <max-pool-size>50</max-pool-size>

                            <prefill>false</prefill>

                            <use-strict-min>false</use-strict-min>

                            <flush-strategy>FailingConnectionOnly</flush-strategy>

                        </pool>                   

                        <validation>

                          <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>

                        </validation>

                      </datasource>                 

                      <drivers>                 

                        <driver name="sqlserver2008" module="com.microsoft">

                            <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

                        </driver>                   

                     </drivers>

                </datasources>

    {code}