And now, in a break from the previous trend of fluffy posts, we have a tutorial on how to (deep breath): connect PHP to a MSSQL Server 2008 instance over ODBC from Ubuntu Linux using the FreeTDS driver and unixODBC. Theoretically it would also work for SQL Server 2005.
I don’t know whether half of the settings flags are necessary or even correct, but what follows Worked for Me™, YMMV, etc, etc.
In the commands below, I’ll use 192.168.0.1 as the server housing the SQL Server instance, with a SQL Server user name of devuser, password devpass. I’m assuming SQL Server is set up to listen on its default port, 1433. Keep an eye out, because you’ll need to change these things to your own settings.
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
Here are some other resources that were helpful to me through this disastrous journey.
- Nice step-by-step at LinuxJournal
- Sample installation (in a bug report!)
- Accessing Microsoft SQL Server from PHP (I found this after I was done, I wish I had earlier! He has luck getting it running simply by installing packages, which wouldn’t ever work for me.)