Connecting to MS SQL server from OpenOffice Base using jTDS JDBC driver on linux

I have been using the Microsoft Enterprise Manager to connect to MS SQL server in order to test SQL queries before being used in applications etc. Clearly this is not an option on a linux platform. However, different methods for connecting to Microsoft SQL server exists. From Python e.g. the freeTDS driver can be used (not covered here). Sometimes a gui administration tool is nice to have. For this purpose I will cover how to connect to a MS SQL server using the jTDS JDBC driver in OpenOffoce Base. According to the website: “jTDS is an open source 100% pure Java (type 4) JDBC 3.0 driver for Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008)…”.

Download driver

The first step is to download the jTDS driver from http://sourceforge.net/projects/jtds/files/. Unpack the zip archive somewhere.

Install driver

Open OpenOffice base (on Ubuntu install it with sudo apt-get install openoffice.org-base). Create a new database and call it e.g. “test”

Pres next and choose not to register the db in openoffice base and press finish. Give the db a name. This db is simple used in order to access the ooBase gui in order to add the jTDS JDBC driver. Choose options in the “Tools” menu

Go to the java options and press the Class Path button.

Add the jtds-1.2.5.jar archive file by pressing the “Add archive” button.

Exit OpenOffice Base

Connect to database

Open OpenOffice Base. Choose to connect to existing database using JDBC

Click next.

In the data source URL type the following:
jtds:sqlserver://hostname:portnumber/databasename
Usually the port number is 1433. Hostname and database name depends on the actual setup.
In the JDBC driver class field type in
net.sourceforge.jtds.jdbc.Driver
Press the Test Class button. If you get a message that says “The JDBC driver was loaded successfully” the driver is OK. Click next to setup authentication.

Test the connection by clicking “Test connection”. If the connection was successful the following message box is displayed