Day 2 Solution

#https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

apt-get install update

apt-get install software-properties-common apt-utils

add-apt-repository ppa:ondrej/php -y
apt-get update
apt-get install php7.2 php7.2-dev php7.2-xml -y –allow-unauthenticated

apt-get install gnupg gnupg2 gnupg1
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add –
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql17
ACCEPT_EULA=Y apt-get install mssql-tools

echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ » ~/.bash_profile
echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ » ~/.bashrc
source ~/.bashrc

apt install -y unixodbc-utf16 unixodbc-dev-utf16 2> /dev/null
sudo apt-get install php-pear
sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv

#had to modify the location
phpini=”/usr/local/lib/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

#I didn’t had CLI?!
#phpini=”/etc/php/7.2/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

echo “Installing Microsoft ODBC Driver for SQL Server”

# Originally from: http://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/
apt install -y libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev

#doesn’t work: $basedir is not set, but also none of the (sub)directories exist on my setup
#sudo ACCEPT_EULA=Y dpkg -i $basedir/config/php-extensions/mssql/bin/debs/current/*.deb

# Create symlinks for tools
#had to change the source filenames to sqlcmd and bcp because with -13.0.1.0 suffix the files were not found
ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

service php7.2-fpm restart
#https://github.com/Microsoft/msphpsql/issues/522

or

<span class="pln">
https://stackoverflow.com/questions/48651225/cant-get-sqlsrv-pdo-to-connect-to-remote-sql-server
</span><span class="com"># Install dependencies</span>
<span class="com">#
</span><span class="pln">
apt</span><span class="pun">-</span><span class="kwd">get</span> <span class="pun">-</span><span class="pln">f install
apt</span><span class="pun">-</span><span class="kwd">get</span><span class="pln"> install libc6 libstdc</span><span class="pun">++</span><span class="lit">6</span><span class="pln"> libkrb5</span><span class="pun">-</span><span class="lit">3</span><span class="pln"> libcurl4 openssl debconf unixodbc unixodbc</span><span class="pun">-</span><span class="pln">dev 

</span><span class="com"># Manually install msodbcsql 13.1 and mssql-tools 14.0.5</span>
<span class="com">#   ODBC .deb found here -> https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server</span>
<span class="com">#   Tools .deb found here -> https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools</span>
<span class="com">#   ... Are they compatible?</span>
<span class="com">#</span><span class="pln">
mkdir </span><span class="pun">/</span><span class="pln">debs
cd </span><span class="pun">/</span><span class="pln">debs
wget https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/msodbcsql17/msodbcsql17_17.2.0.1-1_amd64.deb</span><span class="pln">
wget </span><a href="https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/mssql-tools/mssql-tools_17.2.0.1-1_amd64.deb"><span class="com">https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/mssql-tools/mssql-tools_17.2.0.1-1_amd64.deb</span></a><span class="pln">
dpkg </span><span class="pun">-</span><span class="pln">i </span><span class="pln">msodbcsql17_17.2.0.1-1_amd64.deb
dpkg </span><span class="pun">-</span><span class="pln">i </span><span class="pln"><a href="https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/mssql-tools/mssql-tools_17.2.0.1-1_amd64.deb"><span class="com">mssql-tools_17.2.0.1-1_amd64.deb</span></a>

</span><span class="com"># Link tools to sqlcmd</span>
<span class="com">#</span><span class="pln">
echo </span><span class="str">'export PATH="$PATH:/opt/mssql-tools/bin"'</span> <span class="pun">>></span> <span class="pun">~/.</span><span class="pln">bash_profile
echo </span><span class="str">'export PATH="$PATH:/opt/mssql-tools/bin"'</span> <span class="pun">>></span> <span class="pun">~/.</span><span class="pln">bashrc
source </span><span class="pun">~/.</span><span class="pln">bashrc

</span><span class="com"># PHP 7.2 sqlsrv pdo extension</span>
<span class="com"># </span><span class="pln">
apt-get install php-pear php7.2-dev
pecl install sqlsrv
pecl install pdo_sqlsrv

</span><span class="com"># Config php.ini for CLI & NGINX</span>
<span class="com">#</span><span class="pln">
phpversion</span><span class="pun">=</span><span class="str">"7.2"</span><span class="pln">
phpini</span><span class="pun">=</span><span class="str">"/etc/php/$phpversion/fpm/php.ini"</span><span class="pln">

echo </span><span class="str">""</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">"# Extensions for Microsoft SQL Server Driver"</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">"extension=sqlsrv.so"</span> <span class="pun">>></span><span class="pln"> $phpini
#echo </span><span class="str">"extension=pdo_sqlsrv.so"</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">""</span> <span class="pun">>></span><span class="pln"> $phpini

phpini</span><span class="pun">=</span><span class="str">"/etc/php/"</span><span class="pln">$phpversion</span><span class="str">"/cli/php.ini"</span><span class="pln">

echo </span><span class="str">""</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">"# Extensions for Microsoft SQL Server Driver"</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">"extension=sqlsrv.so"</span> <span class="pun">>></span><span class="pln"> $phpini
#echo </span><span class="str">"extension=pdo_sqlsrv.so"</span> <span class="pun">>></span><span class="pln"> $phpini
echo </span><span class="str">""</span> <span class="pun">>></span><span class="pln"> $phpini

</span><span class="com"># Restart NGINX</span>
<span class="com">#</span>
<span class="pun">service </span><span class="pln">php$phpversion</span><span class="pun">-</span><span class="pln">fpm restart 

</span><span class="com"># Shouldn't have to but just for good measure</span>
<span class="com">#</span><span class="pln">
shutdown </span><span class="pun">-</span><span class="pln">r now

</span><span class="com"># And last but not least, test SQL connection</span>
<span class="com">#
</span>

apt-get install -y locales     && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen     && locale-gen

<span class="pln">chmod -R o+rx /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.
0.1

</span>

<pre><code>apt-get update

<p>
  Now lets fix the naming of the file by creating a link:
</p>

<pre><code>cd /lib/x86_64-linux-gnu

cp /etc/php/7.2/cli/conf.d/30-pdo_sqlsrv.ini /etc/php/7.2/fpm/conf.d

<span class="pln"> sqlcmd </span><span class="pun">-</span><span class="pln">S </span><span class="pln">30daysofazure-sqlserver.database.windows.net</span><span class="pun"> -</span><span class="pln">U </span><span class="pun"><span class="pln">chunsiongtan</span> </span>

make sure that <span class="str">extension=pdo_sqlsrv.so</span> is loaded in 30-pdo_sqlsrv.ini instead of php.ini

debugging do ldd pdo_sqlsrv.so or any other .so files