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.
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:
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:
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.
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.
Note that a default DSN can be defined. The rest is now easy.
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.
When we click on ODBC, we should see a list of all defined data source names, including our SQL Server:
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.
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.
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.
