Wednesday, February 15, 2012

Unholy union: Glassfish and MSSQL

I'm in a situation where the data is on a Microsoft SQL Server and I want to use Glassfish and J2EE. And all of this done inside Netbeans.
SQLServer + Netbeans?
Getting Netbean's version of Glassfish to work with MS SQLServer 2008 is a trick. When you start it up and open the Admin console webpage you'll head to the Resources > JDBC > JDBC Connection Pool and open make a new connection. You'll finish out the wizard until you try to ping the server. FAIL!

The problem is Netbean's version of Glassfish doesn't have the correct jars. Open C:\Program Files\glassfish-3.1.1\glassfish\lib; take a look and facepalm at this display of fail from Oracle.

Lets not dwell on that.

So we need a JDBC driver and a JDBC driver we shall get. Don't worry although its Microsoft, it won't cost you a thing. Documentation here. Driver here and just drop the jar file into that folder you just opened. Use the sqljdbc4.jar. Don't forget to restart the Glassfish server.

Now we redo the step in creating the JDBC connection pool resource. Here is what I did:
  1. Named the Pool: MSSQL2008
  2. Set the resource type to: java.sql.driver
  3. Set the driver class name to: com.microsoft.sqlserver.jdbc.SQLServerDriver
  4.  Set my properties to the following values:
    • URL = jdbc:sqlserver://{iptoserver};instanceName={name};databaseName={defaultdb};integratedSecurity=false;
    • user = my admin username
    • password = duh!
Troubleshoot if the ping test fails.

Don't forget to create a JDBC resource after you succeed in creating your connection pool in Glassfish. You'll need it if you want to get database access via JNDI.