Using Microsoft SQL Server from Linux Machines with ODBC & FreeTDS

FreeTDS is a set of libraries for Unix and Linux machines to use Microsoft SQL server and Sybase databases [Fre16]. FreeTDS C libraries are free, opensource, and distributed under GNU LGPL license. In this article, I will discuss about using MS SQL server located on a Windows machine which is installed using mixed mode authentication. The MS SQL server must be configured to allow remote connection with TCP/IP using username and password. That MS SQL server is to be connected and used from a Linux machine using FreeTDS.

MS SQL server is a result of cooperation between Microsoft and Sybase. Therefore, their SQL servers are quite similar and the communication protocols are almost identical. That protocol is called Tabular Data Stream (TDS). The project for open source implementation of TDS is started by Brian Bruns and it is called FreeTDS.

1. Installation
2. Testing
3. References

Installation

The following commands can be used to set up FreeTDS.

$ sudo apt update
$ sudo apt install unixodbc unixodbc-dev 
$ sudo apt install freetds-dev freetds-bin tdsodbc

The version of installed unixODBC can be checked as follow.

$ odbcinst --version

Configuration

ODBC drivers need to be registered for unixODBC. For that, edit '/etc/odbcinst.ini'

$ sudo nano /etc/odbcinst.ini

as follow.

[FreeTDS]
Description = FreeTDS v0.91 with protocol v7.3
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

That driver path is for desktop machines. For ARM based SBCs, the path should be as follow.

/usr/lib/arm-linux-gnueabihf/odbc/libtdsodbc.so

The drivers can be checked using the following command.

$ odbcinst -q -d

ODBC client applications normally works with ODBC Data Source Name (DSN). For that DSNs can be defined in /etc/odbc.ini.

$ sudo nano /etc/odbcinst.ini

For example, a DSN called 'mssqlserver' is created and its connection attributes (http://www.freetds.org/userguide/odbcconnattr.htm) can be defined as follows [Abb06]. For the driver, you can used the driver name which is defined in '/etc/odbcinst.ini' or the driver path can be put directly. To select the database, an example database 'mytest' is chosen here.

[mssqlserver]
Driver = FreeTDS
Description = Test database
Trace = No
Server = 192.168.1.2
Port = 1433
TDS_Version = 7.3
Database = mytest

Data sources can be listed as follow.

$ odbcinst -q -s

Testing

unixODBC has tools to test and use databases. As an example, a command line tool called isql is used. To connect the DSN that we just created, mssqlserver, use the following command. After the successful connection, SQL> prompt will be appeared. Then, we can use sql command like 'SELECT * FROM tablename' to test the database.

$ isql -v mssqlserver  
SQL>

Acknowledgement

Thanks to my colleague, Qi Chong, for introducing FreeTDS to me and helping me in getting started with it.

References

[Fre16] FreeTDS. FreeTDS - Making the leap to SQL Server. 2016.
url: http://www.freetds.org/.

[Abb06] Bob Abbott. unixODBC - MS SQL Server/PHP - with specifics for Red Hat/Fedora Linux Core 4 and Apache. 2006.
url: http://www.unixodbc.org/doc/FreeTDS2.html.