JBoss 7.1 - SQL Server - Datasource configuration (JTDS)

12,427

(Answered in comments and edits. See Question with no answers, but issue solved in the comments (or extended in chat) )

The OP wrote:

@Jon Skeet: The SQL Server error log shows the following:

01/25/2013 09:47:02,Logon,Unknown,Login failed for user 'aladinoDs'. Reason: Failed to open the explicitly specified database.

So, the problem should be that I'm not using the proper database name.

@CoolBeans: I'm not used to SQL Server (usually I work with Oracle DB), and I'm not familiar with its distinction between databases/instances/logins, but if I'm using the credentials above to successfully logon into db through SQL Server Mgmt Studio, why am I not able to use them for datasource? When I connect to db server, I can see in SQL Server Mgmt Studio, into "databases" folder, two objects: System Databases and AladinoSFA2. Should I use the latest one as server name in the connection string? This will be my next try. I'll post an update.

SOLVED:

I corrected the connection string: <connection-url>jdbc:jtds:sqlserver://DAN-Aladino-vs.usersad.everis.int:1433/AladinoSFA2</connection-url>

It seems that i would have to use the single database name and not the database server name into the connection string. I don't really understand why using SQL Server Mgmt Studio I do not need to specify db instance name, but only db server name, and in the datasource, on the contrary, the database instance name is the only one needed.

However now it all works.

Share:
12,427
Dolfiz
Author by

Dolfiz

Game developer, former IT consultant and couch potato.

Updated on June 29, 2022

