Configuring SQL Server 2005 Express to work with JDBC

If you are configuring a named instance SQL Server Express 2005 to work with JDBC, here are some pointers.

For Eclipse:

1. Make sure you have the latest JDBC drivers from Microsoft. There is lots of information in this download, so make sure you use it.
2. Add the sqljdbc_1.2.jar to your build path

If you are having a little trouble with your connection string and named instances of SQL Server. My connection string looks like this:

private static final String _DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String _CON_STRING = "jdbc:sqlserver://localhost\\SQLExpress";
	+ "PortNumber=1433;databaseName=TEST;username=**********;password=**********;";

To use MS SQL Server 2005

1. Add and external jar to your build path for
2. Configure your firewall to allow SQL Server through (You may use Port 1433 for this, but see the next item).
3. SQL Server doesn't necessarily reside on port 1433. To obtain the <port>, select SQL Server 2005 Network Configuration>Protocols for SQLEXPRESS in SQL Server Configuration Manager. Right-click on TCP/IP node and select Properties. In the TCP/IP Properties tab, select the IP Addresses tab. In IP ALL section, the TCP Dynamic Ports specifies the <port> value. This is found at the end of your list of IPs. I put in the number 1433 to ensure that it used this port. You might also want to enable a particular IP here as well. [1]

At this point I no longer had the connection refused error or any other errors for that matter from the following code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.*;

...

try {
	Class.forName(_DRIVER).newInstance();
	Connection con = DriverManager.getConnection(_CON_STRING);
	Statement s = con.createStatement();
	ResultSet rs = s.executeQuery("SELECT * FROM dbo.tester");
	ResultSetMetaData rsMeta = rs.getMetaData();
	System.out.println("Column Count: " + rsMeta.getColumnCount());
	int ccnt = rsMeta.getColumnCount();
	for (int cols=1; cols<=ccnt; cols++)
	{
		System.out.print(rsMeta.getColumnName(cols) + " ");
	}
	System.out.println(" ");
	while (rs.next())
	{
		for (int i=1; i <= ccnt; i++)
		{
			System.out.print(rs.getString(i) + "  ");
		}
	System.out.println(" ");
}
rs.close();
con.close();
}
catch (Exception e)
{
	System.out.println(e.getMessage());
	System.out.println(e.getStackTrace());
}