Unable to add SQL Server DataSource in JBoss EAP 7.2

Question

I am trying to add an SQL Server datasource in JBoss EAP 7.2 through the management console. However, the Test Connection operation is failing. Below are the version details:

JBoss EAP - 7.2

MSSQL Driver Jar - mssql-jdbc-6.1.0.jre8.jar

Module.xml:

<module name="com.microsoft.sqlserver" xmlns="urn:jboss:module:1.3">
    <resources>
        <resource-root path="mssql-jdbc-6.1.0.jre8.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

Module Directory Structure: jboss-eap-7.2\modules\system\layers\base\com\microsoft\sqlserver\main

Standalone.xml Datasource and Driver Configuration:

<datasource jndi-name="java:/MSSQLDS" pool-name="MSSQLDS">
    <connection-url>jdbc:sqlserver://192.168.XX.XX;DatabaseName=MYTESTDB1</connection-url>
    <datasource-class>com.microsoft.sqlserver.jdbc.SQLServerDataSource</datasource-class>
    <driver>sqlserver</driver>
    <security>
        <user-name>mydb_xxxx</user-name>
        <password>xxxxxxxx</password>
    </security>
</datasource>

<driver name="sqlserver" module="com.microsoft.sqlserver">
    <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
    <datasource-class>com.microsoft.sqlserver.jdbc.SQLServerDataSource</datasource-class>
</driver>

Server Logs:

13:21:52,267 WARN  [org.jboss.as.connector.subsystems.datasources.AbstractDataSourceService$AS7DataSourceDeployer] (MSC service thread 1-5) IJ020020: Connection Properties for DataSource: 'java:/MSSQLDS' is empty, try to use driver-class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver' and connection-url: 'jdbc:sqlserver://192.168.XX.XX;DatabaseName=MYTESTDB1' to connect database
13:21:52,279 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-1) WFLYJCA0001: Bound data source [java:jboss/datasources/ExampleDS]
13:21:52,279 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-7) WFLYJCA0001: Bound data source [java:/MSSQLDS]
13:21:52,640 INFO  [org.wildfly.extension.undertow] (MSC service thread 1-2) WFLYUT0006: Undertow HTTPS listener https listening on 127.0.0.1:8443
13:21:52,803 INFO  [org.jboss.ws.common.management] (MSC service thread 1-8) JBWS022052: Starting JBossWS 5.2.4.Final-redhat-00001 (Apache CXF 3.2.5.redhat-00001)
13:21:54,308 INFO  [org.infinispan.factories.GlobalComponentRegistry] (MSC service thread 1-7) ISPN000128: Infinispan version: Infinispan 'Estrella Galicia' 9.3.3.Final-redhat-00001
13:21:54,598 INFO  [org.jboss.as.clustering.infinispan] (ServerService Thread Pool -- 72) WFLYCLINF0002: Started client-mappings cache from ejb container
13:21:54,792 INFO  [org.jboss.as] (Controller Boot Thread) WFLYSRV0060: Http management interface listening on http://127.0.0.1:9990/management
13:21:54,924 INFO  [org.jboss.as] (Controller Boot Thread) WFLYSRV0051: Admin console listening on http://127.0.0.1:9990
13:21:54,929 INFO  [org.jboss.as] (Controller Boot Thread) WFLYSRV0025: JBoss EAP 7.2.0.GA (WildFly Core 6.0.11.Final-redhat-00001) started in 8695ms - Started 477 of 663 services (333 services are lazy, passive or on-demand)
13:22:21,345 WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (External Management Request Threads -- 1) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: IJ031084: Unable to create connection
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:345)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:352)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:287)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.createConnectionEventListener(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:1326)
        at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreConcurrentLinkedDequeManagedConnectionPool.getConnection(SemaphoreConcurrentLinkedDequeManagedConnectionPool.java:499)
        at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:1067)
        at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:93)
        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:240)
        at org.jboss.as.connector.subsystems.common.pool.PoolOperations$1.execute(PoolOperations.java:97)
        at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:999)
        at org.jboss.as.controller.AbstractOperationContext.processStages(AbstractOperationContext.java:743)
        at org.jboss.as.controller.AbstractOperationContext.executeOperation(AbstractOperationContext.java:467)
        at org.jboss.as.controller.OperationContextImpl.executeOperation(OperationContextImpl.java:1411)
        at org.jboss.as.controller.ModelControllerImpl.internalExecute(ModelControllerImpl.java:423)
        at org.jboss.as.controller.ModelControllerImpl.lambda$execute$1(ModelControllerImpl.java:243)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:265)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:231)
        at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:243)
        at org.jboss.as.domain.http.server.DomainApiHandler.handleRequest(DomainApiHandler.java:212)
        at io.undertow.server.handlers.encoding.EncodingHandler.handleRequest(EncodingHandler.java:72)
        at org.jboss.as.domain.http.server.DomainApiCheckHandler.handleRequest(DomainApiCheckHandler.java:93)
        at org.jboss.as.domain.http.server.security.ElytronIdentityHandler.lambda$handleRequest$0(ElytronIdentityHandler.java:62)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:289)
        at org.wildfly.security.auth.server.SecurityIdentity.runAs(SecurityIdentity.java:246)
        at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:254)
        at org.jboss.as.controller.AccessAuditContext.doAs(AccessAuditContext.java:225)
        at org.jboss.as.domain.http.server.security.ElytronIdentityHandler.handleRequest(ElytronIdentityHandler.java:61)
        at io.undertow.server.handlers.BlockingHandler.handleRequest(BlockingHandler.java:56)
        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:360)
        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
        at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
        at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378)
        at java.lang.Thread.run(Thread.java:748)
        at org.jboss.threads.JBossThread.run(JBossThread.java:485)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: no further information. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:206)
        at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:257)
        at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2385)
        at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:567)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1955)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1616)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1447)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:788)
        at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:719)
        at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:79)
        at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createLocalManagedConnection(LocalManagedConnectionFactory.java:314)
        ... 35 more

