Drivers for Microsoft SQL Server Not found From Python

Docker Desktop
1

I have set up a Microsoft SQL Server and Flask application (Python 3.8) that is going to interracted with the db. I am using Docker Desktop 2.1.0.5 for Mac OS X 10.14.6.

There is no Dockerfile or folder set up for mssql, the only conifguration I have set up for it is as below.

  version: "3.7"
  1 
  2 services:
  3         backend:
  4                 build: ./flask
  5                 image: backend
  6                 container_name: backend
  7                 restart: always
  8                 volumes:
  9                         - ./flask:/usr/src/app
 10                 ports:
 11                         - 5000:5000
 12                 command: python manage.py run -h 0.0.0.0
 13                 env_file:
 14                         - .env
 15                 depends_on:
 16                         - mssql
 17                 networks:
 18                         - microsoftSQL
 19         mssql:
 20                 image: mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04
 21                 container_name: sql1
 22                 restart: always
 23                 ports:
 24                         - 1433:1433
 25                 environment:
 26                         - ACCEPT_EULA=Y
 27                         - SA_PASSWORD=${SQL_PASSWORD}
 28                 networks:
 29                         - microsoftSQL
 30 networks:
 31         microsoftSQL:
 32                 driver: bridge

If I run docker-compose up --build there are no exit errors, sql1 logging is quite noisy on start-up but from what I can tell, none of the logs would concern me. If these start up logs prove of importance I will get about uploading them to a public git repo for analysis.

Once the container is up, I can interract with the db by following the instructions documented here:

Which shows me that the db is up and running as I might expect and that i can add and retrieve data from the CLI.

Things become problematic when I interract with it from my Flask app. Within my views module I have a test method as follows:

 @main_blueprint.route( "/dbTest", methods = ['GET'])
 def db_test():
     """ 
     Create test of the form:
 
         SELECT * FROM Inventory WHERE quantity > 152;
         GO;
 
     """
     conn = pyodbc.connect(
          'Driver={ODBC Driver 17 for SQL Server};'
          f'Server={server_name};'
          'Port:{port};'
          f'Database={db_name};'
          f'UID={uid};'
          f'PWD={pwd};'
          'Trusted_Connection=yes;')
  
      cursor = conn.cursor()
      cursor.execute( f'SELECT * FROM {db_name}.Inventory WHERE quantity > 152')
  
      return jsonify( cursor)

Which returns the following error as the last message in the stack trace:

backend | pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

This is an error message which can be seen elsewhere on Google, with many queries pointing users to replacing the Driver parameter with the path in the file containing the drivers, which in this case is /etc/odbcinst.ini from within the sql bash.

mssql@************:/$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/mssql/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
mssql@************/$

Which is /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1

mssql@************:/$ cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1
UsageCount=1
mssql@************:/$

This yields the same error:

backend | pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1' : file not found (0) (SQLDriverConnect)")

The file of course does exist:

mssql@************:/$ ls /opt/microsoft/msodbcsql17/lib64/
libmsodbcsql-17.4.so.2.1

And I’ve seen some suggestions that I replace the driver parameter with the first line from the driver file ( /opt/microsoft/msodbcsql17/lib64/) but this simply can’t be right as the file contains largely binary characters:

mssql@************:/$ head -n1 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1
ELF>??@p?@8 xx?x?? ?????p???$$P?tdPjPjPj?L?LQ?tdR?tdxx?x?????GNU??6HG?Z??1???ft-??,

Any help on this would be greatly appreciated!

Thanks in advance,
Jacob