MSSQL connection from Ubuntu

Solution 1:

Here are step by step instructions (found here):

First, install unixODBC:

sudo apt-get install unixodbc unixodbc-dev

I also installed the following (perhaps necessary) packages:

sudo apt-get install tdsodbc php5-odbc

Then download, untar, compile, and install FreeTDS (warning, the URL may change):

cd /usr/local

wget http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar xvfz freetds-stable.tgz
cd freetds-0.82
./configure --enable-msdblib --with-tdsver=8.0 --with-unixodbc=/usr
make
make install
make clean

Attempt a connection over Telnet to your SQL Server instance:

telnet 192.168.0.1 1433

Use the tsql tool to test out the connection:

tsql -S 192.168.0.1 -U devuser

This should prompt you for the password, after which you can hope against hope to see this beautiful sign:

1>

If that worked, I recommend throwing a (coding) party. Next up is some configging. Open the FreeTDS config file. /usr/local/etc/freetds.conf

Add the following entry to the bottom of the file. We’re setting up a datasource name (DSN) called ‘MSSQL’.

[MSSQL]
host = 192.168.0.1
port = 1433
tds version = 8.0

Now open the ODBC configuration file: /usr/local/etc/odbcinst.ini

And add the following MSSQL driver entry (FreeTDS) at the end:

[FreeTDS]
Description = FreeTDS driver
Driver = /usr/local/lib/libtdsodbc.so
Setup=/usr/lib/odbc/libtdsS.so
FileUsage = 1
UsageCount = 1

Then, finally, set up the DSN within ODBC in the odbc.ini file here /usr/local/etc/odbc.ini By adding this bit to the file:

[MSSQL]
Description = MS SQL Server
Driver = /usr/local/lib/libtdsodbc.so
Server = 192.168.2.3
UID = devuser
PWD = devpass
ReadOnly = No
Port = 1433

Test out the connection using the isql tool:

isql -v MSSQL devuser 'devpass'

If you see “Connected!” you’re golden, congratulations! If not, I’m truly sorry; see below where there are some resources that might help.

Now restart Apache and test it from PHP using ‘MSSQL’ as the DSN. If something doesn’t work, you might try installing any or all of these packages: mdbtools libmdbodbc libmdbtools mdbtools-gmdb

Solution 2:

On Trusty 14.04 I had problems making http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

When I tried to run a tsql command, I found out tsql can be installed via apt:

$ sudo apt-get install freetds-bin