Mssql-docker: Get error when using ODBC Driver 17 on latest python image (Debian Buster)

[arunchandarQA]

Microsoft ODBC Driver version

msodbcsql17

Docker image

python

Problem description

Latest python image uses Debian 10 (Buster). Connecting to SQL server gives
file not found error:

```ht = 2, h = c_void_p(94831981332832), val_ret = -1, ansi = False

def ctrl_err(ht, h, val_ret, ansi):
    """Classify type of ODBC error from (type of handle, handle, return value)
    , and raise with a list"""

    if ansi:
        state = create_buffer(22)
        Message = create_buffer(1024*4)
        ODBC_func = ODBC_API.SQLGetDiagRec
        if py_v3:
            raw_s = lambda s: bytes(s,'ascii')
        else:
            raw_s = str_8b
    else:
        state = create_buffer_u(24)
        Message = create_buffer_u(1024*4)
        ODBC_func = ODBC_API.SQLGetDiagRecW
        raw_s = unicode
    NativeError = ctypes.c_int()
    Buffer_len = c_short()
    err_list = []
    number_errors = 1

    while 1:
        ret = ODBC_func(ht, h, number_errors, state, \
            ADDR(NativeError), Message, 1024, ADDR(Buffer_len))
        if ret == SQL_NO_DATA_FOUND:
            #No more data, I can raise
            #print(err_list[0][1])
            state = err_list[0][0]
            err_text = raw_s('[')+state+raw_s('] ')+err_list[0][1]
            if state[:2] in (raw_s('24'),raw_s('25'),raw_s('42')):
                raise ProgrammingError(state,err_text)
            elif state[:2] in (raw_s('22')):
                raise DataError(state,err_text)
            elif state[:2] in (raw_s('23')) or state == raw_s('40002'):
                raise IntegrityError(state,err_text)
            elif state == raw_s('0A000'):
                raise NotSupportedError(state,err_text)
            elif state in (raw_s('HYT00'),raw_s('HYT01'),raw_s('01000')):

              raise OperationalError(state,err_text)

E pypyodbc.OperationalError: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1' : file not found")

/usr/local/lib/python3.7/site-packages/pypyodbc.py:983: OperationalError```

Problem is only with this version, previous one (python:3.7.4-stretch) works fine.

I could not find installation instructions in the below page fr Debian 10:
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

[tracker1]

Try the version 9 driver, which may or may not work.

After running:

curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list

• Edit /etc/apt/sources.list.d/mssql-release.list
• Replace the Debian Codename stretch (9) with buster (10)
• continue with the rest of the instructions

Doing this actually does work a lot of the time for when you are bumping to a new version of Debian or Ubuntu derivatives.

[AlaricWhitney]

I've followed the exact steps for Debian 10 using the microsoft installation doc. The driver installs, but the driver does not work.

Steps to reproduce:

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

# Get ODBC connection setup
echo "[MySQLServerDatabase]
Driver=ODBC Driver 17 for SQL Server
Description=My MS SQL Server
Trace=No
Server=server_name\instance_name,1433
Database=database" > ./temp
sudo odbcinst -i -s -f ./temp -l
rm ./temp

# test by running
isql -v  MySQLServerDatabase username 'password'
# Output:
# > [08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746
# > [08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection
# > [ISQL]ERROR: Could not SQLConnect

Repeating these steps using Debian 9 works fine (obviously using https://packages.microsoft.com/config/debian/9/prod.list location).

[leonboot]

I found this thread while googling for docker debian 10 "Error code 0x2746".

This issue seems to be common to all Buster based Docker images. One of my projects uses php:7.2-apache images and it now also fails to connect to an MS SQL server. I haven't figured out what the cause is, but based on this info I'm now basing my Docker image off of php:7.2-apache-stretch and the error goes away.

So it might be worth checking if there is a specific Stretch based image if you run into this issue.

[sweting]

I got the same error in a debian buster virtual machine. The error occurs If I try to connect to an mssql2012 vor 2016 instance. The connection to an mssql2017 works without any errors.

[grogy]

For me works this Dockerfile:

FROM php:7.3-fpm-stretch

RUN apt-get update \
    && apt-get install -y gnupg

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/9/prod.list \
        > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get install -y --no-install-recommends \
        locales \
        apt-transport-https \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && apt-get update \
    && apt-get -y --no-install-recommends install \
        unixodbc-dev \
        msodbcsql17

RUN docker-php-ext-install mbstring pdo pdo_mysql mysqli \
    && pecl install sqlsrv pdo_sqlsrv xdebug \
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug mysqli

Thanks for comments up