MsSQL support

[Stylix]

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.

[cytopia]

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

[Stylix]

@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 ;)

[cytopia]

@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]

@Stylix PR is open and can be checked out: #445

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

[cytopia]

@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]

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]

@Stylix

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

1. So checkout the latest Devilbox master branch and start up the Devilbox
2. Enter the PHP container: ./shell.sh
3. become root: sudo su -
4. 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: #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.

[Stylix]

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

[Stylix]

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 ?

[cytopia]

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]

@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: devilbox/docker-php-fpm#78

[Stylix]

@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'

[cytopia]

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]

Devilbox PR is open: #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]

@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]

Done

@Stylix

• Installing Microsoft ODBC driver

[Stylix]

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

[Stylix]

@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

[cytopia]

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

[Stylix]

@cytopia You are the best 👍

[Stylix]

@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 ?

[cytopia]

$serverName = '127.0.0.1';

Don't use 127.0.0.1, use the Docker hostname

[Stylix]

You are definitely the best!

And I'm definitely stupid 😅

[cytopia]

@Stylix good to hear it works now :-)

I am currently moving anything not related to Devilbox bugs into the new Forum here:

https://devilbox.discourse.group

So for all further non-bug requests, please go there. This will also allow for other people to catch your issue and respond.

[Stylix]

Nice thing and good idea ! Already in my favorites ;)