Can't get SQLSRV PDO to connect to remote SQL

Question

The Exception

"SQLSTATE[HYT00]: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Login timeout expired"

The Backstory

Works: Have the MS SQLSRV PDO extension working on Windows 10 local dev environment, can connect to remote SQL server & do work.

Fails: While can get MS SQLSRV PDO extension to install on staging, which is a vanilla Forge-deployed Ubuntu 16.04.3 x64 on DigitalOcean, running PHP 7.1.13 and NGINX 1.13.6 stagnig staging environment... Can't get it to connect to the aforementioned remote SQL server. I can ping the SQL server from the staging server.

Research: Been through lots of formal and informal documentation, E.g.:

• https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
• https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu/step/2.html
• https://github.com/Microsoft/msphpsql
• loads more...

Closest attempt:

SSH into staging:

sudo su 

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

apt-get update

ACCEPT_EULA=Y apt-get install msodbcsql=13.0.1.0-1 mssql-tools=14.0.2.0-1
apt-get install unixodbc-dev-utf16 #optional but recommended

ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd 
ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

apt install -y libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf #unixodbc unixodbc-dev (using msodbcsql)
pecl install sqlsrv
pecl install pdo_sqlsrv

phpversion="7.1"
echo "" >> /etc/php/$phpversion/fpm/php.ini
echo "# MS SQL Server Driver" >> /etc/php/$phpversion/fpm/php.ini
echo "extension=sqlsrv.so" >> /etc/php/$phpversion/fpm/php.ini
echo "extension=pdo_sqlsrv.so" >> /etc/php/$phpversion/fpm/php.ini
echo "" >> /etc/php/$phpversion/fpm/php.ini

echo "" >> /etc/php/$phpversion/cli/php.ini
echo "# MS SQL Server Driver" >> /etc/php/$phpversion/cli/php.ini
echo "extension=sqlsrv.so" >> /etc/php/$phpversion/cli/php.ini
echo "extension=pdo_sqlsrv.so" >> /etc/php/$phpversion/cli/php.ini
echo "" >> /etc/php/$phpversion/cli/php.ini
/etc/init.d/php$phpversion-fpm restart 

Tests from either sqlcmd:

sqlcmd -S xx.xx.xx.xx\INSTANCE -U username -P xxxxx

...or php:

$db = DB::connection( 'remoteSqlSrv' )->getPdo();

...fail, where the exact same attempts work on the W10 localhost dev env.

Why are there so many different official docs from MSFT on sqlsrv pdo and how to implement...grr!

Originally started with PHP 7.2 on staging, but abandoned all hope after a few hours of bashing. Spun up a new server with 7.1, as 7.2 doesn't have a stable sqlsrv pdo release from MS yet.

---

UPDATE

Updated Shell Script

sudo su

# Install dependencies
#
apt-get -f install
apt-get install libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev 

# Manually install msodbcsql 13.1 and mssql-tools 14.0.5
#   ODBC .deb found here -> https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
#   Tools .deb found here -> https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools
#   ... Are they compatible?
#
mkdir /debs
cd /debs
wget https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/msodbcsql_13.1.9.1-1_amd64.deb
wget https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/mssql-tools/mssql-tools_14.0.5.0-1_amd64.deb
dpkg -i msodbcsql_13.1.9.1-1_amd64.deb
dpkg -i mssql-tools_14.0.5.0-1_amd64.deb

# Link tools to sqlcmd
#
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# PHP 7.1 sqlsrv pdo extension
#
pecl install sqlsrv
pecl install pdo_sqlsrv

# Config php.ini for CLI & NGINX
#
phpversion="7.1"
phpini="/etc/php/$phpversion/fpm/php.ini"

echo "" >> $phpini
echo "# Extensions for Microsoft SQL Server Driver" >> $phpini
echo "extension=sqlsrv.so" >> $phpini
echo "extension=pdo_sqlsrv.so" >> $phpini
echo "" >> $phpini

phpini="/etc/php/"$phpversion"/cli/php.ini"

echo "" >> $phpini
echo "# Extensions for Microsoft SQL Server Driver" >> $phpini
echo "extension=sqlsrv.so" >> $phpini
echo "extension=pdo_sqlsrv.so" >> $phpini
echo "" >> $phpini

# Restart NGINX
#
/etc/init.d/php$phpversion-fpm restart 

# Shouldn't have to but just for good measure
#
shutdown -r now

# And last but not least, test SQL connection
#
sqlcmd -S xx.xx.xx.xx\instance -U username -P password

This yields the same exception:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2AF9. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Just for fun I tried to isql -v <server> <user> <pass> and got:

[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect

Answer 1

The issue was with our SQL server configuration, and not the Unix SQLSRV PDO @ https://github.com/Microsoft/msphpsql.

On our SQL server on Azure, static connections to dynamic ports without the UDP communication occurring on port 1434 were failing.

Assumptions are as a DOS mitigation with SQL Browser, without a solicitated communication (UDP) to SQL, to utilize dynamic TCP ports, those ports will refuse via an ICMP unreachable message.

Once TCP Dynamic Ports were cleared from the daughter NICs and parent IP ALL configurations, and a TCP Port declared on the IP ALL and active NIC, connections formed.