Setup MICROSOFT SQL SERVER Data source in JBoss EAP 7.1

In this post I will demonstrate how to setup Microsoft SQL Server Data source in JBoss EAP 7.1. I will use code base of my previous post "Passing spring application parameters in EAP 7.1"(Initial code base)

Prerequisites 

1. You should have install java 1.8 or above.
2. You should have Eclipse installed in your PC.
3. Your PC should setup Maven installed and configured.
4. Your PC should have installed MySQL and Server need to have user who have access to the configured database and user should able to log in to the database by user name/password. 

Install MSSQL driver in JBoss EAP

First lets download a JDBC Driver based on 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

I downloaded the "sqljdbc4-2.0.jar" which compatible with my SQL server version. Lets keep the downloaded jar in temporary folder. In my case I'll keep it on "G:/" drive. 

So lets use "JBoss-cli" in order to add the Driver and JNDI in to EAP. You can find the jboss-cli in <JBOSS_HOM>/bin directory.

You can start the EAP first by double click on standalone.bat and then start jboss-cli by double click jboss-cli.jar.

Then you can enter connect command in order to connect to EAP.

connect

Then you can enter following command

module add --name=sqlserver.jdbc --resources=@INSTALL_FOLDER@/libext/jtds-1.3.1.jar --dependencies=javax.api,javax.transaction.api
/subsystem=datasources/jdbc-driver=sqlserver:add(driver-module-name=sqlserver.jdbc,driver-name=sqlserver,driver-class-name=@JDBC_DRIVER@)
/subsystem=datasources/data-source=@DATASOURCENAME@:add(jndi-name=java:jboss/@JNDI_NAME@,enabled="true",use-java-context="true",driver-name=sqlserver,connection-url="@JDBC_URL@",user-name=@JDBC_USER@,password=@JDBC_PASSWORD@,validate-on-match=true,background-validation=true)

This is stranded command which you can replace with your parameters. So based on my parameter details Command would be something like below. I have highlighted the modifications which I have done.


module add --name=sqlserver.jdbc --resources=G:\sqljdbc4-2.0.jar --dependencies=javax.api,javax.transaction.api
/subsystem=datasources/jdbc-driver=sqlserver:add(driver-module-name=sqlserver.jdbc,driver-name=sqlserver,driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver)
/subsystem=datasources/data-source=MSSQLDATASOURCE:add(jndi-name=java:jboss/MSSQLJNDI,enabled="true",use-java-context="true",driver-name=sqlserver,connection-url="jdbc:sqlserver://localhost;databaseName=TestDB",user-name=nirmal,password=Test123_,validate-on-match=true,background-validation=true)

Once you run the command you should be able to see following output in jboss-cli.

Then also you can see following section has been added in to the standalone.xml

Lets configure Application to read JNDI

In order to read the DataSource details from JNDI just need to add JNDI string in to Spring Boot application.properties file. I have added spring.datasource.jndi-name=java:jboss/MSSQLJNDI@ application.properties file. (Note that I have commented previous datasource configuration details )

#==== connect to MSSQL ======#
#spring.jpa.hibernate.ddl-auto=update
#spring.datasource.url=jdbc:sqlserver://localhost;databaseName=TestDB
##spring.datasource.username=nirmal
##spring.datasource.password=Test123_
#spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
#spring.jpa.database-platform=org.hibernate.dialect.SQLServer2012Dialect
server.port = 8080
spring.datasource.jndi-name=java:jboss/MSSQLJNDI@

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=false
spring.jpa.properties.hibernate.format_sql=true

#==== Logging configurations ======#
logging.level.root=WARN,INFO,ERROR
logging.level.com.baeldung=TRACE

Then lets deploy the war file. For that click on Back button on left top corner and then click on deployments. Then click "Add" button. Then you should be able to see similar figure to below.

Then click next button and then Click choose file and select the generated war file from target folder in the project. Then click next and then click finish. Once installation completed you should be able to see similar output on command line.

http://localhost:8080/RestApiSpringBoot-0.0.1-SNAPSHOT/findBook/1

Also you can perform any action which I mentioned in my previous blog post.

Project source can be downloaded from following GIT HUB URL.

Installing SQL Server 2017 Express edition in windows

In this post I'm going to setup SQL Server 2017 Express edition in windows.

Download and Install SQL Server 2017 Express edition

You can download SQL server 2017 express edition on windows official download page. Then you can simply run the exe file and install it. You can select Basic installation as show in bellow and installed in to preferred location in your PC.

Select Basic installation in installation process

Once it successfully installed you can see below screen and on there you can click on install SSMS button and download SQL Server Management Studio (SSMS). Then install it.

Then restart your PC in order to complete the process. After restart you can go to start menu and find "SQL Server Management Studio". Then Open the SQL Server Management Studio application.

Creating new Database in SQL Server 

When you open the SQL Server Management Studio you can see similar to below image and you can log in to SQL Server with windows authentication.

Then Go to database section and lets create sample database by right click on Databases and click on new Database menu.

I'll give DB Name as "TestDB" and click ok. Then it will create database which you can see under Database menu. 

Note : Below section impotent to resolve Login Failed for User (Microsoft SQL Server, Error: 18456)

Login Failed for User (Microsoft SQL Server, Error: 18456)

First lets enable SQL Server authentication by right click on SQLExpress in SQL Server Management Studio and click on properties. Then click on Security tab and click SQL Server and Windows Authentication mode radio button in Server Authentication section. Then click ok. Now users can log in to DB by enter user name and password.

Creating new user in SQL Server 

First of all lets enable "SA" account under Security by go to Logins and right click on sa. Then go to status and make it Enable as show in below.

Then lets create new user by right click on 
Security and go to New->Login.
Then provide Login Name, I will use TestUser and then provide preferred password. Then go to User Mapping and select the database you created as show below.

Then if you try to create new Database connection using SQL Server Authentication it will failed.

First go to Control Panel->Administrative Tools->Services, and search for the SQL Server Agent. Right-click, and select Properties From the Startup Type dropdown, change from Disabled to Automatic. As show in below picture. Then right click on SQL Server Agent (SQLEXPRESS) and click start. Then right click on SQL Server (SQLEXPRESS)  and click restart. After that restart your PC.

Then go to SQL Server 2017 Configuration Manager and click on SQL Server Network configuration section. There you can see TCP/IP and Named Pipes sections are disabled. Right click on it and click Enable. 

Then go to SQL Server Service tab and right click on SQL Server Express. Then click on Restart. 

Now you can open the SQL Server Management Studio and click on Connection Object Explorer and Provide Authentication mode as SQL Server Authentication. Then provide your user name and password. Then click connect you will be able to connect to the database.

Now this user can be used in our applications as well.