FreeTDS not connecting to new MSSQL server on Ubuntu 14.04

Herewith my account in fixing FreeTDS connecting to MSSQL server on our Ubuntu 14.04 server.

Few months back I setup for my work FreeTDS on our Ubuntu server to connect to our in office MSSQL 2012 server. Pulling data from MSSQL from to an PHP app. Everything worked fine until the database was moved to a new MSSQL 2014 server.

Original setup that worked came from https://gist.github.com/ghalusa/97bf0b45a27d6b025d670752a7c62ec6. The original process was easy to install, but now I must fix the setup.

Copy of Original setup instructions which worked on the MSSQL 2012 setup.

Install on Ubuntu server

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

Configure Ubuntu server

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 = .database.windows.net
Port = 1433
Database = 
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 = 
  host = .database.windows.net
  port = 1433
  tds version = 7.2
  client charset = UTF-8

My changed code from the example to test PHP PDO Connectivity to the MSSQL server.

$db_args = array(
  'driver' => 'odbc:DRIVER=FreeTDS',
  'server' => '192.168.55.55',
  'port' => '1433',
  'database_name' => 'database',
  'user' => 'user',
  'password' => 'pass',
 // 'additional_parameters' => 'ForceEncryption=no;',
);

// 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('select * from table');
  $stmt->execute();
  $stmt->setFetchMode(PDO::FETCH_ASSOC);
  $data = $stmt->fetchAll();
  echo 'Reach this point';
  print_r($data);
} catch (PDOException $e) {
   $data['error'] = 'Failed to connect: ' . $e->getMessage();
}

Identifying the problem

Manualy adding the Server detail into the freetds.conf and odbc.ini gives me the following error.

tsql -S SERVERNAME -U sa

locale is "en_ZA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20009 (severity 9):
        Unable to connect: Adaptive Server is unavailable or does not exist
        OS error 111, "Connection refused"
There was a problem connecting to the server

With some investigation I found the following command that shows the setup of the new server.

tsql -LH SERVER_IP_ADDRESS


     ServerName SERVERNAME
   InstanceName SQLEXPRESS
    IsClustered No
        Version 13.0.1601.5

     ServerName SERVERNAME
   InstanceName INSTANCE2
    IsClustered No
        Version 12.0.2000.8
            tcp 63820
             np \\SERVERNAME\pipe\MSSQL$INSTANCE2\sql\query

There is 2 instances on the new server "SQLEXPRESS" and "INSTANCE2" I need to connect to "INSTANCE2". So the problem is, the above example is not correctly setup to point to the correct instance.

After lots of testing the answer came form the following link https://stackoverflow.com/questions/7831137/changing-sql-server-named-instance-to-default-instance

The "SQLEXPRESS" instance was installed first so it received the default port 1433, "INSTANCE2" I was trying to connect to was assigned a different port because it was installed second. I could have actualy seen it looking at the following line from the above "tsql -LH" command.

tcp 63820

In my scenario the actual "SQLEXPRESS" instance is actualy disabled so its not in use. So I made a choice to change the "SQLEXPRESS" port to something else and change the "INSTANCE2" port to 1433 which is the default port.

It should be possible to just change the connection port in your FreeTDS settings to the different port but I did not test it.

How to change the MSSQL Instance port.

Go into the "SQL Server Configuration Manager".
open "SQL Server Network Configuration".
Press on "Protocols for [INSTANCENAME]".
Right click on "TCP/IP" and press "Properties"
Choose "IP Addresses" tab.
Go to "IPAII" and change the port at "TCP Dynamic Ports".
You first change the port of "SQLEXPRESS" to something else, then you change the port for "INSTANCE2" to 1433.
Then restart the instance service.

Change of MSSQL port screenshot

Disclaimer: We accept no liability for any loss or damage caused by the info in this article. Please feel free to Contact Us if there is incorrect information.