How to configure Microsoft SQL Server in EAP/Wildfly

Wednesday, December 11, 2019

9:19 AM

1. Download the appropriate jdbc driver from https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
2. Extract the driver package to the modules folder under EAP
1. Here is from where my EAP is running C:\Users\admin\EAP-7.0.0\
2. Create a folder named "sqlserver" under C:\Users\admin\EAP-7.0.0\modules\system\layers\base\com\microsoft
3. Copy the extracted driver jar file (for example mssql-jdbc-6.4.0.jre8.jar) to C:\Users\admin\EAP-7.0.0\modules\system\layers\base\com\microsoft\main
3. Now create a modules.xml file in  C:\Users\admin\EAP-7.0.0\modules\system\layers\base\com\microsoft\main with the following settings

<?xml version="1.0" encoding="UTF-8"?>

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

    <resources>

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

    </resources>

    <dependencies>

        <module name="javax.api"/>

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

    </dependencies>

</module>

4. Now, you need to tell EAP/Wildfly about the new driver and you can do that by modifying the configuration xml.
1. By default the configuration xml is "standalone.xml" which you can find in the folder C:\Users\admin\EAP-7.0.0\standalone\configuration
2. Go to "drivers" node under "<subsystem xmlns="urn:jboss:domain:datasources:4.0">" and add the new driver as shown below (You can see the default h2 driver under this section)

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

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

 </driver>

5. Restart EAP/Wildfly server and you can see the below line in log and this confirms that the JBOSS server detected the new driver
1. WFLYJCA0004: Deploying JDBC-compliant driver class com.microsoft.sqlserver.jdbc.SQLServerDriver (version 6.4)
6. Now let us see how to configure a SQL server as a data source in EAP/Wildfly







1. Connect to JBOSS administration console (For eg: http://localhost:9990/console/App.html#home)
2. Go to Configuration -> Subsystems -> Datasources -> Non-XA and Click on Add
3. Give a Name and JNDI name your data source
4. Specify the driver details as shown in Image 2
5. Provide the connection settings as shown in image 3. In case of local machine use 'localhost', otherwise use the server where SQL server is installed.
6. Make to provide valid user name and password and also make sure the database is created in SQL server
7. Once this is done, click on view the data source and test the connection and make sure it succeeds. If there is a connection problem try restarting the JBOSS server

7. Follow the below steps to configure the persistence.xml
1. Adding the following entry into perisistence.xml

<persistence-unit name="SQLDB" transaction-type="JTA">

        <jta-data-source>java:/SQLDB</jta-data-source>

        <properties>

                    <property name="javax.persistence.jdbc.driver" value="sqlserver"/>

            <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://WIN-FSSP8DPSML0:1433;DatabaseName=SQLDB"/>

            <property name="hibernate.hbm2ddl.auto" value="create-drop" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>

            <property name="hibernate.show_sql" value="true"/>

</properties>

    </persistence-unit>

2. And this is how to inject the PersistenceUnit

@PersistenceContext(unitName="SQLDB")

 private EntityManager entityManager;