Devilbox: MsSQL support

Created on 11 Dec 2018 · 25Comments · Source: cytopia/devilbox

ISSUE TYPE

Feature request

SUMMARY

Add MsSQL support.
MsSQL is now supported with Linux architecture and docker. It would be interesting to complete SQL database support with MsSQL

Goal

Add new DB support on this good project and connect PHP with pdo's driver for mssql.

feature legal issue

Source

Stylix

All 25 comments

@Stylix can you point me to the correct MsSQL module for PHP as well as their install instructions for Linux.

cytopia on 15 Dec 2018

@cytopia You can find here all the instructions to install MsSQL module for php on linux :
Github msphpsql.
I try to install it but i've got issue with connecting to MsSQL database and i don't know why.

Let me know if I can help you for something ;)

Stylix on 17 Dec 2018

@Stylix
I've managed to include them in PHP 7.0, 7.1, 7.2 and 7.3. See linked PR above.
Will be available in Devilbox master branch very soon.

cytopia on 23 Dec 2018

@Stylix PR is open and can be checked out: https://github.com/cytopia/devilbox/pull/445

Ensure to remove them in .env from PHP_MODULES_DISABLE. Let me know how this works for you.

cytopia on 23 Dec 2018

@Stylix
Its merged and now available. Have a look here to enable it for all PHP 7 images (except 7.4)
https://devilbox.readthedocs.io/en/latest/configuration-files/env-file.html#php-modules-disable

Note: documentation might take 10min after writing this comment to be updated.

cytopia on 23 Dec 2018

Reopening. I completely forgot to install the MsODBC driver (apart from the PHP modules itself):
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

cytopia on 23 Dec 2018

@Stylix

Currently the only part that is missing is the ODBC driver software itself, the PHP modules are available.

So checkout the latest Devilbox master branch and start up the Devilbox
Enter the PHP container: ./shell.sh
become root: sudo su -
Copy/Paste the following lines into the root shell of your PHP container (Note: this auto-accepts the ODBC driver License of Microsoft, if you do not agree with their license, do not paste this code into the container, as it will install software for which the license must be agreed with. License is here: https://odbceula.blob.core.windows.net/eula17/LICENSE172.TXT):

MSODBC_URL="https://packages.microsoft.com/debian/8/prod/pool/main/m/msodbcsql17/";
MSODBC_DEB="$( curl -sS "${MSODBC_URL}" | grep -Eo 'msodbcsql[-._0-9]+?_amd64\.deb' | tail -1 )";
curl -sS "${MSODBC_URL}${MSODBC_DEB}" > "/tmp/${MSODBC_DEB}";
ACCEPT_EULA=Y  dpkg -i "/tmp/${MSODBC_DEB}";
rm -f "/tmp/${MSODBC_DEB}";

I am not sure If I can actually bundle this, due to a license that needs to be accepted. This seems to be a similar issue as: https://github.com/cytopia/devilbox/issues/394

However, I am thinking about an autostart option which would execute any arbitrary user scripts and each user could then provide whatever he/she needs to be auto-executed during the PHP container startup. This could then install the MSODBC drivers on each run automatically.

For now, let me know if the above works for you then and if you can successfully connect to a MsSQL database.

cytopia on 24 Dec 2018

@cytopia thanks for work !!
I will try this today and keep you informed that is works !

Stylix on 24 Dec 2018

I've pull the latest master but i've issue when I try to start my container with the 'docker-compose up' cmd. I'm going to investigate why ...

Do you think it's possible to add sqlsrv image like the mysql for a next version ?

Stylix on 24 Dec 2018

I've pull the latest master but i've issue when I try to start my container with the 'docker-compose up' cmd. I'm going to investigate why ...

You should compare your current .env with the new env-example file and make sure you have all required settings.

Also do a:

docker-compose down
docker-compose rm -f
docker-compose up

sqlsrv and pdo_sqlsrv modules for PHP are included. This is however not enough, you still need the ODBC driver to actually make them work. This is described above by the commands you can simply copy/paste into the root shell of the PHP container.

If you still hit issues starting it up, let me know what error is thrown.

cytopia on 24 Dec 2018

@Stylix
The feature for custom startup scripts is on the way. I will also provide default examples per PHP version where you (the user) will have to explicitly accept the license. First set of examples will be provided for

MsSQL ODBC driver
Oracle Instaclient

This will eliminate the current limitations due to legal issues.

Current PR for php-fpm is here: https://github.com/devilbox/docker-php-fpm/pull/78

cytopia on 24 Dec 2018

@cytopia Nice job !!

When I'm try to install ODBC driver, I've got this error

[email protected] in /shared/httpd $ MSODBC_DEB="$( curl -sS "${MSODBC_URL}" | grep -Eo 'msodbcsql[-._0-9]+?_amd64.deb' | tail -1 )";
curl: (51) SSL: no alternative certificate subject name matches target host name 'packages.microsoft.com'

Stylix on 24 Dec 2018

Hmm is there something wrong with your DNS resolution inside the PHP container?
Can you check to curl google or other sites and see if they are actually google. (What is your TLD_SUFFIX ?)

Otherwise if everything is fine, use the -k option to ignore SSL cert errors:

MSODBC_URL="https://packages.microsoft.com/debian/8/prod/pool/main/m/msodbcsql17/";
MSODBC_DEB="$( curl -k -sS "${MSODBC_URL}" | grep -Eo 'msodbcsql[-._0-9]+?_amd64\.deb' | tail -1 )";
curl -k -sS "${MSODBC_URL}${MSODBC_DEB}" > "/tmp/${MSODBC_DEB}";
ACCEPT_EULA=Y  dpkg -i "/tmp/${MSODBC_DEB}";
rm -f "/tmp/${MSODBC_DEB}";

cytopia on 24 Dec 2018

Devilbox PR is open: https://github.com/cytopia/devilbox/pull/446

You can checkout this git branch: custom-startup-scripts to test it out.

Example scripts for Ms ODBC driver are available as well and just need to be copied to a file ending by .sh. And you will have to accept the license inside the bash script.

cytopia on 25 Dec 2018

@Stylix It is now merged and tested with PHP 7.0, 7.1, 7.2 and 7.3

Documentation available here: https://devilbox.readthedocs.io/en/latest/advanced/custom-startup-commands.html

FYI: to get the MsSQL container up and running, you can simply add it to docker-compose.override.yml

Image on Dockerhub: https://hub.docker.com/r/microsoft/mssql-server-linux
Image for self-building: https://github.com/Microsoft/mssql-docker
Devilbox documentation: docker-compose.override.yml
Devilbox documentation: Add your own Docker image

cytopia on 27 Dec 2018

Done

@Stylix

Installing Microsoft ODBC driver

cytopia on 31 Dec 2018

@cytopia Thanks a lot ! I try to test tomorrow morning ;)

