Configuring MS SQLServer Datasource in WildFly

by

In this tutorial we will learn how to configure Microsoft SQLServer Datasource on WildFly application server

First of all, you need a JDBC Driver which is fit for your SQLServer version. You can download the JDBC Driver from Microsoft’s site: https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server

Some popular options include:

  • mssql2017: mssql-jdbc-7.4.1.jre8.jar
  • mssql2016: mssql-jdbc-6.4.0.jre8.jar

Then, from the jboss-cli.sh connect and execute the following commands to install the “com.mssql” module, then the “mssql” jdbc driver and finally the Datasource “MSSQLPool”:

module add --name=com.mssql--resources=/tmp/mssql-jdbc-7.4.1.jre8.jar --dependencies=javax.api,javax.transaction.api

/subsystem=datasources/jdbc-driver=mssql:add(driver-name="mssql",driver-module-name="com.mssql",driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver)

data-source add --jndi-name=java:/MSSQLDS --name=MSSQLPool --connection-url=jdbc:mysql://hostname:3306/DB --driver-name=mssql --user-name=user --password=password

Adapt the location of the JDBC Driver, along with your Database settings before running the above script!

Here is the module structure created by the above commands:

$ tree com

com
└── mssql
    └── main
        ├── module.xml
        └── mssql-jdbc-7.4.1.jre8.jar

The following file, is module.xml:

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

    <resources>
        <resource-root path="mssql-jdbc-7.4.1.jre8.jar"/>
    </resources>

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

Finally, here is the updated configuration of the application server:

<datasource jndi-name="java:/MSSQLDS" pool-name="MSSQLPool">
	<connection-url>jdbc:mysql://hostname:3306/DB</connection-url>
	<driver>mssql</driver>
	<security>
	    <user-name>user</user-name>
	    <password>password</password>
	</security>
</datasource>
<drivers>
    <driver name="mssql" module="com.mssql">
        <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    </driver>
</drivers>