SQL Server on Linux: Using LibreOffice Base as a Client Application

Run sqlcmd on Linux or the SQL Server Managment Studio on Windows? As of November 2106, those are the only two choices provided by Microsoft to serve as a client to SQL Server vNext running on Linux. There are, however, other alternatives and one, in particular, might be attractive to folks looking for a graphical client for SQL Server that will run on Linux. The application is LibreOffice Base, the LibreOffice equivalent to MS Office Access. While Base can be used as an application database in its own right, it can, like Access, be used as a graphical interface for SQL Server. Before we can use Base as an SQL Server client, though, we must first connect. We can do this using either ODBC or JDBC. In this example, we will use ODBC.

Setting up ODBC Data Source Name (DSN)

ODBC data source names are defined in /etc/odbc.ini. If we look in /etc we also see odbcinst.ini which are where ODBC drivers are specified for the system. If you have not yet created any DSN definitions, the odbc.ini should be empty. After a fresh installation of SQL Server vNext on Linus, the odbcinst.ini file will look something like this:

etc/odbcinst.ini

We want to make note of two things: the name of the driver between the square brackets, and the physical location of the driver file, in this case in /opt. If we look in /opt we should see something like this:

/opt folder contents

It’s wise to make a copy of these folders. There is a bit of witchcraft associated with ODBC on Linux, and not the good kind. We will come to that in the next blog when we install RODBC.

Editing odbc.ini

It is my personal preference to edit privileged files in my home directory and then copy to /etc when I am finished. Here is an example of an odbc.ini file for SQL Server on Linux; the data source name defined is sqlservertest01. In general, you can omit the password and the ODBC Manager will prompt you for it at runtime.

etc/odbc.ini

Note that a default DSN can be defined. The rest is now easy.

Connecting LibreOffice Base

LibreOffice Base is not included in Ubuntu, but the Ubuntu Software application will be happy to install it for you. When you start Base, a wizard will ask if you would like to connect to an existing database or create a new one. We’ll create a new one that will serve only as a client for our SQL Server database.

base01

When we click on ODBC, we should see a list of all defined data source names, including our SQL Server:

base02

We will then have to define a client database. This is conceptually identical to creating a Microsoft Access database on Windows with linked tables in SQL Server.

Even if you have included security credentials in your DSN definition (a questionable practice at best), Base will require you to provide them.

base05

When we are finished, we can explore SQL tables, and design or write SELECT statements.

Base has a query designer, once again very analogous to that in the Management Studio. Base will also permit the editing and execution of SELECT statements from an edit window. But, for some reason, arbitrary SQL commands are not permitted in the edit window. It is unclear why the LibreOffice developers have limited this window to the editing of SELECT statements only.

Conclusion

It is hardly surprising that the Management Studio on Windows continues to be a superior tool for working with SQL Server, even when the SQL Server is running on Linux. However, if you would like to use a graphical tool to query SQL Server directly from the local machine, LibreOffice Base is a workable alternative.

In the course of connecting Base to SQL Server, we configured an ODBC Data Source Name (DSN). In the next installment, we will connect to SQL Server from the statistical program R using RODBC, and we shall see that ODBC on Linux can hold some ugly surprises.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.