Msphpsql: Instructions for Installing on Nginx / PHP-FPM

[benyanke]

Many people also serve on Nginx + PHP-FPM. Would love a tutorial on that as well!

[Hadis-Knj]

@benyanke we'll look into your suggestion

[benyanke]

I know you mentioned not taking PRs for the code, but if I were to write it, would you accept? I now have it working on NGINX.

[Hadis-Knj]

yeah would be very appreciated, we take PRs for both code and tutorials, make sure you send your PR on dev branch

[Hadis-Knj]

You can alternatively add a page in our wiki
https://github.com/Microsoft/msphpsql/wiki

[benyanke]

I haven't had time to do a full writeup yet, but here's the relevant part of my Vagrant dev box provisioning script (run as root). If I don't have time to do a write up, perhaps someone else while, since I've solved the technical side.

This assumes a typical working phpfpm setup with NGINX is already in place.

This setup has me connecting to my MSSQL database in nginx using PHP 7.0.x.

    sudo apt install -y unixodbc-dev

    # unixodbc-dev-utf16 is optional but recommended according to MS
    # This package is gone from repos as of sept 19, 2017. Leaving it here and 
    # just piping errors to null in case it returns (hopefully).
    sudo apt install -y unixodbc-dev-utf16 2> /dev/null

    sudo pecl install sqlsrv
    sudo pecl install pdo_sqlsrv


    phpini="/etc/php/7.0/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/7.0/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/
     sudo apt install -y libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev
     sudo ACCEPT_EULA=Y dpkg -i $basedir/config/php-extensions/mssql/bin/debs/current/*.deb

     # Create symlinks for tools
     sudo ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
     sudo ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

     sudo service php7.0-fpm restart

[Hadis-Knj]

Thanks @benyanke very appreciated :) I will review and write it up as soon as I have some spare time

[Glideh]

@Hadis-Fard Thanks for the wiki about sqlsrv with php-fpm in Docker
Helped me a lot !

[Hadis-Knj]

@Glideh glad it could help you :blush:

[Fever905]

This is not yet working for php 7.1 ?

[JanOonk]

@benyanke I tried your script on a Ubuntu 16.04 server + php7-fpm + nginx.
I had to modify your script to get it to run:

    sudo apt install -y unixodbc-dev

    # unixodbc-dev-utf16 is optional but recommended according to MS
    # This package is gone from repos as of sept 19, 2017. Leaving it here and 
    # just piping errors to null in case it returns (hopefully).

    #had to add an extra dependency unixodbc-utf16
    sudo apt install -y unixodbc-utf16 unixodbc-dev-utf16 2> /dev/null

    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.0/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/
     sudo 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
     sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
     sudo ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

     #sudo service php7.0-fpm restart
     sudo /etc/init.d/php-fpm restart 

[benyanke]

Thanks for that - I didn't have a ton of time to do cleanup of my script but wanted to get it out there for others who might find it useful, even if it needs a bit of work - the path with the *.deb file was just where I put the releases from this github repo. Thanks for doing the cleanup work.

I still plan (when I have time - product launch is this week, then more down time at work) to do a full writeup for the repo readme for setting up the driver with phpfpm.

[yukiwongky]

Hello @benyanke , @JanOonk , thank you for all your input. We have already put testing Nginx and writing an official tutorial on it in the backlog. We will work on it soon after we have released our RTW.

[ychdev]

Having a baaaaad time getting the SQLSRV PDO running on Ubuntu 16.4 | NGINX | PHP 7.1...
details at stack overflow

[benyanke]

@ychdev - I'm using this in my laravel env to - did you try the above script I gave? I can confirm that the driver does work with php 7.x on both ubuntu 16.04 and amazon linux.

[ychdev]

@benyanke your script yields this result at the end:

"SQLSTATE[IMSSP]:
This extension requires the Microsoft ODBC Driver 13 for SQL Server to communicate with SQL Server.
Access the following URL to download the ODBC Driver 13 for SQL Server for x64:
http://go.microsoft.com/fwlink/?LinkId=163712"

Note that I had the same problem as @JanOonk , where this line won't run:
sudo ACCEPT_EULA=Y dpkg -i $basedir/config/php-extensions/mssql/bin/debs/current/*.deb

Curiously, why install unixodbc-dev twice?

[benyanke]

did you place a current .deb file in the directory specified? I am using this one:

http://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/msodbcsql_13.1.9.0-1_amd64.deb

[benyanke]

Also, not sure why I installed unixodbc-dev twice. Probably just an oversight.

[ychdev]

Didn't know I had to place a current .deb file anywhere, or where I would get it from or put it when located... What's that about? I'm just a lowly IIS guy who has to connect a PHP app to a MSSQL db... :'(

See anything horribly wrong with this hybrid of the official MSFT shell + yours?

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 

[benyanke]

Mea culpa, that was completely my fault for forgetting to mention. I had to really hack this together to get it to work, and I had forgotten. This just runs in my repo build now, so I take for granted that the dev is there.

Try again, replacing $basedir/config/php-extensions/mssql/bin/debs/current/*.deb with the path to that DEB file you have to download, and give it a try.

[ychdev]

I didn't have to download any DEB files manually (then again, it's not working yet)... but isn't that what's happening here in the script?

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

[benyanke]

That is using the microsoft repos, which I found didn't work well for me. If you download the deb directly here: http://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/msodbcsql_13.1.9.0-1_amd64.deb I know it will work, at least it does for me.

[ychdev]

Okay I'll try that route, nothing to lose but a few minutes of restoring a vm snapshot and some hair.

[benyanke]

Don't feel bad - the package is far from mature. It took me the better part of a month to get working. And I've been a linux admin and dev for years.

[ychdev]

Didn't feel _too_ bad, yet.. until you said that! #lol

[ychdev]

@benyanke - updated script based on our dialogue... still no dice after restoring vm snapshot and trying fresh with several different versions of the odbc driver. Unsure what is left to try.

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

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

[ychdev]

Also, can't install https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/msodbcsql_13.0.1.0-1_amd64.deb because it depends on package unixodbc-utf16 which has no installation candidate (as mentioned above). Figured I'd try V1 since V-latest isn't working...

This is the first package that works without requiring the utf16 dependency: https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/msodbcsql/msodbcsql_13.1.3.0-1_amd64.deb

[ychdev]

Following just the "install SQL" steps in part one from here: https://sqlchoice.azurewebsites.net/en-us/sql-server/developer-get-started/php/ubuntu/ - I can connect to the local SQL server, but still not the remote SQL server: https://ibb.co/ck01Qc

[benyanke]

@ychdev You're using 16.04, right?

Have you tried running sudo apt update ; sudo apt install unixodbc-utf16 -y before running the deb command?

[ychdev]

@benyanke yes sir - though unixodbc-utf16 has no installation candidate, right?

[ychdev]

Gah!

The issue was with our SQL server configuration, and not the Unix SQLSRV PDO.

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.

[JanOonk]

@ychdev I had the exact same issue (and found the same solution as you) a while ago and took me a whole day to figure it out. Hopefully you found/read my post https://github.com/Microsoft/msphpsql/issues/470 it would have saved you alot of trouble.

[ychdev]

@JanOonk we had seen your post during my our research, thank you!

[sccmrb]

Sorry to revive a dead thread. I'm having major issues getting sqlsrv to work with php7.2-fpm and nginx on ubuntu 18.04 as a docker image. It says everything installs correctly, I can see the blocks for sqlsrv and pdo_sqlsrv in the php info page but it absolutely will not connect. I have a test page that works on apache2.4, php7.2 with sqlsrv installed on this same server outside of the container but the same file when accessed from inside the container refuses to connect and sqlsrv_connect() returns false.

I can run nc -z -v -w5 _myipaddress_ 1433 inside and outside of the container and get the desired result of: Connection to _myipaddress_ 1433 port [tcp/ms-sql-s] succeeded!

The hangup seems to be somewhere in the build process which follows Microsoft's tutorials for installing on ubuntu.

[yitam]

@sccmrb if possible, please try to
(1) run sqlcmd or isql
(2) provide an ODBC trace

[benyanke]

@sccmrb I'm working on a docker image right now with an open source base image. I'll post my results here if I do (or don't) get it working for you to use as-is or use as a base for your work.

[sccmrb]

@benyanke This is my current working Dockefile. Feel free to remove the "COPY" instructions as they are just dumping in my own configs as a start point. Also feel free to remove Oracle install stuff.

FROM ubuntu:18.04
# Forward request logs to Docker log collector
#RUN ln -sf /dev/stdout /var/log/nginx/access.log && ln -sf /dev/stderr /var/log/nginx/error.log
ENV TZ=America/Denver
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone
ENV DEBIAN_FRONTEND noninteractive
ENV DEBIAN_FRONTEND teletype

RUN mkdir -p /run/php/ \
&& mkdir -p /etc/php/7.2/fpm/ \
&& touch /run/php/php7.2-fpm.sock \
&& touch /etc/odbcinst.ini
COPY php.ini /etc/php/7.2/fpm/php.ini>
# General image, nginx and php-fpm7.2
RUN apt update --fix-missing \
&& apt-get install -y apt-utils gnupg curl build-essential libaio1 php-pear supervisor software-properties-common locales iputils-ping --no-install-recommends \
&& add-apt-repository ppa:ondrej/php \
&& add-apt-repository ppa:ondrej/nginx-mainline \
&& apt-get update \
&& apt-get install -y apt-transport-https lsb-release ca-certificates --no-install-recommends \
&& echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
&& locale-gen \
&& dpkg-reconfigure locales \
&& mkdir -p /var/log/supervisor \
&& apt update \
&& apt-get install -y nginx php7.2-fpm php7.2-dev php7.2-xml php7.2-bcmath php7.2-bz2 php7.2-curl php7.2-dba \
php7.2-gd php7.2-imap php7.2-intl php7.2-json php7.2-ldap php7.2-mbstring php7.2-mysql php7.2-odbc php7.2-opcache \
php7.2-pspell php7.2-readline php7.2-soap php7.2-sqlite3 php7.2-tidy php7.2-xmlrpc php7.2-zip \
php7.2-cli php-memcached php7.2-common --no-install-recommends --allow-unauthenticated\
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*\
&& pecl config-set php_ini /etc/php/7.2/fpm/php.ini \
&& pear config-set php_ini /etc/php/7.2/fpm/php.ini

# Oracle OCI8 php driver install
RUN mkdir -p /opt/oracle
COPY instantclient /opt/oracle/instantclient
RUN cd /opt/oracle/instantclient/\
&& ln -s libclntsh.so.12.1 libclntsh.so\
&& ln -s libocci.so.12.1 libocci.so\
&& chown -R root:www-data /opt/oracle\
&& echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient.conf \
&& echo "LD_LIBRARY_PATH=\"/opt/oracle/instantclient\"" >> /etc/environment \
&& echo "ORACLE_HOME=\"/opt/oracle/instantclient\"" >> /etc/environment \
&& ldconfig \
&& pecl channel-update pecl.php.net \
&& echo "instantclient,/opt/oracle/instantclient" | pecl install oci8

# Microsft SQL driver install
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-tools.list
RUN apt-get update
RUN echo 'y' | ACCEPT_EULA=Y apt-get install msodbcsql17 mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN apt-get install -y unixodbc-dev
RUN pecl install sqlsrv pdo_sqlsrv
RUN echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini
RUN echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini
RUN echo "extension=pdo_sqlsrv.so" >> /etc/php/7.2/fpm/conf.d/30-pdo_sqlsrv.ini
RUN echo "extension=sqlsrv.so" >> /etc/php/7.2/fpm/conf.d/20-sqlsrv.ini
   # removes duplicate extension=pdo_sqlsrv.so and sqlsrv.so from the top of php.ini after installation
RUN sed -i '1d' /etc/php/7.2/fpm/php.ini
RUN sed -i '1d' /etc/php/7.2/fpm/php.ini
RUN apt-get clean \
&& rm -rf /var/lib/apt/lists/*
# Adding trace to test connectivity
RUN printf "Trace=Yes\nTraceFile=/odbctrace.log" >> /etc/obdcinst.ini

EXPOSE 80

STOPSIGNAL SIGTERM

COPY supervisord.conf /etc/supervisor/supervisord.conf
CMD ["/usr/bin/supervisord", "-n", "-c", "/etc/supervisor/supervisord.conf"]

[benyanke]

Related: #847

[benyanke]

It seems that according to the other thread that 18.04 use is not possible with the ondrej ppa which provides php7.3.

Also, I suggest putting your dockerfile in markdown code block (https://en.support.wordpress.com/markdown-quick-reference/)

[sccmrb]

No, I can install php7.2 just fine.

[sccmrb]

And I've tried it with and without the ondrej ppa and it made no difference.

[benyanke]

Here's my images. I got it to build, now trying to get the connection running:

https://github.com/benyanke/docker-php7

[sccmrb]

@benyanke So I merged your Docker file with mine, added libaio1 for the Oracle OCI8 module and things seem to be working great! Thank you. I think the biggest issue was the --no-install-recommends on line that installs php because I put that back into your file and broke it, so at least I've narrowed down what it is.

Below is my working Dockerfile

FROM ubuntu:18.04

MAINTAINER "Ben Yanke" <[email protected]>

WORKDIR /tmp
ENV TZ=America/Denver
RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone

RUN mkdir -p /run/php/ \
&& mkdir -p /etc/php/7.2/fpm/ \
&& touch /run/php/php7.2-fpm.sock
COPY php.ini /etc/php/7.2/fpm/php.ini

# Installing base packages, adding PHP repo
RUN apt-get update -y && \
    DEBIAN_FRONTEND=noninteractive \
    apt-get install -y \
    software-properties-common curl \
    supervisor iputils-ping libaio1 \
    php-pear apt-transport-https locales gnupg wget --no-install-recommends && \
    LC_ALL=C.UTF-8 add-apt-repository ppa:ondrej/php -y && \
    add-apt-repository ppa:ondrej/nginx-mainline -y

# Install dependencies for MS SQLSRV PHP Driver
RUN 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 -yqq && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev unixodbc --no-install-recommends && \
    rm -r /var/lib/apt/lists/*

# Install Common PHP modules
RUN apt-get update -y && \
    DEBIAN_FRONTEND=noninteractive \
    apt-get install -y \
    zip \
    unzip \
    php7.2-fpm \
    php7.2-cli \
    php7.2-mysqlnd \
    php7.2-apcu \
    php-memcached \
    php7.2-imap \
    php7.2-pgsql \
    php7.2-intl \
    php7.2-mbstring \
    php7.2-redis \
    php7.2-sqlite \
    php7.2-curl \
    php7.2-json \
    php7.2-gd \
    php7.2-xml \
    php7.2-zip \
    php7.2-soap \
    php7.2-bcmath \
    php7.2-dev \
    php7.2-ldap \
    php7.2-bz2 \
    php7.2-json \
    php7.2-odbc \
    php7.2-opcache \
    php7.2-pspell \
    php7.2-readline \
    php7.2-tidy \
    php7.2-xmlrpc\
    php7.2-common \
    php-pear \
    make \
    wget \
    git \
    nginx \
    curl && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

RUN pecl config-set php_ini /etc/php/7.2/fpm/php.ini && \
    pear config-set php_ini /etc/php/7.2/fpm/php.ini && \
    mkdir -p /var/log/supervisor && \
    pecl channel-update pecl.php.net

# Oracle OCI8 php driver install
RUN mkdir -p /opt/oracle
COPY instantclient /opt/oracle/instantclient
RUN cd /opt/oracle/instantclient/ && \
    ln -s libclntsh.so.12.1 libclntsh.so && \
    ln -s libocci.so.12.1 libocci.so && \
    chown -R root:www-data /opt/oracle && \
    echo /opt/oracle/instantclient > /etc/ld.so.conf.d/oracle-instantclient.conf && \
    echo "LD_LIBRARY_PATH=\"/opt/oracle/instantclient\"" >> /etc/environment && \
    echo "ORACLE_HOME=\"/opt/oracle/instantclient\"" >> /etc/environment && \
    ldconfig && \
    pecl channel-update pecl.php.net && \
    echo "instantclient,/opt/oracle/instantclient" | pecl install oci8

# Install SQLSRV PHP Drivers
RUN pecl install pdo_sqlsrv && \
    pecl install sqlsrv

# Clean up php.ini
RUN sed -i '1d' /etc/php/7.2/fpm/php.ini && \
    sed -i '1d' /etc/php/7.2/fpm/php.ini && \
    sed -i '1d' /etc/php/7.2/fpm/php.ini && \
    echo "extension=pdo_sqlsrv.so" >> /etc/php/7.2/fpm/conf.d/30-pdo_sqlsrv.ini && \
    echo "extension=sqlsrv.so" >> /etc/php/7.2/fpm/conf.d/20-sqlsrv.ini && \
    echo "extension=oci8.so" >> /etc/php/7.2/fpm/conf.d/20-oci8.ini

EXPOSE 80

STOPSIGNAL SIGTERM

COPY supervisord.conf /etc/supervisor/supervisord.conf
CMD ["/usr/bin/supervisord", "-n", "-c", "/etc/supervisor/supervisord.conf"]

[benyanke]

Thanks for the tip - I've included that in 547070d on my repo.

[benyanke]

I don't have a ton of time to support, but I got the kinks worked out on my image as well. This will now support a PHP app using the sqlsrv or pdo_sqlsrv drivers. Hopefully it is helpful as either a base image, or an example of how to make this work in your own images.

https://github.com/benyanke/docker-php7

[techguydev]

Hi all, this is the closest to a solution regarding the topic I've found one the internet.

Although I have tried all the suggestion and any has worked for me.

Do you have a Laravel Forge Recipe?

[yitam]

Hi @lmatab, we will be happy to help if you have any specific issue when deploying our drivers. For your question, please contact the support at Laravel instead. Thanks!

[mattkoch614]

Is there a definitive answer as to whether this will work with Ubuntu 18.04 and PHP 7.3 (the latest versions to date when using Laravel Homestead)?

Edit
Got it working. Basically, I followed the instructions here https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu and in addition, added the extensions to fpm as well by adding in

extension=pdo_sqlsrv.so
extension=sqlsrv.so

to /etc/php/7.3/fpm/php.ini

Lastly, I ran these two restart commands:

sudo systemctl restart nginx
sudo systemctl restart php7.3-fpm

[yitam]

Hi @mattkoch614, please use the instructions on this page instead because we actively maintain it.

We just uploaded a Dockerfile example in wiki for this use case.

[mattkoch614]

@yitam thanks for the tip, the official instructions at https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu didn't fully get me going, so it's nice to know there's a separate wiki.

[yitam]

Glad to hear that, @mattkoch614
Please let us know if it does not work for you.

[NoxxieNl]

With some help of @JanOonk script..
I got this working on a Vagrant Laravel Homestead virtual box for php7.4.

The main difference here is that the drivers are added via mods-available, the rest just simply works. The homestead box comes with 5.6 to 7.4, if you want to activate in on a lower version just swap out the 7.4 references.

Select the correct source list of you OS :-).
I ran this script after sudo su

sudo apt install -y curl

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 19.10
curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo 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

sudo apt install -y unixodbc-dev

# unixodbc-dev-utf16 is optional but recommended according to MS
# This package is gone from repos as of sept 19, 2017. Leaving it here and 
# just piping errors to null in case it returns (hopefully).

# had to add an extra dependency unixodbc-utf16
sudo apt install -y unixodbc-utf16 unixodbc-dev-utf16 2> /dev/null

sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv    

echo "Configuring PHP to work with sqlsrv drivers"

# Add sqlsrv and pdo_sqlserv to mods-available
printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.4/mods-available/sqlsrv.ini
printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.4/mods-available/pdo_sqlsrv.ini

sudo phpenmod -v 7.4 sqlsrv pdo_sqlsrv

echo "Installing Microsoft ODBC Driver for SQL Server"

# Install needed libraries
sudo apt install -y libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev

# Create symlinks for tools
sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
sudo ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

# Restart FPM
sudo service php7.4-fpm restart

[yitam]

Thanks @NoxxieNl for your input. FYI, please also check the instructions for Ubuntu

[stevebauman]

Does anyone know if it's possible to install & use these drivers on Laravel Valet (not Homestead)?

I've downloaded the .tar bundle for my PHP version, placed the files inside of my extension directory that PHP is using, updated my PHP.ini file with:

extension=sqlsrv.so
extension=pdo_sqlsrv.so

Then, when running php -v in my terminal, I get this:

I'm a huge macOS novice (coming from Windows) -- what am I missing? Do Nginx and PHP-FPM load extensions completely differently than Apache?

I've also tried following the install instructions using the pecl commands but I'm simply not getting anywhere... Does anyone have any tips at all? I'd be extremely grateful! Thank you!

[yitam]

@stevebauman I'm not familiar with Laravel Valet but what's your mac OS version? How pecl install did not help?

[stevebauman]

Thank you for the response @yitam! Much appreciated!

I'm running macOS Catalina - 10.15.4 (19E287).

I first ran these:

sudo pecl install sqlsrv-5.8.1
sudo pecl install pdo_sqlsrv-5.8.1

And then these appeared to be installed to the following locations (full folder path):

/usr/local/Cellar/php/7.4.3/lib/php/20190902/sqlsrv.so
/usr/local/Cellar/php/7.4.3/lib/php/20190902/pdo_sqlsrv.so

Then, I updated my php.ini extension_dir to:

extension_dir="/usr/local/Cellar/php/7.4.3/lib/php/20190902"

In the same php.ini, I enabled the extensions:

extension=sqlsrv.so
extension=pdo_sqlsrv.so

If I run which php in my terminal, I get:

/usr/local/bin/php

Which is a symlink to (I believe that's the correct terminology):

/usr/local/Cellar/php/7.4.3/bin/php

My php.ini is located in the directory:

/usr/local/etc/php/7.4/php.ini

I'm hoping this information helps, but if you need anything further please let me know! :smile:

EDIT:

Here's my phpinfo() if this helps at all:

[yitam]

hi @stevebauman unlike Windows, you need not modify the php.ini when installing sqlsrv drivers in macOS

After using pecl install, the correct *.ini files will be located in /usr/local/etc/php/7.4/conf.d

Please remove your changes in the php.ini and/or re-install php 7.4 using homebrew.

This page might give you an idea. It does mention Laravel Valet.

[stevebauman]

YES!! Thank you @yitam! You are amazing! I've been working on this for hours!

This line here helped me immensely in understanding how PHP loads extensions:

unlike Windows, you need not modify the php.ini when installing sqlsrv drivers in macOS

The link you mentioned helped me get up and running -- I now see it enabled in my phpinfo():

Here is what I did in case someone else runs into this issue:

I did all of this in the same terminal window.

1. brew update
2. brew uninstall php
3. Manually deleted the /usr/local/etc/php directory (it will not be deleted automatically)
4. brew cleanup (not sure if this helped, but it will force PHP to be re-downloaded)
5. brew install php
6. Make sure PHP is installed and accessible via php -v
7. sudo nginx -s reload
8. composer global update
9. valet install
10. sudo pecl install sqlsrv
11. sudo pecl install pdo_sqlsrv
12. Run pecl list to make sure you have them both properly installed
13. sudo nginx -s reload
14. valet restart
15. Run phpinfo() on one of your valet hosts and check to make sure pdo_sqlsrv and sqlsrv is listed

My php.ini file located at:

/usr/local/etc/php/7.4/php.ini

Automatically had these two lines added to the top:

extension="pdo_sqlsrv.so"
extension="sqlsrv.so"
[PHP]

;;;;;;;;;;;;;;;;;;;
; About php.ini   ;
;;;;;;;;;;;;;;;;;;;

If anyone runs into this issue, let me know and I can try my best to help

[yitam]

Glad to be of help, @stevebauman :)

[yitam]

Closing this issue as an example for Ubuntu are up on Microsoft Docs