How to install the SQL Server PHP drivers in DDEV-Local?

Question

I'm working on a DDEV-Local project where I need the SQL Server drivers for PHP (sqlsrv and pdo_sqlsrv). How can I add them to its web container? I can't use the extra packages configuration because it requires adding external repositories.

Answer 1

This answer is updated for ddev v1.14.1+.

Add this Dockerfile to .ddev/web-build:

ARG BASE_IMAGE
FROM $BASE_IMAGE
COPY .ddev/web-build/install_sqlsrv.sh /tmp/

# If using DDEV < 1.21.5, use this instead of the above COPY:
#COPY install_sqlsrv.sh /tmp/

RUN apt-get update && DEBIAN_FRONTEND=noninteractive sudo apt-get install -y -o Dpkg::Options::="--force-confold" --no-install-recommends --no-install-suggests gcc make autoconf libc-dev pkg-config php-pear
RUN sudo /bin/bash /tmp/install_sqlsrv.sh

And save this script as .ddev/web-build/install_sqlsrv.sh :

#!/bin/bash
# This script installs MSSQL server. Contains some ddev-specific tweaks:
# - doesn't add ondrej's repo because that's already added
# - doesn't source .bashrc because that will happen anyway.
# - doesn't restart Apache; it's not started at this point.
# - assumes it's being run under sudo anyway and doesn't use sudo or su or exit
# - tries to run apt update as few times as possible
# - Apache's mpm_event module is already disabled.
#   Those don't work well with Docker builds.


# Optional: Exit if already installed.
if php -m | grep sqlsrv; then
  exit
fi

# https://learn.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-2017#step-1-install-php-2
# Some of these packages will be redundant.
export DEBIAN_FRONTEND=noninteractive
# Install sqlsrv drivers.
export PHP_VERSIONS="php7.0 php7.1 php7.2 php7.3"
# Note: Only works for PHP 7.0+.
export PHP_SUFFIXES="7.0 7.1 7.2 7.3"

# https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
curl https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmour -o /etc/apt/trusted.gpg.d/microsoft.gpg
# Download appropriate package for the OS version
OS=$(grep -P '(?<=^ID=)' /etc/os-release | cut -c 4-)
VERSION=$(lsb_release -rs)

sudo touch /etc/apt/sources.list.d/mssql-release.list
sudo chmod 666 /etc/apt/sources.list.d/mssql-release.list
sudo curl https://packages.microsoft.com/config/$OS/$VERSION/prod.list >/etc/apt/sources.list.d/mssql-release.list
sudo chmod 644 /etc/apt/sources.list.d/mssql-release.list

apt-get update
apt-get install -y curl apt-transport-https
for v in $PHP_VERSIONS; do
  apt-get install -y -o Dpkg::Options::="--force-confold" "$v" "$v"-dev "$v"-xml
done
ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>~/.bashrc
# optional: for unixODBC development headers
apt-get install -y unixodbc-dev

# https://learn.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-2017#step-3-install-the-php-drivers-for-microsoft-sql-server
# See https://stackoverflow.com/questions/40419718/how-to-install-php-extension-using-pecl-for-specific-php-version-when-several-p/48352487
for v in $PHP_SUFFIXES; do
  pecl -d php_suffix="$v" install sqlsrv
  pecl -d php_suffix="$v" install pdo_sqlsrv
  # This does not remove the extensions; it just removes the metadata that says
  # the extensions are installed.
  pecl uninstall -r sqlsrv
  pecl uninstall -r pdo_sqlsrv