13:22:21,350 ERROR [org.jboss.as.controller.management-operation] (External Management Request Threads -- 1) WFLYCTL0013: Operation ("test-connection-in-pool") failed - address: ([
    ("subsystem" => "datasources"),
    ("data-source" => "MSSQLDS")
]) - failure description: "WFLYJCA0040: failed to invoke operation: WFLYJCA0047: Connection is not valid"

Update - Even though i have specified the IP address of my database, JBoss is trying to connect to localhost instead of that IP. I have verified and ruled out any network / firewall related problems. Through a sample Java/SpringBoot project, i am able to open a JDBC connection to the database. However, the datasource creation from JBoss is failing.

Log says: "The TCP/IP connection to the host localhost, port 1433 has failed." check your TCP connection and the relative port. — Max, Sep 19 '19 at 08:15
Hi Max, I reckon that's the problem that even though i have specified IP address for my database, JBoss is trying to connect to localhost. I notice a statement in the logs saying - IJ020020: Connection Properties for DataSource: 'java:/MSSQLDS' is empty, try to use driver-class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver' Not sure why it says Connection Properties empty even though all the fields like DB URL, driver class, datasource class are provided — Vaibhav1988, Sep 19 '19 at 08:19
I think the Jboss don't read your xml, [docs says](https://docs.jboss.org/author/display/WFLY10/DataSource+configuration) to place the config on Datasource Definitions under `subsystem xmlns="urn:jboss:domain:datasources:4.0"` — Max, Sep 19 '19 at 08:26
Make sure that TCP/IP is enabled in MSSql Server. check https://stackoverflow.com/questions/2388042/connect-to-sql-server-2008-with-tcp-ip question — seenukarthi, Sep 19 '19 at 08:27
Possible duplicate of [JDBC connection failed, error: TCP/IP connection to host failed](https://stackoverflow.com/questions/18841744/jdbc-connection-failed-error-tcp-ip-connection-to-host-failed) — seenukarthi, Sep 19 '19 at 08:29
@KarthikeyanVaithilingam I am able to connect to the database directly through a Java program, however, its the JBoss datasource creation which is failing. Another thing i notice, is that even though i have the connection-url and driver details specified, JBoss reports that connection properties are empty. I reckon this is the main problem, just that i am yet to figure the solution out. — Vaibhav1988, Sep 19 '19 at 11:55

score 2 · Answer 1 · answered Nov 16 '19 at 10:55

2

Found the solution finally. It’s a problem with Wildfly when the datasource-class is used while adding the driver module.

With the datasource class, explicit connection properties are required and the connection-url tag is “effectively” ignored. By “effectively” ignored, I mean JBoss erroneously expects connection-url tag to be present and just ignores it.

This issue has been called out in issues - WFLY-6157 and WFLY-6200.

answered Nov 16 '19 at 10:55

Vaibhav1988

39
1
7

you saved my day. Thanks. – niiraj874u Nov 27 '19 at 07:20
Can you expand? I don't understand how this is an answer (my fault, I'm sure). I'm having this exact issue – Keith Fosberg Jan 31 '20 at 17:38

Glenn · Answer 2 · 2020-04-28T12:32:52.407

1

This is a little bit late but the solution can be found in redhat solutions. The issue is when defining non-XA datasource, datasource-class has been define in the datasource setup.

So what I did is goto admin console > configuration > subsystems > Datasource & drivers > Datasources > < datasource name > > click view.

on the attributes tab > click edit > delete the value in Datasource Class > save and reload server.

Test your connection now and it should be successful.

edited Apr 28 '20 at 12:32

answered Apr 28 '20 at 12:25

Glenn

9
3

I could kiss you. But first Im going to wait in the red hat parking lot and beat up all their devs. – George Dec 24 '20 at 00:15

mchav5778 · Answer 3 · 2020-06-18T03:42:13.483

1

Taking into account Vaibhav1988's comments about "the datasource class needs explicit connection properties", in my case the solution was adding the following 2 properties under the datasource configuration:

databaseName=

ServerName=

In the Management Console go to "Subsystems" -> "Datasources & Drivers" -> "Datasources" then click on "View" in your datasource, go to "Connection" tab, click on "Edit" and add the properties in the field that says "Connection Properties".

edited Jun 18 '20 at 03:42

answered Jun 18 '20 at 02:52

mchav5778

11
2

This worked for me when attempting to connect to a DB2 running on an AS400. – allkenang Aug 03 '23 at 05:03

score 1 · Answer 4 · answered Sep 27 '21 at 21:02

Solution for me, edit file standalone.xml:

<datasource jndi-name="java:jboss/datasources/SqlServerdb" pool-name="SqlServerdb" statistics-enabled="true">
                    <datasource-class>com.microsoft.sqlserver.jdbc.SQLServerDataSource</datasource-class>
                    <connection-property name="databaseName">
                        DATABASENAME
                    </connection-property>
                    <connection-property name="ServerName">
                        IP(WITHOUT PORT)
                    </connection-property>
                    <driver>sqlserver</driver>
                    <security>
                        <user-name>USER</user-name>
                        <password>PASSWORD</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
                        <background-validation>true</background-validation>
                    </validation>
</datasource>

and:

<driver name="sqlserver" module="com.microsoft.sqlserver">
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>

Unable to add SQL Server DataSource in JBoss EAP 7.2

4 Answers4