6

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!

Denis Schlesinger
  • 65
  • 1
  • 4
  • Are you using the Flask-SQLAlchemy extension or the Flask-Login extension for OpenShift? This tutorial covers both and might help you double-check your configuration in the OpenShift environment: https://blog.openshift.com/use-flask-login-to-add-user-authentication-to-your-python-application/ – Mike Ubezzi MSFT Apr 12 '19 at 18:33
  • Thanks @MikeUbezziMSFT, I am using Flask-SQLAlchemy. I will have a look at the link! – Denis Schlesinger Apr 30 '19 at 07:20

2 Answers2

8

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

Gord Thompson
  • 98,607
  • 26
  • 164
  • 342
Filip Reimer
  • 96
  • 1
  • A very good example of how to solve this in your Docker image using nss_wrapper can be found here: https://github.com/openshift/jenkins/pull/1024/files – Tom Desair May 08 '20 at 13:19
  • @filip-reimer do you know how we can do this for a debian 10? The solution you provided seems to be only for openshift – knguyen Aug 12 '20 at 10:43
0

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

mohanish
  • 1