Trouble With Connecting to MSSQL DB

Question

I have an Ubuntu 12.04 server and I'm trying to establish a connection to a MSSQL database.

I've managed to connect using tsql and isql, but osql doesn't work and connecting with PHP using PDO also isn't working.. I will try to provide as much information as I can and if you need more just let me know and I will edit.

freetds.conf:

[MSSQL]
  host = TPSACC
  port = 54488
  tds version = 8.0

odbc.ini:

[MSSQL]
Description     = MS SQL connection to PRODUCTION database
Driver          = FreeTDS
Database        = PRODUCTION
Server          = TPSACC
UserName        = sa
Password        = pass
Trace           = No
TDS_Version     = 8.0
Port            = 54488

odbcinst.ini:

[FreeTDS]
Description = ODBC for Microsoft SQL
Driver      = /usr/local/lib/libtdsodbc.so
UsageCount  = 1
Threading   = 2

~> isql MSSQL sa pass

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> 

~> tsql -S MSSQL -U 'sa' -P 'pass'

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> 

~> osql -S MSSQL -U sa -P pass

checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
    trying /tmp/sql ... no
    trying /tmp/sql ... no
    trying /etc ... OK
checking odbc.ini files
    reading /home/toolplas/.odbc.ini
[MSSQL] not found in /home/toolplas/.odbc.ini
    reading /etc/odbc.ini
[MSSQL] found in /etc/odbc.ini
found this section:
    [MSSQL]
    Description     = MS SQL connection to PRODUCTION database
    Driver          = FreeTDS
    Database        = PRODUCTION
    Server          = TPSACC
    UserName        = sa
    Password        = pass
    Trace           = No
    TDS_Version     = 8.0
    Port            = 54488
looking for driver for DSN [MSSQL] in /etc/odbc.ini
  found driver line: "  Driver          = FreeTDS"
  driver "FreeTDS" found for [MSSQL] in odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /etc/odbcinst.ini
  found driver line: "  Driver      = /usr/local/lib/libtdsodbc.so"
  found driver /usr/local/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/usr/local/lib/libtdsodbc.so is an executable file
"Server" found, not using freetds.conf
Server is "TPSACC"
osql: no IP address found for "TPSACC"

In PHP I have:

$conn = new PDO ("dblib:host=TPSACC;dbname=PRODUCTION","$username","$pw");

..or..

$conn = new PDO ("dblib:host=TPSACC;port=54488;dbname=PRODUCTION","$username","$pw");

And they both return this error:

SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9) 

I have been stuck here for a couple days and can't quite figure out why only half of the connections actually work..

Any and all help is really appreciated, thanks!

EDIT: It is different than that question, I explained in a comment but will repost here:

For that one the problem was that the port was changed from 1433. Mine was also changed and I fixed that, the port is now 54488 and since that change tsql and isql have been working. However, it still doesn't solve the osql and PHP issues.

Answer 1

osql is choking on something in your configuration. osql is a debugging utility which simply checks out your configuration, then passes along to unixODBC's isql to connect (http://linux.die.net/man/1/osql). Try this for your odbc.ini:

[MSSQL]
Driver = FreeTDS
Description = MS SQL connection to PRODUCTION database
Server = tpsacc.yourfulldomain.com
Port = 54488
TDS_Version = 7.2
Database = PRODUCTION
UserName = sa
Password = pass
Trace = No

Also, are you sure that's the correct location for libtdsodbc.so? When I install freetds-dev with Ubuntu 14 x64 (utopic), it installs to /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so; example odbcinst.ini:

[FreeTDS]
Description = v0.91 with protocol v7.2
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

And for good measure, the typical freetds.conf I use:

# A typical Microsoft server
[MSSQL]
host = tpsacc.yourfulldomain.com
port = 54488
tds version = 7.2

FreeTDS only supports up to TDS version 7.2 with most languages. While using "8.0" shouldn't break anything, using 7.2 is better for consistency. If you need to check for reference, I have a Vagrant box with a full configuration available here, with examples: https://github.com/FlipperPA/django-python3-vagrant/ Good luck!