ODBC Driver 13 for SQL Server can't open lib on pyodbc while connecting on ubuntu docker image

Question

I was told to ask this question separately but it is related to this question here.

I am having the exact problem on a docker image I created using official Ubuntu (16.04). It works from isql, but not via pyodbc connection. Below is the odbc trace:

[ODBC][60][1487069096.117665][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x1458c20
[ODBC][60][1487069096.117687][SQLSetEnvAttr.c][189]
                Entry:
                        Environment = 0x1458c20
                        Attribute = SQL_ATTR_ODBC_VERSION
                        Value = 0x3
                        StrLen = 4
[ODBC][60][1487069096.117695][SQLSetEnvAttr.c][363]
                Exit:[SQL_SUCCESS]
[ODBC][60][1487069096.117702][SQLAllocHandle.c][375]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x1458c20
[ODBC][60][1487069096.117709][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x148ab10
[ODBC][60][1487069096.117719][SQLDriverConnectW.c][290]
                Entry:
                        Connection = 0x148ab10
                        Window Hdl = (nil)
                        Str In = [SERVER=server;DATABASE=db;UID=user;PWD=pwd;DRIVER={ODBC Driver 13 for SQL Server};][length = 116]
                        Str Out = (nil)
                        Str Out Max = 0
                        Str Out Ptr = (nil)
                        Completion = 0
                UNICODE Using encoding ASCII 'UTF8' and UNICODE 'UTF16LE'

[ODBC][60][1487069096.118365][SQLConnect.c][1114]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.1.0' : file not found
[ODBC][60][1487069096.118384][SQLDriverConnect.c][726]
                Entry:
                        Connection = 0x148ab10
                        Window Hdl = (nil)
                        Str In = [SERVER=server;DATABASE=database;UID=user;PWD=********;DRIVER={ODBC Driver 13 for SQL Server};][length = 116 (SQL_NTS)]
                        Str Out = 0x7ffc2880f570
                        Str Out Max = 2048
                        Str Out Ptr = (nil)
                        Completion = 0
                UNICODE Using encoding ASCII 'UTF8' and UNICODE 'UTF16LE'

[ODBC][60][1487069096.118786][SQLConnect.c][1114]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.1.0' : file not found
[ODBC][60][1487069096.118802][SQLGetDiagRec.c][680]
                Entry:
                        Connection = 0x148ab10
                        Rec Number = 1
                        SQLState = 0x7ffc28810160
                        Native = 0x7ffc2881014c
                        Message Text = 0x7ffc28810170
                        Buffer Length = 1023
                        Text Len Ptr = 0x7ffc2881014a
[ODBC][60][1487069096.118816][SQLGetDiagRec.c][717]
                Exit:[SQL_SUCCESS]
                        SQLState = 01000
                        Native = 0x7ffc2881014c -> 0
                        Message Text = [[unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.1.0' : file not found]
[ODBC][60][1487069096.118832][SQLFreeHandle.c][284]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x148ab10
[ODBC][60][1487069096.118839][SQLFreeHandle.c][333]
                Exit:[SQL_SUCCESS]

Here's my dockerfile:

FROM ubuntu:latest

RUN locale-gen en_US.UTF-8
ENV LANG en_US.UTF-8
ENV LANGUAGE en_US:en
ENV LC_ALL en_US.UTF-8

RUN apt-get update && \
    apt-get clean && \
    apt-get -y install curl build-essential \
    libssl-dev libldap2-dev libffi-dev libpq-dev apt-transport-https dialog

RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get -y install msodbcsql=13.1.1.0-1 mssql-tools && \
    apt-get -y install unixodbc-dev-utf16

CMD ["bin", "bash"]

Answer 1

Thanks to Meet and his buddy, Luis, at Microsoft, I was able to use conda distribution with pyodbc in a docker container to connect with SQL server. Below is the dockerfile that they configured for me -

# mssql-python-pyodbc
# Python runtime with pyodbc to connect to SQL Server
FROM ubuntu:16.04

# apt-get and system utilities
RUN apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-5\
    && rm -rf /var/lib/apt/lists/*

# adding custom MS repository
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# install SQL Server drivers
RUN apt-get update && ACCEPT_EULA=Y apt-get -y install msodbcsql 
RUN apt-get -y install unixodbc unixodbc-dev

# install SQL Server tools
RUN apt-get update && ACCEPT_EULA=Y apt-get -y install mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

# python libraries
RUN apt-get update && apt-get install -y \
    python-pip python-dev python-setuptools \
    --no-install-recommends \
    && rm -rf /var/lib/apt/lists/*

# install necessary locales
RUN apt-get update && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen
RUN pip install --upgrade pip

# install SQL Server Python SQL Server connector module - pyodbc
RUN pip install pyodbc

RUN curl -LO https://repo.continuum.io/archive/Anaconda2-4.3.0-Linux-x86_64.sh && \
    bash Anaconda2-4.3.0-Linux-x86_64.sh -p /Anaconda -b && \
    rm Anaconda2-4.3.0-Linux-x86_64.sh && \
    rm -rf /var/lib/apt/lists/*

ENV PATH $PATH:/Anaconda/bin
RUN conda update -y conda


# add sample code
RUN mkdir /sample
ADD . /sample
WORKDIR /sample