PHP: Connect to SQL Server Database on Linux (and Mac OS X)

Microsoft SQL Server
Microsoft SQL Server

I have used PHP to connect to SQL Server plenty of time, and every time I would use the SQLSRV extension. The problem with that is, PHP’s SQLSRV extension is only available for Windows servers. Which wasn’t a problem for me because I would normally develop PHP on Windows machines. It wasn’t until recently I needed SQL Server access on a Linux machine.

It wasn’t as straightforward as I had hoped, and it certainly did not help to learn that the MSSQL PHP extension was removed in version 7.0, which took me a few hours to discover. I am writing this post in hopes that I save some people a few hours of pain, including my future self.

I am going to assume you are using package managers Homebrew or Apt-get.

Install the requirements:

#Homebrew:
brew install freetds --with-unixodbc
#apt-get:
apt-get install tdsodbc
apt-get install unixodbc

Create template files for ODBC. Click here for better instructions:

# Save this file as tds.driver.template
# OSX
[FreeTDS]
Description = FreeTDS
Driver = /usr/local/freetds/lib/libtdsodbc.so
# Linux
[FreeTDS]
Description = FreeTDS
# Driver on Ubuntu 32 bit
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so

# Driver on Ubuntu 64 bit
# Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

# Driver on Ubuntu 8.04
# Driver = /usr/lib/odbc/libtdsodbc.so


# Save this file as tds.datasource.template
[MSSQLTestServer]
Driver = FreeTDS
Description = Northwind sample database
Trace = No
Server = 192.168.1.25
Port = 1433
Database = Northwind
TDS_Version = 8.0

Run the following commands, assuming you are in the same directory as the saved templates from above:

odbcinst -i -d -f tds.driver.template
odbcinst -i -s -f tds.datasource.template -l

A “gotcha” section from the link above is something that should be reviewed:

Gotchas

In FC4 I found that FreeTDS and unixODBC looks for odbc.ini and odbcinst.ini in the directory /usr/local/etc. Apache’s PHP5 looks for those two files in /etc. The ODBCConfig graphical program in KDE modifies those two files in /usr/local/etc. I recommend putting them in /usr/local/etc and creating symbolic links in /etc to those two files.

$ cd /etc
$ ln -s /usr/local/etc/odbc.ini odbc.ini
$ ln -s /usr/local/etc/odbcinst.ini

create symbolic links for PHP

That’s it! Just test to make sure it’s working:

isql -v MSSQLTestServer
SQL>

If the above works but you still have trouble connecting from PHP, make sure the ODBC extension is enabled. That will not be covered here.

For other methods of connecting PHP with FreeTDS, go here.

Leave a Reply

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