Squirrel SQL provides some nice features that SQL Management Studio does not have out-of-the-box including auto-completion of table and column names.  Granted, Red Gate makes tools for Management Studio to get the same functionality, but SQuirrel SQL is open source and free.

There are plenty of articles and blog posts around that cover installing SQuirrel, using JDBC with SQL Server, and connecting to SQL Server Express.  However, I ran into enough snags getting SQuirrel to connect to my installation of SQL Server Express that I thought a brief how-to was in order.  I will show the basic steps that I used to get SQuirrel talking with the database using integrated Windows security.  I will also provide some links to useful articles for further reading.

I assume that the machine is starting with a current installation of SQL Server Express.  I have tested the information here against the 2005 version.  Some settings may be different for 2008.

Initial Installs

Download and install the following requirements:

1. The Java Runtime
2. SQuirrel SQL
3. The Microsoft SQL Server JDBC Driver

Follow the installation instructions for each of the above as detailed on the provided web sites.  This was all pretty straightforward in my experience.  The tricky part was setting up the JDBC URL correctly and getting the right driver .dll files copied around so that the JDBC driver can use integrated security.

EDIT:  Determining the Port Number For Your SQLEXPRESS Instance

For the MS JDBC driver to connect through TCP/IP, this protocol must be enabled for the database instance that you wish to connect to.  SQL Server Configuration Manager is your friend for this.

1. Launch SQL Server Configuration Manager
2. In the left pane, navigate to SQL Server Configuration Manager (Local) -> SQL Server 2005 Network Configuration -> Protocols for
3. In the right pane, right-click “TCP/IP” and enable this protocol if it is not already enabled.
4. Double-click “TCP/IP”
5. Click the “IP Addresses” tab
6. Scroll to the bottom
7. Set a port value in IPAll -> TCP Dynamic Ports

Setting the TCP/IP port forces the instance to use a constant port rather than relying on the SQL Server Browser service for forwarding connections.  You’ll need to know the port number of the instance to connect through TCP/IP

Configuring the JDBC Driver in SQuirrel

SQuirrel supports connections to any database that has a corresponding JDBC driver.  And, there are JDBC drivers available practically every major database as well as plenty of flat file formats.  For connecting to SQL Server, SQuirrel must be told where to find the Microsoft driver that was installed above.

• Start SQuirrel SQL
• Click on the “Drivers” tab on the left side
• Either find the existing entry for the Microsoft JDBC driver or add a new one
• Setup the example URL.  Since I am using Windows security, my example URL looks like this:
  • jdbc:sqlserver://localhost:8433;instanceName=SQLEXPRESS;integratedSecurity=true;databaseName=[catalog];
• Click the “Extra Class Path” tab
• Click the “Add” button
• Navigate to and select the sqljdbc4.jar file in the installation directory of the Microsoft SQL Server JDBC Driver
• Set the class name to com.microsoft.sqlserver.jdbc.SQLServerDriver
• Click OK

If you are using SQL Server Authentication or need other options, more detailed information can be found on the Microsoft web site for this driver.

Setting Up for Integrated Security

In order for the JDBC driver to use integrated security, it needs access to a particular .dll file named “sqljdbc_auth.dll.”  The JDBC driver ships with three different versions, and the required version varies depending on whether the 32-bit or 64-bit version of Java has been installed.  The right .dll can be found under the JDBC driver installation directory at:

<_installation directory_="">auth

EDIT: It appears that the required version depends on the architecture of SQLEXPRESS that is installed.  So, with a 64-bit JVM and 32-bit SQLEXPRESS, I required the 32-bit auth dll.  When I switched to SQLEXPRESS 2008×64, I had to switch to the 64-bit auth dll.

In order for the driver to find the .dll at runtime, copy the appropriate “sqljdbc_auth.dll” into a folder that is already on the system path or add an entry to the PATH environment variable for the directory that contains the .dll file.  Do not put the auth .dll under a path that contains spaces.  This will NOT work.

If you get the wrong version of the .dll, or if Squirrel cannot find the .dll, you will see a message like

JcmsImport: This driver is not configured for integrated authentication.

Correct the problem by shutting down SQuirrel, setting up a different auth .dll on the system path, then relaunching SQuirrel and trying again.

More detailed information about using this driver with integrated security is available from Microsoft.

Configuring The First Alias

In order for the JDBC driver to connect to the correct instance, it needs to know the port for that instance.  This was one of the details that I initially overlooked.  My SQLEXPRESS instance is NOT at the default port.  Rather, I had to look in SQL Server Configuration Manager to find the port number.  To determine the port for a particular instance, launch Configuration Manager and click “Protocols for –> TCP/IP (double click) –> IP Addresses” Scroll to the bottom to see “IPAll” and note the TCP Port setting.  This is the port number that needs to be in the JDBC url.  For my installation, I used 8433.

To add a new alias follow these steps:

1. Click on the “Alias” tab on the left side of SQuirrel.
2. Click the “+” symbol to add an alias.
3. Set the name
4. Select the Microsoft JDBC Driver that was configured earlier
5. Edit the URL to set the initial database catalog
6. Use the “Test” button to test the connection to the database.

Further Reading

• The Microsoft site for the JDBC driver has pretty good information
• Some good information about problems with integrated authorization can be found on this blog post.