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

[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

[andsch]

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

hth
Andreas

[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 :)

[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.

[omprakash82]

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