Connecting to a remote MSSQL server

[matthias-van-woensel]

Goal

This article describes how to configure your project so you can connect to MSSQL server. This article specifically looks at the drupal module sqlsrv https://www.drupal.org/project/sqlsrv but it can be adapted to work for any PHP application

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

Install

The install steps mentioned on the sqlsrv page https://www.drupal.org/docs/contributed-modules/drupal-driver-for-sql-server-and-sql-azure/install mention the following:

Linux Installation

1. Install Microsoft SQL Server
2. Install the Microsoft ODBC Drivers
3. Install the PDO drivers, sqlsrv and pdo_sqlsrv
4. Verify that the php modules are enabled with phpinfo() from both CLI and web.
5. Copy the drivers directory from the module to the web root.
6. Install Drupal from the web interface.

Let us go through each one and see how they translate to Platform.Sh

0. Platform.Sh project

We need a Platform.Sh project to work with. If you already have a drupal installation set up, great!

If not, feel free to start with a fresh one based on the drupal 8 template https://github.com/platformsh-templates/drupal8/

Or click the deploy on Platform button below:

1. SQL Server

I’m going to assume you already have one. But if not, you can set one up on azure if you like.

2. MS SQL ODBC Driver

While the official instructions do mention unixODBC https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17 we are not going to be installing those.

In stead, we are going to install FreeTDS https://github.com/FreeTDS/freetds an opensource implementation of the unixODBC that is easier to install and is actively maintained.

We are going to leverage brew brew.sh to save us the hassle of compiling it ourselves.

Create a file install-odbc.sh and commit it to your repository.

run() {
    # Run the compilation process.
    cd $PLATFORM_CACHE_DIR || exit 1;

    if [ ! -f "${PLATFORM_CACHE_DIR}/.linuxbrew/bin/isql" ]; then
        install_brew
        install_unixodbc
        copy_lib_to_cache
    else
        copy_lib_from_cache
    fi

}

copy_lib_to_cache() {
    echo "Copy to cache..."
    cp -Rfv $PLATFORM_APP_DIR/.linuxbrew $PLATFORM_CACHE_DIR
}

copy_lib_from_cache() {
    echo "Copy from cache..."
    cp -Rfv $PLATFORM_CACHE_DIR/.linuxbrew $PLATFORM_APP_DIR
}

install_brew() {
    echo "Installing homebrew"
    mkdir -p /app/.linuxbrew

    echo "Downloading tarball from master repository"
    curl -SsL https://github.com/Homebrew/brew/tarball/master -o brew.tar.gz
    
    echo "Unpacking into .linuxbrew folder"
    tar xzf brew.tar.gz --strip-components 1 -C /app/.linuxbrew/
    rm brew.tar.gz

    eval $(/app/.linuxbrew/bin/brew shellenv)
    brew analytics off

}

install_unixodbc() {
    brew install openssl
    brew install freetds
}

run

3. Install PDO drivers

The PDO drivers can be found on github https://github.com/Microsoft/msphpsql/ but we cannot use pecl to install them. We can however, compile them from source.

Create a new file install-mssql-php.sh that does the compilation

run() {
    # Run the compilation process.
    cd $PLATFORM_CACHE_DIR || exit 1;

    if [ ! -f "${PLATFORM_CACHE_DIR}/msphpsql/modules/sqlsrv.so" ]; then
        ensure_source
        checkout_version "$1"
        compile_source
    fi

    copy_lib
    enable_lib
}

enable_lib() {

    # Tell PHP to enable the extension.
    echo "Enabling extension."
    echo "extension=${PLATFORM_APP_DIR}/sqlsrv.so" >> $PLATFORM_APP_DIR/php.ini
    echo "extension=${PLATFORM_APP_DIR}/pdo_sqlsrv.so" >> $PLATFORM_APP_DIR/php.ini
}

copy_lib() {
    # Copy the compiled library to the application directory.
    echo "Installing extension."
    cp -rv $PLATFORM_CACHE_DIR/*/source/*/modules/*.so $PLATFORM_APP_DIR
}

checkout_version () {
    # Check out the specific Git tag that we want to build.
    git checkout "$1"
}

ensure_source() {
    # Ensure that the extension source code is available and up to date.
    if [ -d "msphpsql" ]; then
        cd msphpsql || exit 1;
        git fetch --all --prune
    else
        git clone https://github.com/microsoft/msphpsql.git
        cd msphpsql || exit 1;
    fi
}

compile_source() {
    # Compile the extension.

    export LIBRARY_PATH=/app/.linuxbrew/lib
    cd source
    bash packagize.sh

    cd pdo_sqlsrv
    phpize
    ./configure --with-libdir=/app/.linuxbrew/include CXXFLAGS="-I/app/.linuxbrew/include"
    make
    ls
    cd ..


    cd sqlsrv
    phpize
    ./configure --with-libdir=/app/.linuxbrew/include CXXFLAGS="-I/app/.linuxbrew/include"
    make
    ls
    cd ..

    cd ..
}

ensure_environment() {
    # If not running in a Platform.sh build environment, do nothing.
    if [ -z "${PLATFORM_CACHE_DIR}" ]; then
        echo "Not running in a Platform.sh build environment. Aborting installation."
        exit 0;
    fi
}

ensure_arguments() {
    # If no version was specified, don't try to guess.
    if [ -z $1 ]; then
        echo "No version of the extension specified. You must specify a tagged version on the command line."
        exit 1;
    fi
}

ensure_environment
ensure_arguments "$1"
run "$1"

Putting 2 and 3 together In the build hook.

We can now call the bash scripts from our build hook

# The hooks executed at various points in the lifecycle of the application.
hooks:
    # The build hook runs after Composer to finish preparing up your code.
    # No services are available but the disk is writeable.
    build: |
        set -e
        #install odbc via brew
        bash install-odbc.sh

        #install mssql by compiling from source (pass specific version to compile)
        bash install-mssql-php.sh 5.8.1

4. Verify installation (php_info)

platform ssh 'php -i | grep sqlsrv'

This should return something like this:

Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, compress.bzip2, phar, zip, sqlsrv
PDO drivers => mysql, sqlite, sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.set_locale_info => 2 => 2
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.SetLocaleInfo => 2 => 2
sqlsrv.WarningsReturnAsErrors => On => On
SSH_ORIGINAL_COMMAND => php -i | grep sqlsrv
$_SERVER['SSH_ORIGINAL_COMMAND'] => php -i | grep sqlsrv
$_ENV['SSH_ORIGINAL_COMMAND'] => php -i | grep sqlsrv
Connection to ssh.eu-3.platform.sh closed.

5. Copying the drivers

We already did that

6. Install Drupal

Out of scope for this article.

Conclusion

Now you are ready to input your remote MSSQL server connection details, and connect to it via PHP.