SQuirreL SQL: a nice database GUI frontend for Ubuntu

Posted: 15 August 2011 by Robert Hutton in System Administration
Tags: , , , , , , , , , , , , , , , , ,

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.

Advertisements
Comments
  1. xylene says:

    Excellent instructions, thank you!
    The only problem is that SquirrelSQL is an obfuscated, piece of junk aptly named to dive a user nuts. Aliases? Drivers?? This thing is crude. I spent an hour trying to get it to connect to a mysql database on my localhost.

  2. Well, it’s certainly a little arcane, but I think “piece of junk” is going a little too far. It suffers from being too deeply invested in the old Java nomenclature and uses the clunky old Swing UI, but once you’ve worked it out, it is a very versatile piece of kit.

    But then again, I’m a command-line client kind of guy. 😉

  3. Gorm says:

    what if the server is located at a workgroup ?

  4. Hi Gorm,

    It depends on the type of authentication you want to use, integrated security or standard mode. For integrated security, specify the workgroup in the DOMAIN property in the connection alias. For standard mode, don’t specify it.

    You can work out what authentication mode you need to use by opening up the SQL Server Management Studio, and then expanding the Databases node, right clicking on the database in question and doing properties. Under Permissions, you’ll see a list of users, and the username will either be preceeded by a domain in the form of DOMAIN\username (integrated security) or just be a naked username (standard mode).

    Regards,

    Rob

  5. Homer Dong says:

    Fabulous instructions, you saved my day.

  6. Geno says:

    sudo chown root:root SQuirreL\ SQL.desktop

    please explain?

    • Hi Geno,

      We’re moving the desktop file into /usr/local/share/applications and changing its ownership and group to “root”. This effectively installs it “system wide”, making it visible to all users. Note that the file has a space in its name, so the backslash escapes that. Here is another way of doing this that is perhaps a little easier to understand:

      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”

      Regards,

      Rob

      • Derrick Kirkland says:

        Ok, does anyone know why other users can use Squirrel but they all can’t see the aliases that I set up. How can I make it so that all users can see my aliases? Is that possible? I have administrator privileges.

  7. Connecting from Linux into a MS domain, I found the connection string worked better if I used the MSSQL server’s IP address instead of a name, and ignore the MS domain name

    e.g.
    jdbc:jtds:sqlserver://192.168.0.199:1433;databaseName=STATS

  8. avaya says:

    works beautifully. Thanks

  9. You can check free tool Valentina Studio http://www.valentina-db.com/en/valentina-studio-overview which I think is far superior to other GUI tools.

  10. PAscal says:

    SQLeo could also help

  11. Flávio says:

    thank you

  12. Minhaj A. Mohammad says:

    Has anyone document of SQuirreL SQL for postgres installation on ubuntu 14.04?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s