I’m getting “Login timeout expired” with the following connection attempt:
$dbx = new PDO("sqlsrv:Server=RemoteSQLServer;Database=TheDatabase", SQL_USER, SQL_PW);
If I add LoginTimeout:
$dbx = new PDO("sqlsrv:Server=RemoteSQLServer;Database=TheDatabase;LoginTimeout=60;", SQL_USER, SQL_PW);
The script returns a Bad Gateway error. I’m at my wits end and am unsure what I’m missing. Details below.
This is a RHEL 8.3 system with PHP 7.4.6 installed. RemoteSQLServer is a remote Windows system running SQLServer 2012. I’m able to successfully connect just to this instance from a RHEL 7 system with php-mssql (which isn’t available in RHEL 8 as far as I can tell).
To hopefully rule out any network issues, I’m able to connect to RemoteSQLServer using tsql from the RHEL 8 client with zero problem.
[user@rhel8client ~]# tsql -S RemoteSQLServer -U SQL_USER Password: locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1> select getdate(); 2> go Apr 19 2021 08:32:06:467PM (1 row affected) 1> quit [user@rhel8client ~]#
Related phpinfo details:
[user@rhel8client ~]# php -i | grep sqlsrv /etc/php.d/20-sqlsrv.ini, /etc/php.d/30-pdo_sqlsrv.ini, /etc/php.d/30-sqlsrv.ini, Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, compress.bzip2, phar, sqlsrv PDO drivers => mysql, sqlite, sqlsrv pdo_sqlsrv pdo_sqlsrv support => enabled pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240 pdo_sqlsrv.log_severity => 0 => 0 pdo_sqlsrv.report_additional_errors => 1 => 1 pdo_sqlsrv.set_locale_info => 2 => 2 sqlsrv sqlsrv support => enabled sqlsrv.ClientBufferMaxKBSize => 10240 => 10240 sqlsrv.LogSeverity => 0 => 0 sqlsrv.LogSubsystems => 0 => 0 sqlsrv.SetLocaleInfo => 2 => 2 sqlsrv.WarningsReturnAsErrors => On => On
ini files above:
[user@rhel8client ~]# cat /etc/php.d/20-sqlsrv.ini extension=sqlsrv.so [user@rhel8client ~]# cat /etc/php.d/30-sqlsrv.ini extension=sqlsrv.so [user@rhel8client ~]# cat /etc/php.d/30-pdo_sqlsrv.ini extension=sqlsrv.so
Related installed packages:
[user@rhel8client ~]# yum list installed | grep msodb msodbcsql17.x86_64 17.7.2.1-1 @System [user@rhel8client ~]# yum list installed | grep ODBC unixODBC.x86_64 2.3.7-1.el8 @rhel-8-for-x86_64-appstream-rpms unixODBC-devel.x86_64 2.3.7-1.el8 @rhel-8-for-x86_64-appstream-rpms [user@rhel8client ~]# pecl list Installed packages, channel pecl.php.net: ========================================= Package Version State pdo_sqlsrv 5.9.0 stable sqlsrv 5.9.0 stable
This post from 2019 asks a similar question but lone answer appears to leave out details on what to do after installing the packages they list (I’ve done that much see above).
I’m not sure what else I need to do here.
Is there any way to get PDO to use tsql?
Advertisement
Answer
I finally figured how to get a working connection from a RHEL8 machine to a Windows system running SQL Server 2012. This how-to was helpful.
First install a few needed packages
sudo dnf install php-odbc freetds freetds-devel
Go to your SQL Server 2012 machine and grab these variables:
SELECT @@servername AS 'Server Name'; # MYSERVERNAME SELECT @@servicename AS 'Instance Name'; # MSSQLSERVER
Issue this from prompt to get the libtdsodbc.so.0 path
ldconfig -p | grep libtdsodbc
[user@rhel8 ~]# ldconfig -p | grep libtdsodbc
-> libtdsodbc.so.0 (libc6,x86-64) => /lib64/libtdsodbc.so.0
libtdsodbc.so (libc6,x86-64) => /lib64/libtdsodbc.so
Edit /etc/odbcinst.ini and change the FreeTDS section to the following:
[FreeTDS] Description = FreeTDS Driver Driver = /lib64/libtdsodbc.so.0 # save & exit
Edit /etc/odbc.ini — this ini file defines DSN’s. Note that it may be blank to start with, that is OK
[ArbitraryServerID] Description = AppDatabase database on MYSERVERNAME Driver = FreeTDS Database = AppDatabase ServerName = ArbitraryServerID TDS_Version = 8.0 # save and exit
NOTE: “ArbitraryServerID” can be any string – just use it consistently across all of your configs
Edit /etc/freetds.conf to add the following section
[ArbitraryServerID]
host = mysqlserver.example.com
# host = 1.2.3.4
instance = MYSQLSERVER
port = 1433
tds version = 8.0
text size = 64512
client charset = UTF-8
The connection can now be tested using isql
isql -S ArbitraryServerID -U 'username' -P 'password'
Assuming that works OK, restart php-fpm
service php-fpm restart
In your PHP application, the PDO connection string should look something like:
$mssql_server = 'ArbitraryServerID';
$mssql_user = 'username';
$mssql_pw = 'password';
$dbx_mssql = new PDO("odbc:DRIVER=FreeTDS;SERVERNAME=$mssql_server;Database=AppDatabase", $mssql_user, $mssql_pw);
That’s it, you now have working ODBC connection to a remote SQLServer instance.