PDO_ODBC and unixODBC cannot connect MSSQL with non-default port

3.3k views Asked by At

I have to connect two MSSQL servers on php/linux environment. So I decide to use unixODBC and PDO_ODBC.

  • mssql on server1: 10.10.10.5:1433

  • mssql on server2: 10.10.10.8:14233 (non-default port number)

I think there is a port problem of PDO_ODBC or unixODBC. I tried some code below.

This code works fine. connected successfully.

$db = new PDO('odbc:Driver=SQL Server Native Client 11.0; Server=10.10.10.5; Port=1433; Database=dbname; UID=uid; PWD=pwd');

This code didn't work. connect failed.

$db = new PDO('odbc:Driver=SQL Server Native Client 11.0; Server=10.10.10.8; Port=14233; Database=dbname; UID=uid; PWD=pwd');

Strangely, This code also works fine with wrong value. :(

$db = new PDO('odbc:Driver=SQL Server Native Client 11.0; Server=10.10.10.5; Port=14233; Database=dbname; UID=uid; PWD=pwd');

I concluded that someone ignore port setting on dsn of PDO.

I also tried to other setting using /etc/odbc.ini

[odbc-erp]
Driver=SQL Server Native Client 11.0
Description=MSSQL
Trace=Yes
Server=10.10.10.8
Port=14233
Database=dbname

And, this code didn't make connection.

$dbh = new PDO('odbc:odbc-erp', $uid, $pwd);

Error message:

DB Error: SQLSTATE[HYT00] SQLConnect: 0 [unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired in /home/user/public_html/test/testodbc.php on line 40

AND, this code works fine.

[odbc-mes]
Driver=SQL Server Native Client 11.0
Description=MSSQL
Trace=Yes
Server=10.10.10.5
Port=14233              // Wrong value (10.10.10.5 server port is 1433.)
Database=dbname

And, this code didn't make connection.

$dbh = new PDO('odbc:odbc-mes', $uid, $pwd);

The ports is all opened. tcpdump show the connection use always use ms-sql-s (1433). sqlcmd is works fine.

# sqlcmd -U uid -P pwd -S 10.10.10.8,14233 -d OES
1> select @@version
2> go

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) 
    Jul  9 2014 16:04:25 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


(1 rows affected)
1>

I wonder what I'm missing. Or is there the other way to connect with mssql?

  • CentOS 6.6
  • PHP 5.5.19
  • sqlncli-11.0.1790.0

-

# rpm -qa
php-5.5.19-2.el6.remi.x86_64
php-mssql-5.5.19-2.el6.remi.x86_64
php-odbc-5.5.19-2.el6.remi.x86_64
php-pdo-5.5.19-2.el6.remi.x86_64

# sqlcmd
Microsoft (R) SQL Server Command Line Tool
Version 11.0.1790.0 Linux
Copyright (c) Microsoft Corporation.  All rights reserved.

# cat /etc/sysconfig/selinux 
SELINUX=disabled
SELINUXTYPE=targeted 
1

There are 1 answers

1
Jeroen Mostert On BEST ANSWER

The driver doesn't support a Port keyword. Per the documentation, you need to append the port after the server name in the DSN:

Server=10.10.10.5,14233

The documentation also lists the keywords that are supported.