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

Hi guys, I already had a look at previous similar issues but I could not find a proper solution. I am using a Jupyter notebook on Azure ML compute instance. I am trying to connect an Azure SQL database using pyodbc but I get below error: _(‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 17 for SQL Server’ : file not found (0) (SQLDriverConnect)”)_

My code:

import pyodbc
server = 'tcp:xxx.database.windows.net'
database = 'xxxx'
username = 'xxx'
password = 'xxxxx'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Environment

Kernel: Python 3.6 Azure ML
Python: 3.6.9 |Anaconda, Inc. [GCC 7.3.0]
pyodbc: 4.0.27
OS: 16.04.6 LTS (Xenial Xerus)
DB: Azure SQL
driver: ODBC Driver 17 for SQL

Issue

Expected behaviour: I expect to have a successfull connection, Observerd behaviour: No connection to the DB because it can not locate ODBC Driver 17 for SQL.

Extra information from similar questions online: 1- There is no microsoft folder in /opt folder (referring this question and solutions) 2- There is no odbcinst file in /etc folder (referring this question and solutions) 3- When I try pyodbc.drivers() it returns an empty array. (I uninstalled and reinstalled pyodbc, I am able to import it.) 4- I can not locate msodbcsql17 (referring here) 4- I tried ODBC Driver 13 as well, same problem exists.

Any help will be appreciated!

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 34 (7 by maintainers)

Most upvoted comments

Did you actually install the driver as per the instructions here?

+24

gordthompson on Feb 27, 2020

@gabraham00 - If pyodbc.drivers() is returning an empty list then your unixODBC is not configured correctly. It should look like this:

gord@vbox-Xubu-20-04-a:~$ 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/gord/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
gord@vbox-Xubu-20-04-a:~$ 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.6.so.1.1
UsageCount=1

+11

gordthompson on Sep 27, 2020

If even after you install /opt/microsoft and /etc/odbcinst.ini etc. are still missing, then something is going wrong with the installation, you may want to look at what the output of sudo apt install msodbcsql17, to see what’s the error.

+10

v-makouz on Apr 15, 2020

Just figured out that the issue is that I’m running this on mac m1. Tried to build this image on Ubuntu and it works. Just in case if anyone has this problem.

dmydry on Apr 19, 2022

It looks like this problem is when trying to upload a container on Mac M1. Does anyone know if there is already a solution?

paulosscruz on Jul 15, 2022

@jigisha9 If you have Trusted_Connection=yes; in you connection string it doesn’t use username and password, but tries to authenticate automatically using Kerberos, which doesn’t appear to be working based on that SSPI Provider error. If you can connect using username and password, I would suggest using those and not Trusted_Connection.

v-makouz on Aug 9, 2021

I had the same problem and managed to solve it by installing the MS driver like @jigisha9 mentioned. This is the Docker file I produced:


FROM python:3.8.12-alpine

RUN apk update
RUN apk add gcc libc-dev g++ libffi-dev libxml2 unixodbc-dev unixodbc mariadb-dev libstdc++6
RUN apk add bash icu-libs krb5-libs libgcc libintl libssl1.1 libstdc++ zlib curl gnupg

#Download the desired package(s)
RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.8.1.1-1_amd64.apk
RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.8.1.1-1_amd64.apk

#(Optional) Verify signature, if 'gpg' is missing install it using 'apk add gnupg':
RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/msodbcsql17_17.8.1.1-1_amd64.sig
RUN curl -O https://download.microsoft.com/download/e/4/e/e4e67866-dffd-428c-aac7-8d28ddafb39b/mssql-tools_17.8.1.1-1_amd64.sig

RUN curl https://packages.microsoft.com/keys/microsoft.asc  | gpg --import -
RUN gpg --verify msodbcsql17_17.8.1.1-1_amd64.sig msodbcsql17_17.8.1.1-1_amd64.apk
RUN gpg --verify mssql-tools_17.8.1.1-1_amd64.sig mssql-tools_17.8.1.1-1_amd64.apk

#Install the package(s)
RUN apk add --allow-untrusted msodbcsql17_17.8.1.1-1_amd64.apk
RUN apk add --allow-untrusted mssql-tools_17.8.1.1-1_amd64.apk


RUN mkdir /code
WORKDIR /code
ADD . /code/
RUN pip install -r requirements.txt

EXPOSE 8000

CMD ["python","/code/manage.py","runserver","0.0.0.0:8000"]

onepointconsulting on Dec 1, 2021

finally I am able to complete it. just taking out Trusted_Connection=yes; will resolve issue.

Thank you sooo much v-makouz

jigisha9 on Aug 9, 2021

@jigisha9 Does pyodbc.drivers() also return an empty string?

Also what happens if you do isql -v -k "DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx"?

v-makouz on Aug 5, 2021

Not working for me using Ubuntu 20. I connect successfully using isql. But I think something it’s missing when I tried to get the drivers with pyodbc.

pyodbc.drivers() —> not working with Linux only Windows pyodbc.dataSources() {‘ODBC Driver 17 for SQL Server’: ‘/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1’, ‘testmssql’: ‘/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1’}

Any guess? Thanks!

gabraham00 on Sep 27, 2020