SQuirreL SQL: a nice database GUI frontend for Ubuntu

Here at MalariaGEN, we use MySQL extensively, and there are myriad nice GUI tools for accessing it from our Ubuntu desktops. However, we also use Microsoft SQL Server for some of our particularly large laboratory data, and we wanted to access MS SQL Server databases on Ubuntu (11.04 Natty Narwhal) with a GUI, preferably with open source software.

Here is how to set up one such tool (SQuirrel SQL). Note that we will install the application system wide; it is also possible to install it in your home directory, and to create the custom launcher in .local/share/applications if you like. We’re focussed on the install process on Ubuntu 11.04 with Unity, but these instructions should work on other modern linux distros without too much modification.

We want to support “Windows NT Authentication”, often called “integrated security“, relying on Microsoft’s domain logins, which establish a user’s network security attributes at network login time. When connecting to the database server, SQL Server accepts an encrypted password in the login packet, and uses Windows NT facilities authenticate it, usually via the Primary Domain Controller (PDC). The server then permits or denies login access based on the response. We also want to support traditional “Standard Mode” authentication, where usernames and passwords are stored within SQL Server. They are passed in the login packet as plaintext, and connection requests are authenticated without consulting the operating system. jTDS supports both of these modes. In our example, we will use integrated security, but you can use standard mode by leaving out the steps to specify the DOMAIN property in the connection alias.

From the jTDS FAQ:

domain
    Specifies the Windows domain to authenticate in. If present and the user name and password are provided, jTDS uses Windows (NTLM) authentication instead of the usual SQL Server authentication (i.e. the user and password provided are the domain user and password). This allows non-Windows clients to log in to servers which are only configured to accept Windoes authentication.

0. ensure you have java installed (either sun-java6-jre or openjdk-6-jre). If you don’t, install one using software centre, apt-get or synaptic, etc. As far as we can tell, this works equally well with either openjdk or sun java. Remember that to install Sun java, you will need to enable the Partner repository by uncommenting the lines in /etc/apt/sources.list (just search for partner).

1. download SQuirreL SQL.

2. run the installer with “sudo java -jar squirrel-sql-3.2.1-install.jar”.
– the default installation directory (when the installer is run as root) is “/usr/local/squirrel-sql-3.2.1” which is fine. If you don’t run it as root, the default install location is ~/squirrel-sql-3.2.1).
– include the Base, Standard, Microsoft SQL Server and MySQL packs.
– don’t bother installing shortcuts; we’ll do this manually to support the Unity way of doing things later.

3. If you’re installing it system-wide rather than in your home directory, create a link to the app in /usr/local/bin (which is in the default path).

cd /usr/local/bin
sudo ln -s ../squirrel-sql-3.2.1/squirrel-sql.sh squirrel-sql

This allows you to run squirrel SQL with the squirrel-sql command in a terminal.

4. (optional) create a Unity compatible launcher for squirrel sql:
– right click your desktop
– Create Launcher…
– Name: SQuirreL SQL
– Command: /usr/local/bin/squirrel-sql
– Comment: Universal SQL Client
– Click the icon (by default this is a spring thingy)
– navigate to /usr/local/squirrel-sql-3.2.1/icons/acorn.png and click Open
– Click OK

cd /usr/local/share/applications # create it if it doesn't exist, rwxr-xr-x root:root
sudo mv ~/Desktop/SQuirreL\ SQL.desktop .
sudo chown root:root SQuirreL\ SQL.desktop

You should now be able to run SQuirreL SQL by typing SQuirrel SQL in the quick search bar you get by clicking the Ubuntu button in the top right corner of the screen.

5. Install and set up the jTDS database driver:
– install the libjtds-java ubuntu package (using software centre, apt-get or synaptic, etc). [incidentally, the mysql driver package is libmysql-java, in case you’re interested].
– Run SQuirreL SQL
– Click Drivers on the left
– Right click jTDS Microsoft SQL and click Modify Driver…
– Click the Extra Class Path tab
– Click Add
– navigate to /usr/share/java/jtds.jar and click open
– Click OK. You should see a “Driver class net.sourceforge.jtds.jdbc.Driver sucessfully registered for driver definition: jTDS Microsoft SQL” message at the bottom of the screen.

6. Create the connection aliases:
– Click Aliases on the left
– Click the Plus symbol to add an alias
– Give it a name.
– Select Driver: jTDS Microsoft SQL
– URL:
– Something like this: jdbc:jtds:sqlserver://servername.domainname:1433/master
– User Name: (your user name without the domain part, e.g. username, not DOMAIN\username)
– Password: (leave this blank)
– Click Properties, then Driver properties
– Check Use driver properties
– Find Name: DOMAIN, check Specify next to it. Enter the domain Value: DOMAIN (change this to your domain name)
– Click OK
You can now connect using the alias. Double click it, and enter your password.

Advertisement