PHP – Connect to remote SQL Server on Linux Server

If you are using PHP hopefully you are running on Linux which means you probably won’t have the unfortunate task of having to connect to MS SQL Server. Although rare, there may come a time where you need to integrate with an outside system that uses a SQL Server database and no other options for getting data are viable (ie APIs).

This post will outline how to set this up. In this case I was using Ubuntu 14.04 LTS 64 bit but the instructions should be pretty close to earlier versions or 32 bit. These are what worked for me on a clean Virtual Box installation then replicated on a remote server. It is possible some of the commands are redundant or not needed. Please comment if you find some. These instructions assume you already have the LAMP stack installed.

1. Apt-Get Commands
   • sudo apt-get update
   • sudo apt-get install unixodbc tdsodbc php5-odbc freetds-dev unixodbc-dev freetds-bin
   • sudo apt-get update
   • sudo service apache2 restart
2. /freetds/freetds.conf – Here you can declare multiple server parameters if you need to have multiple connections. In this case we are using the connection defined with ‘sqlsrv’

   • 	# This file is installed by FreeTDS if no file by the same
     	# name is found in the installation directory.
     	#
     	# For information about the layout of this file and its settings,
     	# see the freetds.conf manpage "man freetds.conf".
     	# Global settings are overridden by those in a database
     	# server specific section
     	[global]
     	# TDS protocol version
     	; tds version = 4.2
     	# Whether to write a TDSDUMP file for diagnostic purposes
     	# (setting this to /tmp is insecure on a multi-user system)
     	; dump file = /tmp/freetds.log
     	; debug flags = 0xffff
     	# Command and connection timeouts
     	; timeout = 10
     	; connect timeout = 10
     	# If you get out-of-memory errors, it may mean that your client
     	# is trying to allocate a huge buffer for a TEXT field.
     	# Try setting 'text size' to a more reasonable limit
     	text size = 64512
     	# A typical Sybase server
     	[egServer50]
     	host = symachine.domain.com
     	port = 5000
     	tds version = 5.0
     	# A typical Microsoft server
     	[egServer70]
     	host = ntmachine.domain.com
     	port = 1433
     	tds version = 7.0
     	# Define SQL Server Connection
     	# TDS_Version is particular to the version of SQL Server.
     	[sqlsrv]
     	host = xx.xx.xx.xx
     	Port = 1433
     	TDS_Version = 8.0

     view raw freetds.conf hosted with by GitHub
3. /etc/odbcinst.ini

   • 	# Define where to find the driver for the Free TDS connections.
     	# Make sure you use the right driver (32-bit or 64-bit).
     	[ODBC]
     	Trace = Yes
     	TraceFile = /tmp/odbc.log
     	[freetds]
     	Description = Free TDS Driver
     	# 64 bit
     	Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
     	Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
     	# 32 bit
     	# Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
     	# Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so

     view raw odbcinst.ini hosted with by GitHub
4.  /etc/odbc.ini

   • 	# Define a connection to a Microsoft SQL server
     	# The Description can be whatever we want it to be.
     	# The Driver value must match what we have defined in /etc/odbcinst.ini
     	# The Database name must be the name of the database this connection will connect to.
     	# The ServerName is the name we defined in /etc/freetds/freetds.conf
     	# You can also declare Server = xx.xx.xx.xx where xx.xx.xx.xx is the server ip address or
     	# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
     	[mssql]
     	Description = MSSQL Server
     	Driver = freetds
     	Database = HECM_Data
     	# ServerName = sqlsrv
     	Server = xx.xx.xx.xx
     	Port = 1433
     	TDS_Version = 8.0

     view raw odbc.ini hosted with by GitHub
5. connect.php – PHP file to connect to database. This is just an example. You’d probably want to declare a database class and connect to the database as needed.

   • 	<?php
     	try {
     	$user = 'dbusername';
     	$pass ='dbuserpassword';
     	//the odbc connection must match the definition in /etc/odbc.ini
     	//In this example we defined mssql so the odbd connections is odbc:mssql
     	$mssqlpdo = new PDO("odbc:mssql", $user,$pass);
     	$mssqlpdo->setAttribute(PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
     	} catch (PDOException $e) {
     	$message = sprintf(
     	'Unable to connect to the database: %s: The error id is [%s]',
     	$e->getMessage(),
     	uniqid('ERROR-')
     	);
     	echo $e->getMessage();
     	}
     	try {
     	$sql = "SELECT TOP 10 * FROM People"; //generic query to test.
     	$query = $mssqlpdo->query($sql);
     	print_r($query->fetchAll());
     	} catch (PDOException $e) {
     	$message = sprintf(
     	'Query failed: %s: The error id is [%s]',
     	$e->getMessage(),
     	uniqid('ERROR-')
     	);
     	echo $e->getMessage();
     	}
     	?>

     view raw connect.php hosted with by GitHub