Msphpsql: 1617 SQLSTATE [IM004, 0]: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

[bizmate]

Hi I am running the new drivers under php:fpm on docker with the following Dockerfile

FROM php:fpm
MAINTAINER Diego Gullo <[email protected]>

# Add Microsoft repo for Microsoft ODBC Driver 13 for Linux
RUN apt-get update && apt-get install -y \
    apt-transport-https \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update

# Install Dependencies
RUN ACCEPT_EULA=Y apt-get install -y \
    unixodbc \
    unixodbc-dev \
    libgss3 \
    odbcinst \
    msodbcsql \
    locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen && locale-gen

# Install pdo_sqlsrv and sqlsrv from PECL. Replace pdo_sqlsrv-4.1.8preview with preferred version.
RUN pecl install pdo_sqlsrv-4.1.9preview sqlsrv-4.1.9preview \
    && docker-php-ext-enable pdo_sqlsrv sqlsrv

RUN apt-get install -y libpq-dev libcurl4-gnutls-dev \
    && docker-php-ext-install curl pdo pdo_pgsql

When running a query I get the error

1617 SQLSTATE [IM004, 0]: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed

I am using the preview version 9 because the stable packages wont work and give this error when running a build

ERROR: `make' failed
error: /usr/local/lib/php/extensions/no-debug-non-zts-20160303/pdo_sqlsrv does not exist

A full log provided in the file
pecl_install_sqlsrv_Fails.txt

Any suggestions on how to fix the problem is appreciated

[david-puglielli]

@bizmate I'm having trouble reproducing this error. Could you provide the sequence of shell commands you are using and the php script?

[bizmate]

This is a silex app in php and I am just running a select version on the db (the error happens with real apps and complex queries too).
I give the db details through env variables

export DB_DATABASE=master
export DB_PASSWORD=XXXXXXXX
export DB_HOSTNAME=192.168.0.10
export DB_USERNAME=SA

once the script below is running you can call http://localhost:8080/mssql

require_once __DIR__.'/vendor/autoload.php';

use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\Request;

$app = new Silex\Application();

$app->error(function (\Exception $e, Request $request, $code) {
    switch ($code) {
        case 404:
            $message = 'The requested page could not be found.';
            break;
        default:
            $message = 'ERROR : ' . get_class($e) . ' code: ' . $e->getCode() . ' msg: ' . $e->getMessage();
    }

    return new Response($message);
});

$app->get('/hello/{name}', function ($name) use ($app) {
    return 'Hello '.$app->escape($name);
});

$app->get('/info', function () use ($app) {
    return phpinfo();
    //return 'Hello '. $app->escape( json_encode(ini_get_all()) );
});

$app->get('/dbparam', function () use ($app) {
    return '<br>DB_DATABASE: ' . getenv('DB_DATABASE') .
    '<br>DB_USERNAME: ' . getenv('DB_USERNAME') .
    '<br>DB_PASSWORD: ' . getenv('DB_PASSWORD') .
    '<br>DB_HOSTNAME: ' . getenv('DB_HOSTNAME');
});

$app->get('/mssql', function () use ($app) {

    $serverName = getenv('DB_HOSTNAME');
    $connectionOptions = array(
        "Database" => getenv('DB_DATABASE'),
        "Uid" => getenv('DB_USERNAME'),
        "PWD" => getenv('DB_PASSWORD')
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);

    if($conn) {
        echo "Connection established.\n";
    } else {
        echo "Connection could not be established.\n";
        die( print_r( sqlsrv_errors(), true));
    }

    //Select Query
    $tsql= "SELECT @@Version as SQL_VERSION";
    //Executes the query
    $getResults= sqlsrv_query($conn, $tsql);
    //Error handling

    $output = "<h1> Results : </h1>";

    if ($getResults == FALSE){
        $errors = sqlsrv_errors();
        /* Display errors. */
        $output .= "<br/><br/>Error information: " ;

        foreach ( $errors as $error )
        {
            $output .= "<br/> SQLSTATE: ".$error['SQLSTATE'];
            $output .= "<br/>Code: ".$error['code'];
            $output .= "<br/>Message: ".$error['message'];
        }
        throw new Exception($output);
    }

    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
        $output .= "<br/> Sql version : " . $row['SQL_VERSION'];
    }
    sqlsrv_free_stmt($getResults);

    return $output;
});

$app->run();

You can use this docker-compose.yml file

version: '2'

services:
  data:
   image: busybox
   user: $UID
   volumes:
     - ./:/var/www/html/:z

  nginx:
    image: nginx
    ports:
      - "8080:80"
    volumes:
      - ./docker/php7/nginx.conf:/etc/nginx/nginx.conf:ro
    volumes_from:
      - data
    depends_on:
      - php7

  php7:
    build: docker/php7
    user: $UID
    ports:
      - "9000"
    volumes:
      - ./docker/config/php7.ini:/usr/local/etc/php/php.ini
      - ./docker/config/odbcinst.ini:/etc/odbcinst.ini
      - ./docker/config/freetds.conf:/etc/freetds/freetds.conf

    volumes_from:
     - data
    environment:
      - DB_DATABASE
      - DB_USERNAME
      - DB_PASSWORD
      - DB_HOSTNAME
    security_opt:
      - seccomp:unconfined

See the nginx, php, odbcinst and freetds files attached.
odbcinst.ini.txt
php7.ini.txt
freetds.conf.txt
nginx.conf.txt

and you need to composer install to load all the vendor dependencies
composer.json.txt

When you call the page /mssql you will see the error

Connection could not be established. Array ( [0] => Array ( [0] => IM004 [SQLSTATE] => IM004 [1] => 0 [code] => 0 [2] => [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [message] => [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed ) )

[david-puglielli]

@bizmate Thank you. I have a couple of questions:

1. Using the same docker setup, does the error happen if you run regular (non-silex) php scripts that use the sqlsrv driver from the command line?

2. Does the error happen without using docker but using the same silex script that you included above? (It's fine if you can't set up without docker.)

Also, please run the following commands and let us know the output:

odbcinst -j
odbcinst --version
odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

[bizmate]

hi @david-puglielli

thank you for the questions.
About point 1 - yes it happens if you even just take the code in the $app->get('/mssql action and run it in the index file on its own. I have noticed though that at the beginning the code works. Then if I try to run some of the internal app queries that are heavier and not just a version query then the problem comes back.
About point 2 - I have not tried, I think it would be difficult to compare because my host machine has a lot of different software and it is configured with a slightly different version of php. I can try but I dont think it would be representative of the problem.

I think the problem might be in odbc somewhere but I have no clue on how to spot it. Here are the commands execs as you asked before

/var/www/html/app$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

/var/www/html/app$ odbcinst --version
unixODBC 2.3.1

odbcinst -q -d -n "ODBC Driver 13 for SQL Server"
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0
UsageCount=1
Trace=Yes

As you can see in the last command that is exactly what I load through the file and has tracing enabled. I assumed this would show me further details of why the problem is happening but so far my limited experience with odbc and mssql has not helped.

[bizmate]

@david-puglielli Unsure if you had any time to look at the above but I was wondering if this applied to the sqldriver too when using unixodbc

• http://stackoverflow.com/questions/24444326/connecting-to-db2-databaseunixodbcdriver-managerdrivers-sqlallochandle-on
  
  
  
  • http://www-01.ibm.com/support/docview.wss?uid=swg21229860
  
  

In the container i dont see any special groups for unixodbc or sqlsrv to be able to add www-data into that group. Any thoughts?

[david-puglielli]

@bizmate I am still unable to reproduce the issue. I will tell you what I have done to reproduce your environment:

After starting a clean Ubuntu 16.04 VM, I download the Dockerfile, docker-compose.yml, odbcinst.ini, freetds.conf, php7.ini, nginx.conf, and compose.json files as you link above. I download the silex script as index.php. I run the following commands:

mkdir -p ~/github400/docker/config ~/github400/docker/php7
cd ~/Downloads
cp *.ini freetds.conf ~/github400/docker/config/
cp nginx.conf Dockerfile ~/github400/docker/php7/
cp docker-compose.yml index.php composer.json ~/github400/
cd
sudo apt-get update
sudo apt-get install docker*
sudo apt-get install composer php-dev
cd ~/github400
composer install
sudo docker-compose up

At this point I open my browser and enter localhost:8080/mssql, and (after substituting the environment variables directly in index.php) it works as expected.

Does this match your environment? Am I missing anything?

[bizmate]

@david-puglielli by it works as expected it means you see the string/version of mssql and the query is running? Unfortunately I see this error

Connection could not be established. Array ( [0] => Array ( [0] => IM004 [SQLSTATE] => IM004 [1] => 0 [code] => 0 [2] => [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed [message] => [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed ) )

as I reported previously. To give you a click and reproduce option I have created this repo
https://github.com/bizmate/mssql-odbc-err
Same as you did locally you have everything here. In the readme the instruction to take the docker infrastructure up and set up the env variables (you can rename them in the code too)

Once done you can try again and pls let me know? I have added in the README the version of the mssql server I have installed on my machine (on my host) not using docker for DBs locally at the moment

[david-puglielli]

@bizmate I am still unable to reproduce the error using your repo. However I do get another error telling me that the msodbcsql library is not found: Connection could not be established. Array ( [0] => Array ( [0] => 01000 [SQLSTATE] => 01000 [1] => 0 [code] => 0 [2] => [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0' : file not found [message] => [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.7.0' : file not found ) )

Is there a problem with the repo, or does it seem like this is related to your issue?

The error message you are getting leads me to think it may be due to a conflict involving the version(s) of unixODBC you have installed. Have you tried reproducing the error in a clean Ubuntu machine (a VM that is)?

[bizmate]

Hi @david-puglielli
I reproduced the error you are getting and I fixed it. Your error was due to the fact that the odbc driver version has changed in the meantime from a .7.0 version to .8.0 version as per commit.
https://github.com/bizmate/mssql-odbc-err/commit/4f694b72355ac91bfac918aaaa9b499219928be5

Can you try pulling the fix from my repository? Once pulled you can make down and make up. See the page and you will see the error. I have reproduced it in a totally separate machine

[david-puglielli]

@bizmate Aha! I can now reproduce your error. The reason I could not reproduce it earlier is because I was calling sudo make up. However now I have added my local user to the docker group and now I get the same error message you do. I will continue to investigate.

[bizmate]

hi @david-puglielli
did you get any idea of what might be causing the problem?

[ulvii]

Hi @bizmate ,

@david-puglielli is on vacation, I will be trying to help you :)

I just set up a clean Ubuntu 16.04 VM and tried the following steps:

sudo apt-get install php php-xml   #to install composer
wget https://github.com/bizmate/mssql-odbc-err/archive/master.tar.gz
tar -xf master.tar.gz
cd mssql-odbc-err-master/
wget https://getcomposer.org/installer -O composer-setup.php
php composer-setup.php
php composer.phar install
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install -y docker-ce
sudo curl -o /usr/local/bin/docker-compose -L "https://github.com/docker/compose/releases/download/1.11.2/docker-compose-$(uname -s)-$(uname -m)"
sudo chmod +x /usr/local/bin/docker-compose
sudo make up

Setting database credentials as environment variables did not work for me, so I just hard-coded them inside index.php file. The script executed fine without any errors and I actually got @@Version back. Do you think the commands that I posted above could be helpful? If not, we will have to wait for @david-puglielli. Looks like he managed to reproduce the the error you are getting.

[david-puglielli]

To reproduce the error you must add your local user to the docker group so that you need not call make up with sudo. This suggests that there is some kind of permissions issue involved. The error also occurs when calling a simple script from the command line without silex or any other packages, so the problem does not seem to be linked to nginx or anything composer-related.

[bizmate]

@david-puglielli I have played a little with the setup based on your spot on suggestion about permission but I cannot really find a reason for the bug. It might be one of those things where a specific type or permission or host resource must be shared with the container to avoid problems. As it looks like an ODBC specific error do you have any suggestion on how this should be set up? Maybe an extra instruction in the Dockerfile would be able to fix this

[yitam]

Hi @bizmate , as indicated by @david-puglielli , this is highly likely a permission issue involving docker.

FYI, the error message usually means the driver manager couldn't find the resource file (msodbcsql13.rll or msodbcsql17.rll) - on Linux and Mac. For example, it looks for the .so in a hardcoded relative path or something like that.

[timsavage]

I've been getting a suspiciously similar error when using "ODBC Driver 13 for SQL Server" driver (in my case it's a Python application however the error looks to be in the ODBC driver rather than the PHP or Python wrappers).

My setup is as follows:

• Docker container based on Centos 7
• Dockerfile installs MSSQL ODBC driver
• docker-compose binds in the /etc/odbc.ini file with the details of my SQLServer instance
• docker-compose is configured with the user option to have the uid mapped to my current user.
• odbcinst -q -d reports "ODBC Driver 13 for SQL Server" installed

I think the issue is related to the user that is being used as if I remove the user option from my docker-compose file everything works as expected when attempting to connect using isql.

My question is, therefore, what files are being accessed during the SQL_HANDLE_HENV call so that permissions can be set correctly? Would this also solve the op's issue?

[lolli42]

My dockerized PHP errored out with "Driver's SQLAllocHandle on SQL_HANDLE_HENV failed" if not executed as root, too. Solution was to map host's /etc/passwd and /etc/group into the container. Using docker-compose.yml:

volumes:
      - /etc/passwd:/etc/passwd:ro
      - /etc/group:/etc/group:ro

[david-puglielli]

Closing due to inactivity. Please reopen if necessary.

[jakjohnson]

This seems to occur when the UID of the process using the ODBC driver is not in /etc/passwd.
A simple fix is to create a user having that UID.

This could explain why running as root solves this issue, as UID 0 is always present.

We experienced this issue when using running pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server},...) from inside a Docker container started by Jenkins using the UID of the jenkins user of the host. This UID had no corresponding user inside our container.

[yitam]

Thanks @jakjohnson. Hope the others find this helpful.