Installing and Configuring FreeTDS on Ubuntu 14.04.4 LTS and Establish a Connection via PHP PDO

freetds_install_ubuntu_14.md

Installing and Configuring FreeTDS on Ubuntu 14.04.4 LTS and Establish a Connection via PHP PDO

---

Install

sudo apt-get install -y unixodbc unixodbc-dev unixodbc-bin libodbc1 odbcinst1debian2 tdsodbc php5-odbc

sudo apt-get install -y freetds-bin freetds-common freetds-dev libct4 libsybdb5

Source: Configure unixODBC for Ubuntu 14.04 using FreeTDS

---

Configure

sudo vi /etc/odbcinst.ini

[ODBC]
Trace = No
TraceFile = /tmp/odbc.log

[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 1

sudo vi /etc/odbc.ini

[dbserverdsn]
Driver = FreeTDS
Server = <server_name>.database.windows.net
Port = 1433
Database = <database_name>
Driver=/usr/local/lib/libtdsodbc.so
UsageCount = 1

[Default]
Driver=/usr/local/lib/libtdsodbc.so

sudo vi /etc/freetds/freetds.conf

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# 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 = 7.2
  port = 1433

  # 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 Microsoft server
[dbserverdsn]
  database = <database_name>
  host = <server_name>.database.windows.net
  port = 1433
  tds version = 7.2
  client charset = UTF-8

---

Establish a Connection to a Microsoft Azure SQL Server Database via PHP PDO

$db_args = array(
  'driver' => 'odbc:DRIVER=FreeTDS',
  'server' => '<server_name>.database.windows.net',
  'port' => '1433',
  'database_name' => '<database_name>',
  'user' => '<database_user>',
  'password' => '<database_password>',
  'additional_parameters' => 'ForceEncryption=yes;',
);

// If the driver is FreeTDS (odbc:DRIVER=FreeTDS), set environment variables.
if($db_args['driver'] === 'odbc:DRIVER=FreeTDS') {
  putenv('TDSVER=80');
  putenv('FREETDSCONF=/etc/freetds/freetds.conf');
}

// Build the data source string.

// First, set the port, if it is present.
$port = isset($db_args['port']) ? ',' . $db_args['port'] : '';

// MS Azure needs the @server added to the UID.
$azure_uid_append = stristr($db_args['server'], '.database.windows.net') ? '@' . $db_args['server'] : '';

$data_source = $db_args['driver'] . ';';
$data_source .= 'SERVER=' . $db_args['server'] . $port . ';';
$data_source .= 'UID=' . $db_args['user'] . $azure_uid_append . ';';
$data_source .= 'PWD=' . $db_args['password'] . ';';
$data_source .= 'DATABASE=' . $db_args['database_name'] . ';';
$data_source .= $db_args['additional_parameters'];

try {
  // Connect to the data source and get a database handle for that connection.
  $dbh = new PDO($data_source, $db_args['user'], $db_args['password']);
  $stmt = $dbh->prepare($sql);
  $stmt->execute();
  $stmt->setFetchMode(PDO::FETCH_ASSOC);
  $data = $stmt->fetchAll();
} catch (PDOException $e) {
   $data['error'] = 'Failed to connect: ' . $e->getMessage();
}