背景故事

Works :使MS SQLSRV PDO扩展程序在Windows 10本地开发环境中正常工作，可以连接到远程SQL Server&做工作.

The Backstory

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

失败:虽然可以在舞台上安装MS SQLSRV PDO扩展，但是这是在DigitalOcean上香草伪造部署的Ubuntu 16.04.3 x64，运行PHP 7.1.13和NGINX 1.13.6停滞暂存环境...无法使其连接到上述远程SQL Server.我可以从登台服务器ping SQL服务器.

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/zh-cn/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
https ://www.microsoft.com/zh-CN/sql-server/developer-get-started/php/ubuntu/step/2.html
https://github.com/Microsoft/msphpsql
可加载更多...

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

SSH进入登台:

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

通过任一sqlcmd进行的测试:

Tests from either sqlcmd:

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

...或php:

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

...失败，在W10本地主机开发环境中进行完全相同的尝试.

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

为什么在sqlsrv pdo上有这么多来自MSFT的不同官方文档以及如何实现... grr！

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

最初在分阶段使用PHP 7.2，但经过数小时的抨击却放弃了所有希望.使用7.1来启动新服务器，因为7.2还没有来自MS的稳定的sqlsrv pdo版本.

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.

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

这会产生相同的异常:

只是为了好玩，我尝试isql -v <server> <user> <pass>并得到了:

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

无法使SQLSRV

无法使SQLSRV PDO连接到远程SQL Server

问题描述

背景故事

The Backstory

推荐答案