Glassfish Connection Pool setup

downloaded the 2005 JDBC divers for UNIX. You have to agree to a licence before doing so. I didn't read it. I hope I still own my soul :) On glassfish I copied the jar to  /lib/ext. I then setup a connection pool with the following properties:

• DataSource Classname: com.microsoft.sqlserver.jdbc.SQLServerXADataSource
• Resource Type: javax.sql.XADataSource
• Additional Properties:
  • instanceName : SQLEXPRESS (or whatever you named your instance)
  • password : super-secret-password
  • user: database user
  • portNumber : dynamic port number(1123)
  • serverName: name or ip address of the server

After the I was successful in pining the remote MS SQL 2005 server and setup the relevant JNDI entries. What a relief! 

SQL Server 2005 Jdbc connection|MSSQL 2005 -JDBC Connection

Connecting to a version of MS SQL post 2000 requires a few extra pieces of knowledge these days.

• By default MS SQL does not listen on anyTCP/IP socket. You have to go to MS SQL configuration manager console and enable tcp/ip for the instance under Network Configuration. You need to restart SQL for this to take effect. It also appears that enabling TCP/IP does not enable it on a specific ip address. I had to set the specific ip address under "SQL Server 2005 Network Configuration"=>TCP/IP Protocol=>(right click) Properties=>IP Addresses to enabled = yes.
• Instances do not run on port 1433 anymore. They use dynamic ports. If you right click on the tcp/ip protocol for your instance under "SQL Server 2005 Network Configuration" you should be able to force this to change by changing the setting on the "IP Addresses "tab but this didn't work for me.You can pick up the dynamic port it is using with netstat or from the "IP Addesses" tab. Since it is a dev environment I didn't care much as long as I could connect.
• Don't foget to allow the windows firewall to let external request to the port through.