done
for v in $PHP_SUFFIXES; do
  touch /etc/php/"$v"/mods-available/sqlsrv.ini
  touch /etc/php/"$v"/mods-available/pdo_sqlsrv.ini
  chmod 666 /etc/php/"$v"/mods-available/*sqlsrv*.ini
  printf "; priority=20\nextension=sqlsrv.so\n" >/etc/php/"$v"/mods-available/sqlsrv.ini
  printf "; priority=30\nextension=pdo_sqlsrv.so\n" >/etc/php/"$v"/mods-available/pdo_sqlsrv.ini
done
phpenmod sqlsrv pdo_sqlsrv

# Step 4 skipped because Apache is already configured.
# Step 5 skipped because Apache is not started at this point.

# Reduce image size some.
if [ -f "/.dockerenv" ]; then
  rm -rf /var/lib/apt/lists/*
fi

Then just run ddev start again.

Note: This takes several minutes to build. You can speed it up by removing PHP versions you don't use from the variable at the top of the Bash script.

Important: The script must be run with sudo to work. If you change the script to add sudo to the front of each command, this will break the apt-get install lines as export DEBIAN_FRONTEND=noninteractive no longer will work. You will have to add DEBIAN_FRONTEND=noninteractive to each apt-get command that requires it. I think the only needed one is

  sudo DEBIAN_FRONTEND=noninteractive apt-get install -y -o Dpkg::Options::="--force-confold" "$v" "$v"-dev 

See the comments in the scripts for where the instructions came from and how they were tweaked to work with ddev.

Answer 2

This answer is an addendum, or an extension to the 2019 answer by user @wizonesolutions, edited by @rfay.

For ddev instances that run Debian 10, and which should be enabled to connect to older MSSQL server instances, there are Error code 0x2746 problems with TLS (encryption) when the connection from Linux to SQL Server is established.

I need 3 files:

1. Dockerfile
2. install_sqlsrv.sh shell script
3. custom openssl.cnf configuration

The openssl.cnf configuration file is necessary to solve the problem described in this blog post: Installing PDO_SQLSRV on Debian 10 :

Note that Debian requires TLS 1.2 and if your SQL Server does not support that you will run into "Error code 0x2746"...

---

Dockerfile - similar to the 2019 file.

ARG BASE_IMAGE
FROM $BASE_IMAGE

ENV NVM_DIR=/usr/local/nvm
ENV NODE_DEFAULT_VERSION=v16

RUN curl -sL https://raw.githubusercontent.com/nvm-sh/nvm/v0.38.0/install.sh -o install_nvm.sh
RUN mkdir -p $NVM_DIR && bash install_nvm.sh
RUN echo "source $NVM_DIR/nvm.sh" >>/etc/profile
RUN bash -ic "nvm install $NODE_DEFAULT_VERSION && nvm use $NODE_DEFAULT_VERSION"
RUN chmod -R ugo+w $NVM_DIR

# knb 20210720

COPY openssl.cnf /etc/ssl/openssl.cnf
COPY install_sqlsrv.sh /var/tmp/
RUN apt-get update && DEBIAN_FRONTEND=noninteractive sudo apt-get install -y -o Dpkg::Options::="--force-confold" --no-install-recommends --no-install-suggests gcc make autoconf libc-dev pkg-config php-pear php-dev
RUN /bin/bash /var/tmp/install_sqlsrv.sh

---

install_sqlsrv.sh shell script - similar to the 2019 script.

#!/usr/bin/env bash
export DEBIAN_FRONTEND=noninteractive
# Install sqlsrv drivers.
export PHP_VERSIONS="php7.0 php7.1 php7.2 php7.3 php7.4 php8.0"
# Note: Only works for PHP 7.0+.
export PHP_SUFFIXES="7.0 7.1 7.2 7.3 7.4 8.0"


if [ ! -f /etc/apt/sources.list.d/mssql-release.list ]; then
  curl -s https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
  sudo bash -c "curl -s https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list"
fi

sudo apt -y update

for v in $PHP_VERSIONS; do
  sudo apt-get install -y "$v" "$v"-dev "$v"-xml
done

if [ ! -d /opt/microsoft ]; then
  sudo ACCEPT_EULA=Y apt -y install msodbcsql17 mssql-tools
  sudo apt -y install unixodbc-dev
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>~/.bash_profile
  echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>~/.bashrc
fi

for v in $PHP_SUFFIXES; do
  sudo pecl -d php_suffix="$v" install sqlsrv
  sudo pecl -d php_suffix="$v" install pdo_sqlsrv
  # This does not remove the extensions; it just removes the metadata that says
  # the extensions are installed.
  sudo pecl uninstall -r sqlsrv
  sudo pecl uninstall -r pdo_sqlsrv
done
for v in $PHP_SUFFIXES; do
  sudo bash -c "printf \"; priority=20\nextension=sqlsrv.so\n\" >/etc/php/\"$v\"/mods-available/sqlsrv.ini "
  sudo bash -c "printf \"; priority=30\nextension=pdo_sqlsrv.so\n\" >/etc/php/\"$v\"/mods-available/pdo_sqlsrv.ini "
  sudo bash -c "chmod 666 /etc/php/"$v"/mods-available/*sqlsrv*.ini"
done
sudo phpenmod sqlsrv pdo_sqlsrv

---

openssl.cnf configuration - line 19 is new, and stuff below line 355 is also new.

I have copied the script from this Github issue thread:

#
# OpenSSL example configuration file.
# This is mostly being used for generation of certificate requests.
#

# Note that you can include other files from the main configuration
# file using the .include directive.
#.include filename

# This definition stops the following lines choking if HOME isn't
# defined.
HOME            = .

# Extra OBJECT IDENTIFIER info:
#oid_file       = $ENV::HOME/.oid
oid_section     = new_oids

# System default
openssl_conf = default_conf

# To use this configuration file with the "-extfile" option of the
# "openssl x509" utility, name here the section containing the
# X.509v3 extensions to use:
# extensions        =
# (Alternatively, use a configuration file that has only
# X.509v3 extensions in its main [= default] section.)

[ new_oids ]

# We can add new OIDs in here for use by 'ca', 'req' and 'ts'.
# Add a simple OID like this:
# testoid1=1.2.3.4
# Or use config file substitution like this:
# testoid2=${testoid1}.5.6

# Policies used by the TSA examples.
tsa_policy1 = 1.2.3.4.1
tsa_policy2 = 1.2.3.4.5.6
tsa_policy3 = 1.2.3.4.5.7

####################################################################
[ ca ]
default_ca  = CA_default        # The default ca section

####################################################################
[ CA_default ]

dir     = ./demoCA      # Where everything is kept
certs       = $dir/certs        # Where the issued certs are kept
crl_dir     = $dir/crl      # Where the issued crl are kept
database    = $dir/index.txt    # database index file.
#unique_subject = no            # Set to 'no' to allow creation of
                    # several certs with same subject.
new_certs_dir   = $dir/newcerts     # default place for new certs.

certificate = $dir/cacert.pem   # The CA certificate
serial      = $dir/serial       # The current serial number
crlnumber   = $dir/crlnumber    # the current crl number
                    # must be commented out to leave a V1 CRL
crl     = $dir/crl.pem      # The current CRL
private_key = $dir/private/cakey.pem# The private key

x509_extensions = usr_cert      # The extensions to add to the cert

# Comment out the following two lines for the "traditional"
# (and highly broken) format.
name_opt    = ca_default        # Subject Name options
cert_opt    = ca_default        # Certificate field options

# Extension copying option: use with caution.
# copy_extensions = copy

# Extensions to add to a CRL. Note: Netscape communicator chokes on V2 CRLs
# so this is commented out by default to leave a V1 CRL.
# crlnumber must also be commented out to leave a V1 CRL.
# crl_extensions    = crl_ext

default_days    = 365           # how long to certify for
default_crl_days= 30            # how long before next CRL
default_md  = default       # use public key default MD
preserve    = no            # keep passed DN ordering

# A few difference way of specifying how similar the request should look
# For type CA, the listed attributes must be the same, and the optional
# and supplied fields are just that :-)
policy      = policy_match

# For the CA policy
[ policy_match ]
countryName     = match
stateOrProvinceName = match
organizationName    = match
organizationalUnitName  = optional
commonName      = supplied
emailAddress        = optional

# For the 'anything' policy
# At this point in time, you must list all acceptable 'object'
# types.
[ policy_anything ]
countryName     = optional
stateOrProvinceName = optional
localityName        = optional
organizationName    = optional
organizationalUnitName  = optional
commonName      = supplied
emailAddress        = optional

####################################################################
[ req ]
default_bits        = 2048
default_keyfile     = privkey.pem
distinguished_name  = req_distinguished_name
attributes      = req_attributes
x509_extensions = v3_ca # The extensions to add to the self signed cert

# Passwords for private keys if not present they will be prompted for
# input_password = secret
# output_password = secret

# This sets a mask for permitted string types. There are several options.
# default: PrintableString, T61String, BMPString.
# pkix   : PrintableString, BMPString (PKIX recommendation before 2004)
# utf8only: only UTF8Strings (PKIX recommendation after 2004).
# nombstr : PrintableString, T61String (no BMPStrings or UTF8Strings).
# MASK:XXXX a literal mask value.
# WARNING: ancient versions of Netscape crash on BMPStrings or UTF8Strings.
string_mask = utf8only

# req_extensions = v3_req # The extensions to add to a certificate request

[ req_distinguished_name ]
countryName         = Country Name (2 letter code)
countryName_default     = AU
countryName_min         = 2
countryName_max         = 2

stateOrProvinceName     = State or Province Name (full name)
stateOrProvinceName_default = Some-State

localityName            = Locality Name (eg, city)

0.organizationName      = Organization Name (eg, company)
0.organizationName_default  = Internet Widgits Pty Ltd

# we can do this but it is not needed normally :-)
#1.organizationName     = Second Organization Name (eg, company)
#1.organizationName_default = World Wide Web Pty Ltd

organizationalUnitName      = Organizational Unit Name (eg, section)
#organizationalUnitName_default =

commonName          = Common Name (e.g. server FQDN or YOUR name)
commonName_max          = 64

emailAddress            = Email Address
emailAddress_max        = 64

# SET-ex3           = SET extension number 3

[ req_attributes ]
challengePassword       = A challenge password
challengePassword_min       = 4
challengePassword_max       = 20

unstructuredName        = An optional company name

[ usr_cert ]

# These extensions are added when 'ca' signs a request.

# This goes against PKIX guidelines but some CAs do it and some software
# requires this to avoid interpreting an end user certificate as a CA.

basicConstraints=CA:FALSE

# Here are some examples of the usage of nsCertType. If it is omitted
# the certificate can be used for anything *except* object signing.

# This is OK for an SSL server.
# nsCertType            = server

# For an object signing certificate this would be used.
# nsCertType = objsign

# For normal client use this is typical
# nsCertType = client, email

# and for everything including object signing:
# nsCertType = client, email, objsign

# This is typical in keyUsage for a client certificate.
# keyUsage = nonRepudiation, digitalSignature, keyEncipherment

# This will be displayed in Netscape's comment listbox.
nsComment           = "OpenSSL Generated Certificate"

# PKIX recommendations harmless if included in all certificates.
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer

# This stuff is for subjectAltName and issuerAltname.
# Import the email address.
# subjectAltName=email:copy
# An alternative to produce certificates that aren't
# deprecated according to PKIX.
# subjectAltName=email:move

# Copy subject details
# issuerAltName=issuer:copy

#nsCaRevocationUrl      = http://www.domain.dom/ca-crl.pem
#nsBaseUrl
#nsRevocationUrl
#nsRenewalUrl
#nsCaPolicyUrl
#nsSslServerName

# This is required for TSA certificates.
# extendedKeyUsage = critical,timeStamping

[ v3_req ]

# Extensions to add to a certificate request

basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment

[ v3_ca ]


# Extensions for a typical CA


# PKIX recommendation.

subjectKeyIdentifier=hash

authorityKeyIdentifier=keyid:always,issuer

basicConstraints = critical,CA:true

# Key usage: this is typical for a CA certificate. However since it will
# prevent it being used as an test self-signed certificate it is best
# left out by default.
# keyUsage = cRLSign, keyCertSign

# Some might want this also
# nsCertType = sslCA, emailCA

# Include email address in subject alt name: another PKIX recommendation
# subjectAltName=email:copy
# Copy issuer details
# issuerAltName=issuer:copy

# DER hex encoding of an extension: beware experts only!
# obj=DER:02:03
# Where 'obj' is a standard or added object
# You can even override a supported extension:
# basicConstraints= critical, DER:30:03:01:01:FF

[ crl_ext ]

# CRL extensions.
# Only issuerAltName and authorityKeyIdentifier make any sense in a CRL.

# issuerAltName=issuer:copy
authorityKeyIdentifier=keyid:always

[ proxy_cert_ext ]
# These extensions should be added when creating a proxy certificate

# This goes against PKIX guidelines but some CAs do it and some software
# requires this to avoid interpreting an end user certificate as a CA.

basicConstraints=CA:FALSE

# Here are some examples of the usage of nsCertType. If it is omitted
# the certificate can be used for anything *except* object signing.

# This is OK for an SSL server.
# nsCertType            = server

# For an object signing certificate this would be used.
# nsCertType = objsign

# For normal client use this is typical
# nsCertType = client, email

# and for everything including object signing:
# nsCertType = client, email, objsign

# This is typical in keyUsage for a client certificate.
# keyUsage = nonRepudiation, digitalSignature, keyEncipherment

# This will be displayed in Netscape's comment listbox.
nsComment           = "OpenSSL Generated Certificate"

# PKIX recommendations harmless if included in all certificates.
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer

# This stuff is for subjectAltName and issuerAltname.
# Import the email address.
# subjectAltName=email:copy
# An alternative to produce certificates that aren't
# deprecated according to PKIX.
# subjectAltName=email:move

# Copy subject details
# issuerAltName=issuer:copy

#nsCaRevocationUrl      = http://www.domain.dom/ca-crl.pem
#nsBaseUrl
#nsRevocationUrl
#nsRenewalUrl
#nsCaPolicyUrl
#nsSslServerName

# This really needs to be in place for it to be a proxy certificate.
proxyCertInfo=critical,language:id-ppl-anyLanguage,pathlen:3,policy:foo

####################################################################
[ tsa ]

default_tsa = tsa_config1   # the default TSA section

[ tsa_config1 ]

# These are used by the TSA reply generation only.
dir     = ./demoCA      # TSA root directory
serial      = $dir/tsaserial    # The current serial number (mandatory)
crypto_device   = builtin       # OpenSSL engine to use for signing
signer_cert = $dir/tsacert.pem  # The TSA signing certificate
                    # (optional)
certs       = $dir/cacert.pem   # Certificate chain to include in reply
                    # (optional)
signer_key  = $dir/private/tsakey.pem # The TSA private key (optional)
signer_digest  = sha256         # Signing digest to use. (Optional)
default_policy  = tsa_policy1       # Policy if request did not specify it
                    # (optional)
other_policies  = tsa_policy2, tsa_policy3  # acceptable policies (optional)
digests     = sha1, sha256, sha384, sha512  # Acceptable message digests (mandatory)
accuracy    = secs:1, millisecs:500, microsecs:100  # (optional)
clock_precision_digits  = 0 # number of digits after dot. (optional)
ordering        = yes   # Is ordering defined for timestamps?
                # (optional, default: no)
tsa_name        = yes   # Must the TSA name be included in the reply?
                # (optional, default: no)
ess_cert_id_chain   = no    # Must the ESS cert id chain be included?
                # (optional, default: no)
ess_cert_id_alg     = sha1  # algorithm to compute certificate
                # identifier (optional, default: sha1)

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=1

I recommend to make a "diff" with your existing openssl.cnf file.

Do not use this in production. - The openssl.cnf file "dumbs down" the default encryption setting of Debian 10. See the respective Github Issues for details.