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:
brew install freetds --with-unixodbc
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
Description = FreeTDS
Driver = /usr/local/freetds/lib/libtdsodbc.so
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
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:
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
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.