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?
-
# 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
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.