JDBC connection to MS SQL 2005

I haven't had to work with MS SQL server since around 2003 but we recently took over a project that was running on MS SQL so I got to work with it again. It was quiet nice when MS took long to release new versions of its flagship database because it meant I didn't have to learn anything new for years. Sadly things have changed since then :) Although our client is running on MS SQL 2000 and that should have been a relief, except for the fact that, being a Linux shop, we don't have a copy of MS SQL 2000 and could only get the Express version of 2008 and 2005 from MS web site.

MS SQL 2008  - Restoring MS SQL 2000 files

I first could only find SQL Express 2008, and so tried that. Re-attaching a detached database from 2000 to 2008 worked like a charm. This was my biggest relief as all I have right now are the MDF files. I would have been screwed if I needed to run some export or migration script to upgrade the database.

I then started looking around for JDBC drivers. I had the old drivers for 2000 from MS and the open source ones but couldn't get them to work. It later emerged that MS SQL, since 2005, is using dynamic ports on its instances so maybe they would work it I knew this before hand. (More about MS SQL connection setup below). Anyway this prompted me to look for an alternative to running MS SQL 2008.

MS SQL 2005-Restoring MS SQL 2000 files

With relief again I found that there were updated JDBC MSSQL drivers for 2005. I also managed to find the Express edition on Microsoft's site too, so downloaded that. Once  again re-attaching a detached database worked a charm. I did have this funny experience though that the windows box kept on making the folder I placed the MDF files in read only, which caused the re-attach to fail. I tried removing the read only attribute on the folder but no luck. I have no idea what Windows was up to, maybe some new security feature? In the end I just placed the files under "Program Files" in the data directory for MS SQL and was able to attach successfully.

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.

Glassfish Connection Pool setup

Next I 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  <domain>/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!