Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect) when connecting to Azure SQL database from Python running in OpenShift

Question

Only when trying to connect to my Azure DB from Python 3.7 running in a OpenShift container (FROM rhel7:latest) I see the following error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('IM004', "[IM004][unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed (0) (SQLDriverConnect)

I tried the exact same code in Docker on my MAC, Windows and a RHEL7 Virtualbox running the RHEL7 base container - it always works! The problem is only in my container running in OpenShift! I checked that I can telnet to my Azure DB server in 1433 from Openshift.

I enabled the ODBC logs as well but there is no more information than the above error.

What else should I check?

Here is how I set up the MSODBC driver in my Dockerfile:

RUN curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo && \
 yum remove unixODBC-utf16 unixODBC-utf16-devel && \
 ACCEPT_EULA=Y yum install -y msodbcsql17 && \
 yum install -y unixODBC-devel

And here is the code that throws the error:

inside modules.database:

pyodbc_connstring_safe = 'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER='+config.settings["DB_HOST"]+\
                        ';PORT=1433;DATABASE='+config.settings["DB_NAME"]+';UID='+config.usernames["database"]+\
                        ';PWD={};MARS_Connection=Yes'

if config.settings["debug"]:
    print("Using DB connection string: {}".format(pyodbc_connstring_safe.format("SAFE_DB_PASS")))

pyodbc_connstring = pyodbc_connstring_safe.format(config.passwords["database"])

Base = declarative_base()
quoted = urllib.parse.quote_plus(pyodbc_connstring)

def get_engine():
    return create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted), echo=config.settings["debug"], pool_pre_ping=True)

Inside my flask app (the error gets thrown in the call to 'has_table'):

@app.route("/baselinedb", methods=["POST"])
def create_db():
    from modules.database import Base
    engine = database.get_engine()
    if not engine.dialect.has_table(engine, database.get_db_object_name("BaselineDefinition"), schema = 'dbo'):
        Base.metadata.create_all(engine)
    db.session.commit()
    return "OK"

As I mentioned in the beginning, the same Dockerfile gives me a working Container in Docker either locally on Mac or Windows or inside a RHEL7 VM. Thanks for having a look!

Answer 1

unixODBC is trying to find the odbc.ini in the current users home directory. It's trying to do this by looking up the user in /etc/passwd. Since Openshift is using a project specific UID which does not exist in /etc/passwd the user lookup will not work and the connection will fail.

To resolve this add the following to the dockerfile

ADD entrypoint.sh .
RUN chmod 766 /etc/passwd
..
..
ENTRYPOINT entrypoint.sh

And the following in the entrypoint script

export $(id)
echo "default:x:$uid:0:user for openshift:/tmp:/bin/bash" >> /etc/passwd
python3.7 app.py

The above will insert the current user to /etc/passwd during startup of the container.

An alternative and probably better approach might be to use nss_wrapper: https://cwrap.org/nss_wrapper.html

Answer 2

I encountered the same problem while using django on Windows. After upgrading the 'SQL Server 2017 client' to the latest client resolves my issue. Use below link to download latest patch: https://www.microsoft.com/en-us/download/details.aspx?id=56567