5 Replies Latest reply on Feb 18, 2007 8:51 AM by omprakash82

    Configuring access to ms sql server 2005

    andsch

      Hi,

      version 4.0.3 SP1 of JBoss AS ships with the following example file for accessing MS Sql Server:

      <datasources>
       <local-tx-datasource>
       <jndi-name>MSSQLDS</jndi-name>
       <connection-url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=MyDatabase</connection-url>
       <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
       <user-name>x</user-name>
       <password>y</password>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>MS SQLSERVER2000</type-mapping>
       </metadata>
       </local-tx-datasource>
      
      </datasources>
      


      I found that the following configuration works for MS SQL Server 2005:

      <datasources>
       <local-tx-datasource>
       <jndi-name>MSSQLDS</jndi-name>
       <connection-url>jdbc:sqlserver://localhost:1433;databaseName=MyDatabase</connection-url>
       <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
       <user-name>x</user-name>
       <password>y</password>
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
      
       <!-- sql to call on an existing pooled connection when it is obtained from pool
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
       <metadata>
       <type-mapping>MS SQLSERVER2000</type-mapping>
       </metadata>
       </local-tx-datasource>
      
      </datasources>
      


      Regards,
      Andreas

        • 1. Re: Configuring access to ms sql server 2005
          ahoward333

          I'm using JBoss 4.0.2 and have recently installed SQL Server 2005 (Express).
          However, I get the following exception:

          org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect)
          



          I tried changing the JNDI to specify the instance name (the only way I can successfully connect to the database without JNDI):
          jdbc:sqlserver://myserver\\SQLEXPRESS;DatabaseName=MyDatabase
          


          However, this results in a different exception:
          org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the named instance has failed. Error: java.net.SocketTimeoutException: Receive timed out.)
          



          I have successfully connected via JDBC (without JNDI) with the instance name, so I'm assuming that the problem lies in how I'm using the JNDI descriptor.
          Has anyone else successfully setup a JNDI using the instance name nomenclature?
          If I must specify the port number, then is there a configuration change I must make to SQL Server to allow direct port connections?

          Any help would be greatly appreciated!
          -Trey


          • 2. Re: Configuring access to ms sql server 2005
            andsch

            did you enable TCP/IP as a protocol in the SQL Server Configuration Manager? If not, try doing so.

            hth
            Andreas

            • 3. Re: Configuring access to ms sql server 2005
              ahoward333

              Thanks for the quick reply, Adreas.

              TCP/IP was enabled, but I found the culprit.
              I accidentally had two backslashes in the JNDI URL.
              When I removed the extra backslash, everything works :)

              • 4. Re: Configuring access to ms sql server 2005
                dmver

                Sorry, I'm not sure
                Try to specify dynamic TCP port.
                e.g. jdbc:jtds:sqlserver://localhost:<PORT_NUM>/test
                See "SQL Server Configuration Manager" -> relevant instance network protocol TCP/IP (MUST be enabled) properties -> dynamic port.

                • 5. Re: Configuring access to ms sql server 2005
                  omprakash82

                  did u try with MYsql then pl send me detail of how to do that. i am getting server not found exception