Comments

  • Dolfiz
    Dolfiz over 1 year

    I'm fighting with this problem for several days and I has not been able to solve it. I've got a server with an instance of SQL Server 2008 R2 and a JBoss 7.1 installed (I'm using standalone configuration). I was trying to configure a datasource in the application server to connect to database, using JTDS drivers. The application server starts correctly but when I tried to test the datasource through the admin console it spools out the following error:

    17:49:42,117 WARN  [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (HttpManagementService-threads - 1) IJ000604: Throwable while attempting to get a new connection: null: javax.resource.ResourceException: Co
    uld not create connection
            at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:277) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:235) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.createConnectionEventListener(SemaphoreArrayListManagedConnectionPool.java:761) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.
    9.Final]
            at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:343) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.core.connectionmanager.pool.AbstractPool.internalTestConnection(AbstractPool.java:627) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.jca.core.connectionmanager.pool.strategy.OnePool.testConnection(OnePool.java:88) [ironjacamar-core-impl-1.0.9.Final.jar:1.0.9.Final]
            at org.jboss.as.connector.pool.PoolOperations$TestConnectionInPool.invokeCommandOn(PoolOperations.java:121) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.connector.pool.PoolOperations$1.execute(PoolOperations.java:60) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.connector.pool.PoolOperations.execute(PoolOperations.java:74) [jboss-as-connector-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.ModelControllerImpl$DefaultPrepareStepHandler.execute(ModelControllerImpl.java:466) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.executeStep(AbstractOperationContext.java:385) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.doCompleteStep(AbstractOperationContext.java:272) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.AbstractOperationContext.completeStep(AbstractOperationContext.java:200) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.ModelControllerImpl.execute(ModelControllerImpl.java:121) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:309) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.controller.ModelControllerImpl$1.execute(ModelControllerImpl.java:299) [jboss-as-controller-7.1.1.Final.jar:7.1.1.Final]
            at org.jboss.as.domain.http.server.DomainApiHandler.processRequest(DomainApiHandler.java:294)
            at org.jboss.as.domain.http.server.DomainApiHandler.doHandle(DomainApiHandler.java:201)
            at org.jboss.as.domain.http.server.DomainApiHandler.handle(DomainApiHandler.java:208)
            at org.jboss.as.domain.http.server.security.SubjectAssociationHandler.handle(SubjectAssociationHandler.java:51)
            at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
            at org.jboss.sun.net.httpserver.AuthFilter.doFilter(AuthFilter.java:69)
            at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
            at org.jboss.sun.net.httpserver.ServerImpl$Exchange$LinkHandler.handle(ServerImpl.java:710)
            at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:78)
            at org.jboss.as.domain.http.server.RealmReadinessFilter.doFilter(RealmReadinessFilter.java:54)
            at org.jboss.com.sun.net.httpserver.Filter$Chain.doFilter(Filter.java:81)
            at org.jboss.sun.net.httpserver.ServerImpl$Exchange.run(ServerImpl.java:682)
            at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.7.0_11]
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.7.0_11]
            at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_11]
            at org.jboss.threads.JBossThread.run(JBossThread.java:122) [jboss-threads-2.0.0.GA.jar:2.0.0.GA]
    Caused by: java.sql.SQLException: Cannot open database "SQLEXPRESS" requested by the login. The login failed.
            at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
            at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
            at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
            at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:603)
            at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:345)
            at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
            at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
            at org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory.getLocalManagedConnection(LocalManagedConnectionFactory.java:249) [ironjacamar-jdbc-1.0.9.Final.jar:1.0.9.Final]
            ... 39 more
    

    It seems a simple authentication problem, but the user/pwd is absolutely correct! Through SQL Server Management Studio I'm able to correctly connect to the db using the following:

    • Server name: DAN-Aladino-vs.usersad.everis.int\SQLEXPRESS
    • Authentication: SQL Server Authentication
    • Login: aladinoDs
    • Password: aladinoDs

    To configure the datasource I've made the following steps:

    1) In JBoss I've created the directory "modules\net\sourceforge\jtds\main".

    Inside it I've put the jtds-1.2.5.jar and a new module.xml with the following content:

    <?xml version="1.0" encoding="UTF-8"?>
    <module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">
      <resources>
        <resource-root path="jtds-1.2.5.jar"/>
            <!-- Insert resources here -->
      </resources>
      <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
      </dependencies>
    </module>
    

    2) I've modified the standalone.xml configuration file adding the followings:

    <datasource jndi-name="java:jboss/datasources/AladinoDS" pool-name="AladinoDS" enabled="true" use-java-context="true">
        <connection-url>jdbc:jtds:sqlserver://DAN-Aladino-vs.usersad.everis.int:1433/SQLEXPRESS</connection-url>
        <driver>JTDS</driver>
        <new-connection-sql>select 1</new-connection-sql>
        <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
        <pool>
            <min-pool-size>5</min-pool-size>
            <max-pool-size>50</max-pool-size>
        </pool>
        <security>
            <user-name>aladinoDs</user-name>
            <password>aladinoDs</password>
        </security>
        <validation>
            <check-valid-connection-sql>select 1</check-valid-connection-sql>
        </validation>
        <timeout>
            <set-tx-query-timeout>true</set-tx-query-timeout>
            <blocking-timeout-millis>5000</blocking-timeout-millis>
            <idle-timeout-minutes>15</idle-timeout-minutes>
        </timeout>
        <statement>
            <track-statements>false</track-statements>
        </statement>
    </datasource>
    

    and, in the <drivers> section:

    <driver name="JTDS" module="net.sourceforge.jtds">
        <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    </driver>
    

    According to what I've found on the web, it should be correct, but it still not work. By the way, I don't want to use Windows Authentication for the datasource but I tried also that way, unsuccessfully.

    I hope someone can find something wrong in my caonfiguration. If it's correct, could be a problem of the database server/instance configuration? I'm puzzled... through SQL Server Mgmt Studio all seems to work.

    Thank you all, Luca

  • Prashant
    Prashant over 8 years
    SQLEXPRESS is the instance name. You could specify it as a property "instanceName=SQLEXPRESS" with the jtds driver but you dont need to if you connect to the correct port (each instance has its own TCP port number, only if you want to make the jtds driver find out the (dynamic) port via SQL Browser service you would use the instanceName.