Mssql-docker: pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'driver' : file not found (0) (SQLDriverConnect)")

[przemolb]

Hi all,
I am trying to use a docker image built from https://github.com/Microsoft/mssql-docker/blob/master/oss-drivers/pyodbc/Dockerfile (and added python3) to connect to Azure SQL database.

import pyodbc
driver = 'ODBC Driver 17 for SQL Server'
server = 'sql_srv'
database = 'mydb'
uid = 'myadmin'
pwd = 'mypass'
con_string = r'DRIVER={driver};SERVER={server};DATABASE={database};UID={uid};PWD={pwd}'
cnxn = pyodbc.connect(con_string)

cursor = cnxn.cursor()

print ('Using the following SQL Server version:')
tsql = "SELECT @@version;"
with cursor.execute(tsql):
    row = cursor.fetchone()
    print (str(row[0]))

but I get the following error:

Traceback (most recent call last):
  File "/sample.py", line 22, in <module>
    cnxn = pyodbc.connect(con_string)
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'driver' : file not found (0) (SQLDriverConnect)")

Can anyone hint me please what is missing ?

[forkazmoden]

Hi,

[unixODBC][Driver Manager]Can't open lib 'driver' : file not found (0) (SQLDriverConnect)")

The error indicates that the configuration file of the ODBC driver is not configured well. Please check it out with odbcinst -j

[przemolb]

root@49299e8d95f7:/sample# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@49299e8d95f7:/sample# cat /etc/odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2
UsageCount=1

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

root@49299e8d95f7:/sample# cat /etc/odbc.ini
root@49299e8d95f7:/sample# ls -al /etc/ODBCDataSources/
total 8
drwxrwxr-x 2 root root 4096 Dec  5 23:17 .
drwxr-xr-x 1 root root 4096 Apr 23 21:18 ..

What is missing/wrong ?

[przemolb]

Any hint what is missing ?

[behconsci]

@przemolb having same issue here, did you solve it?

[przemolb]

@behconsci yes, I did:

drivers = [item for item in pyodbc.drivers()]
driver = drivers[-1]
print("driver:{}".format(driver))
server = 'myserver'
database = 'mydb'
uid = 'myuser'
pwd = 'mypass'
con_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={uid};PWD={pwd}'
print(con_string)
cnxn = pyodbc.connect(con_string)
...

[selvakarna]

How to connect SQL Using pyodbc Linux OS? I got error below when i use above code?

raise Exception('This function is available for use in Windows only.')

Exception: This function is available for use in Windows only.

[yoshi-kim]

can you give an example of what you mean by "drivers = [item for item in pyodbc.drivers()]"

[jborichevskiy]

can you give an example of what you mean by "drivers = [item for item in pyodbc.drivers()]"

It looks like @przemolb is simply making a list of the returned drivers from the pyodbc.drivers() function and using the last one in the list with [-1].

In my case, here is what that looks like:

In [34]: pyodbc.drivers()
Out[34]: ['ODBC Driver 13 for SQL Server']

In [35]: [item for item in pyodbc.drivers()][-1]
Out[35]: 'ODBC Driver 13 for SQL Server'

In [36]: driver = [item for item in pyodbc.drivers()][-1]

In [37]: driver
Out[37]: 'ODBC Driver 13 for SQL Server'

This driver string should now be passed as the parameter in the connection string like so:

con = pyodbc.connect('DRIVER={driver};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

assuming that you've set the server, database, username, password variables before this.

For reference, I'm using this guide.

[yoshi-kim]

The connection string i'm attempting to connect with is as follows:
conn_string = "\
driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so; \
host=server.example.com; \
port=21050; \
database=default; \
AuthMech=1; \
KrbFQDN=server.example.com; \
KrbRealm=EXAMPLE.COM; \
KrbServiceName=impala; \
UseSASL=1; \
SSL=1; \
CAIssuedCertNamesMismatch=0; \
TrustedCerts=/opt/cloudera/impalaodbc/lib/64/cacerts.pem; \
Trusted_Connection=yes"

conn = pyodbc.connect(conn_string, autocommit=True)

Traceback (most recent call last):
File "", line 1, in
pyodbc.OperationalError: ('08S01', '[08S01] [Cloudera][ImpalaODBC] (100) Error from the Impala Thrift API: SASL(-1): generic failure: GSSAPI Error: Unspecified GSS failure. Minor code may provide more information (No Kerberos credentials available (default cache: KEYRING:persistent:123456789)) (100) (SQLDriverConnect)')

Is it possible to use a krb service name instead of a uid?

[anjanwahwar]

Hi, This error indicates the missing ODBC driver while building the docker images. I have got the same issue and resolved the same by explicitly providing instruction to install Linux ODBC driver.

Write below instruction in your docker file to make it work:
-======================================
RUN apt-get install curl
RUN apt-get install apt-transport-https
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | tee /etc/apt/sources.list.d/msprod.list

RUN apt-get update
ENV ACCEPT_EULA=y DEBIAN_FRONTEND=noninteractive
RUN apt-get install mssql-tools unixodbc-dev -y

[vishnusagittarius]

Im facing this issue