1

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!

  • You're not using ODBC, with those connect strings. You're telling PHP to use the SQL Server-specific DBLIB connector, originally developed as Sybase (hence "Adaptive Server"). You may benefit from reviewing some documentation on PHP + ODBC. If you're targeting a recent SQL Server, you may also need a different ODBC driver, as FreeTDS doesn't yet fully support all the new features of SQL Server 2008 and later. My employer makes a few you can test for free. – TallTed Sep 6 '15 at 1:52
2

PHP modules installed?

Create a info.php file in your webserver (apache2 dir: /var/www/html in Ubuntu) with <?php phpinfo() ?> for view details. Use odbcinst -j for get config files loaded by unixODBC and tsql -C for get FreeTDS details; check if is MS db-lib source compatibility: yes.

If the MS db-lib is 'no' you need configure freetds with the --enable-msdblib.

My ./configure line:

./configure --with-tdsver=8.0 --with-unixodbc=/usr/local --enable-msdblib

Then sudo make && sudo make install

This is my working config:

Freetds.conf:

[MSSQL]
host = 192.168.1.200
port = 1433
tds version = 8.0
client charset = UTF-8

odbc.ini:

[mssql]
Description = MSSQL Server
Driver      = freetds #The odbcinst.ini driver name
ServerName  = MSSQL # The Freetds.conf connection name
Database    = database
TDS_Version = 8.0

odbcinst.ini

[freetds]
Description = MS SQL database access with Free TDS
Driver      = /usr/lib/libtdsodbc.so
Setup       = /usr/lib/x86_64-linux-gnu/odbclibtdsS.so
UsageCount  = 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.