Stylix on 1 Jan 2019

@cytopia Installation for OBCD driver works very well ! Nice job and thanks a lot !

I also added my MsSQL container to the docker-compose.override.ymland starts well but I have to add ACCEPT_EULAenvironment variable at start. Is there a method to add this at startup like in your cockroad example with command: start --insecure option ?

Here is my docker-compose.override.yml file :


# IMPORTANT: The version must match the version of docker-compose.yml
version: '2.1'

services:
  srv-mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    ports:
      - "${LOCAL_LISTEN_ADDR}1433:1433"
    networks:
      app_net:
        ipv4_address: 172.16.238.200
    # (Optional) For ease of use always automatically start these:
    depends_on:
      - bind
      - php
      - httpd

Stylix on 3 Jan 2019

Just add an environment section and add the variable to your .env file:


# IMPORTANT: The version must match the version of docker-compose.yml
version: '2.1'

services:
  srv-mssql:
    image: mcr.microsoft.com/mssql/server:2017-latest
    environment:
      - ACCEPT_EULA=${ACCEPT_EULA:-no}
    ports:
      - "${LOCAL_LISTEN_ADDR}1433:1433"
    networks:
      app_net:
        ipv4_address: 172.16.238.200
    # (Optional) For ease of use always automatically start these:
    depends_on:
      - bind
      - php
      - httpd

Add the following to .env

# Assuming `yes` as a value is required
ACCEPT_EULA=yes

cytopia on 3 Jan 2019

@cytopia You are the best 👍

Stylix on 3 Jan 2019

@cytopia I have a mistake and i don't find solution...
Have you try to do a PHP script to connect at the MsSQL database ?

I've done 2 tests with the same script :

$serverName = '127.0.0.1';
// Tried with the SQL port but it doesn't work either
//$serverName = '127.0.0.1,1433';
        $connectionOptions = array(
            "Database" => 'myDatabase',
            "Uid" => 'mylogin',
            "PWD" => 'mypassword'
        );
        //Establishes the connection
        $conn = sqlsrv_connect($serverName, $connectionOptions);
        if($conn){
            echo "Connected!";
        }
        else{
            die(
            var_dump(sqlsrv_errors(), true)
            );
        }

First test

Execute script from PHP on my local computer, it works

Second test

Execute script from PHP on my devilbox container, I have this error :

array (size=3)
  0 => 
    array (size=6)
      0 => string 'HYT00' (length=5)
      'SQLSTATE' => string 'HYT00' (length=5)
      1 => int 0
      'code' => int 0
      2 => string '[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired' (length=73)
      'message' => string '[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired' (length=73)
  1 => 
    array (size=6)
      0 => string '08001' (length=5)
      'SQLSTATE' => string '08001' (length=5)
      1 => int 10057
      'code' => int 10057
      2 => string '[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2749' (length=83)
      'message' => string '[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2749' (length=83)
  2 => 
    array (size=6)
      0 => string '08001' (length=5)
      'SQLSTATE' => string '08001' (length=5)
      1 => int 10057
      'code' => int 10057
      2 => string '[unixODBC][Microsoft][ODBC Driver 17 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.' (length=340)
      'message' => string '[unixODBC][Microsoft][ODBC Driver 17 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.' (length=340)

I can connect to the docker machine and also to the SQL server from the command line, but not from PHP. Do you have an idea ?

Stylix on 4 Jan 2019