Database validation checker and Integrated Windows Authentication with Database in Wildfly 10

Database validation checker

To restore the database connection pool after its connection failure, we can use valid-connection-checker class
which keeps on polling on the database and restores i.

<datasource jndi-name="java:/mssqlds" pool-name="java:/mssqlds" enabled="true" statistics-enabled="true">
   <connection-url>jdbc:jtds:sqlserver://localhost:1433/mssqldb;SelectMethod=direct;prepareSQL=0;sendStringParametersAsUnicode=false</connection-url>
   <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
   <driver>net.sourceforge.jtds</driver>
   <pool>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>50</max-pool-size>
      <prefill>true</prefill>
   </pool>
   <security>
      <user-name>mssqldb</user-name>
      <password>pass@123</password>
   </security>
   <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker" />
      <background-validation>true</background-validation>
      <background-validation-millis>120000</background-validation-millis>
   </validation>
</datasource>

Statistics:
To capture the database connection statistics like available count, active database count, etc set statistics-enabled="true" on datasource tag.

valid-connection-checker
org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker class internally fires a query on database.

background-validation
The validation checker will run as a daemon thread every 120000 millis.Once the database connection is restored it connects with is immediately.

Integrated Windows Authentication with Database

Pre-Requisite :

1.Application Server needs to be on Windows machine and it should be in the same domain as AD.
2.Windows User should have access to all the entities of the database.
3.Database schema should be the default schema for user.

As it does not provide native libraries which are required for JTDS to work with
Windows authentication on Microsoft SQL Server.
Thus, it is necessary to download that native dll (ntlmauth.dll) and perform some additional settings.

The download instructions are:
1.Download the distribution package using below url (jtds-xxx-dist.zip)
https://sourceforge.net/projects/jtds/files/jtds/1.2.7/
2.Extract the contents and in the extracted folder go to x64\SSO, or x86\SSO.Use the file that matches the target architecture. For instance, for a 64-bit operating system, extract the file under x64/SSO.
3.ntlmauth.dll is located x64\SSO or x86\SSO folder. Copy the file under %SYSTEMROOT%/system32.

Add datasource in domain.xml ({Wildfly-Home}\domain\configuration)

<datasource jndi-name="java:/mssqlds" pool-name="java:/mssqlds" enabled="true" statistics-enabled="true">
   <connection-url>jdbc:jtds:sqlserver://localhost:1433/mssqldb;integratedSecurity=true;authenticationScheme=JavaKerberos;SelectMethod=direct;prepareSQL=0;sendStringParametersAsUnicode=false</connection-url>
   <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
   <driver>net.sourceforge.jtds</driver>
   <pool>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>50</max-pool-size>
      <prefill>true</prefill>
   </pool>
   <validation>
      <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker" />
      <background-validation>true</background-validation>
      <background-validation-millis>120000</background-validation-millis>
   </validation>
</datasource>