Install and configure ODBC Drivers on Linux – SLES

We ran into this issue that the proper MS SQL drivers for SLES/PHP are unavailable. There is no phpX-mssql package on the official development media to install, therefore we had to find an alternative solution to access and write data into an MS SQL database from our SLES server.

After some research we decided to set up ODBC on SLES to see if that is working. You need to follow the steps below to install and enable ODBC on your SLES Linux Server.

Install the odbc driver packages from the official SLES 11 Media using zypper.If you do not have a compiler installed also install gcc.

zypper install unixODBC unixODBC-devel gcc

Since there is no freetds package available on the installation media we need to compile that ourselves. Download the installation media from http://www.freetds.org/, upload it to your server and unpack it to a location of your choice. Run the following commands from the directory where you unpacked freedts.

./configure
 make
 make install

Now edit the tds configuration file called freetds.conf. Located in /usr/local/etc/ and add the following to the end of the file end of the file and modify the host, port and version number to your requirements.

[sql-server]
 host = 192.168.0.105
 port = 1433
 tds version = 7.0

Now test if you can connect to the database server. Make sure that the server listens on port 1433 and you have the relevant user id and password.

tsql -S sql-server -U sa -P ********
 locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C"
 locale charset is "UTF-8"
 using default charset "UTF-8"
 1>

If you do not see any error messages at this stage your connection is working. The next step is to modify the odbc configuration files. First we will do the drivers file. Edit the /etc/unixODBC/odbcinst.ini file by adding the following:

[sql-server]
Description = TDS Connection
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/lib64/unixODBC/libtdsS.so
UsageCount = 1
FileUsage = 1

The libtdsodbc.so and libtdsS.so files might be on a different location on your installation check them before you add the lines.

The last step is to add the server information into the odbc.ini file which is located also in /etc/unixODBC directory. Add the following and modify the database name and port if required.

[DsPcDb]
Description = odbc test driver
Driver = sql-server
Servername = sql-server
Database = DsPcDb
UID = sa
Port = 1433

Now run isql to check if your configuration is correct. You have to use the name of the odbc connection after the isql command. In our case this was DsPcDb.

HUITM001-P1:/etc/unixODBC # isql DsPcDb sa *********
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

If you see the “Connected!” state your setup was successful.

Leave a Reply

Your email address will not be published. Required fields are marked *