PyODBC : can't open the driver even if it exists

Question

I'm new to the linux world and I want to query a Microsoft SQL Server from Python. I used it on Windows and it was perfectly fine but in Linux it's quite painful.

After some hours, I finally succeed to install the Microsoft ODBC driver on Linux Mint with unixODBC.

Then, I set up an anaconda with python 3 environment.

I then do this :

import pyodbc as odbc

sql_PIM = odbc.connect("Driver={ODBC Driver 13 for SQL Server};Server=XXX;Database=YYY;Trusted_Connection=Yes")

It returns :

('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0' : file not found (0) (SQLDriverConnect)")

The thing I do not undertsand is that PyODBC seems to read the right filepath from odbcinst.ini and still does not work.

I went to "/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0" and the file actually exists !

So why does it tell me that it does not exist ? Here are some possible clues :

I'm on a virtual environment
I need to have "read" rights because it's a root filepath

I do not know how to solve either of these problems.

Thanks !

It could be a missing library (a library used by libmsodbcsql-13.0.so.0.0) or a LD_LIBRARY_PATH issue. Could you please share the result of the following command? `ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0` — mauro, Jan 14 '16 at 14:57
Related: [ODBC Driver 13 for SQL Server can't open lib on pyodbc](https://stackoverflow.com/q/41182415/55075). — kenorb, Oct 24 '17 at 15:05

score 15 · Accepted Answer · edited Oct 24 '17 at 15:07

I also had the same problem on Ubuntu 14 after following the microsoft tutorial for SQL Server Linux ODBC Driver.

The file exists and after running an ldd, it showed there were dependencies missing:

/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0: /usr/lib/x86_64-linux-gnu/libstdc++.so.6: version GLIBCXX_3.4.20' not found (required by /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0) /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.0.0: /usr/lib/x86_64-linux-gnu/libstdc++.so.6: versionCXXABI_1.3.8' not found (required by

after searching for a while I found its because Ubuntu's repo didnt have GLIBCXX on version 3.4.20, it was at 3.4.19.

I then added a repo to Ubuntu, updated it and forced it to upgrade libstdc++6

sudo add-apt-repository ppa:ubuntu-toolchain-r/test 
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install libstdc++6

Problem solved, tested with isql:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

After that I tried testing using pdo_odbc (PHP), it then gave me the same driver not found error. To solve this I had to create a symbolic link to fix libodbcinst.so.2:

sudo ln -s /usr/lib64/libodbcinst.so.2 /lib/x86_64-linux-gnu/libodbcinst.so.2

Thanks, I also fount his solution. than I have a problem with the connection to my database but that is another story. ! — Joseph Yourine, Feb 01 '16 at 08:48

score 8 · Answer 2 · edited Oct 23 '18 at 02:19

8

I had the same problem 'file not found (0) (SQLDriverConnect)' on MAC OS with the following code

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER=myServerIP,1433;DATABASE=myDBName;UID=sa;PWD=dbPassword')

after googling for two days, I cannot fix the issue even modify the freetds.conf, odbcinst.ini and odbc.ini

finally, I found the solution via replacing DRIVER value

cnxn = pyodbc.connect('DRIVER={/usr/local/lib/libmsodbcsql.13.dylib};SERVER=myServerIP,1433;DATABASE=myDBName;UID=sa;PWD=dbPassword')

My dev environment

MAC OS El Capitan
python 3.6.1 in Anaconda

edited Oct 23 '18 at 02:19

Cœur

37,241
25
195
267

answered Jun 14 '17 at 07:54

Karen Chang

121
1
2

Hello please can you comment on this? I tried doing this this, and I am sure that the .dylib file exists in the folder which I specified. But it still gives me the error. 01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/home/environment/msodbcsql-17.1.0.1/lib/libmsodbcsql.17.dylib' : file not found (0) (SQLDriverConnect)", can you suggest anything I may be doing wrong? – Kikanye Mar 07 '20 at 22:42
cnxn = pyodbc.connect('DRIVER={/home/environment/msodbcsql-17.1.0.1/lib/libmsodbcsql.17.dylib};SERVER=XXXXX;DATABASE=DB;UID=XXXXXX;PWD=XXXXX) is what I had in my code – Kikanye Mar 07 '20 at 22:45
I tried this and I got the following error: pyodbc.Error: ('HY000', '[HY000] [IBM][System i Access ODBC Driver]Key value in connection string too long. (30119) (SQLDriverConnect)') This is my connection string: DRIVER={/opt/ibm/iaccess/lib/libcwbodbc.dylib};SYSTEM=xxxxx;SIGNON=3;UID=xxxxx;PWD=xxxxx; – pmalbu Jun 28 '20 at 02:53
I can confirm that using absolute path to driver works on my MacOS machine. My driver is: "/usr/local/lib/libmsodbcsql.17.dylib". – Nguyen Aug 21 '20 at 00:51

sanhitamj · Answer 3 · 2017-05-30T21:02:32.230

4

I found an answer that works for me here. This is for python 2.7 (so may not work for those who are looking for a solution for python 3.x).

The suggested solution is to update libgcc: 4.8.5-2 --> 5.2.0-0

For updating libgcc, use this command

conda update libgcc

edited May 30 '17 at 21:02

answered May 30 '17 at 17:14

sanhitamj

81
3

If you follow that link you get to the answer from nehalijwani that perfectly re-produces the problem and explains why updating libgcc fixes it. To summarize: pyodbc.so requires libstdc++.so. This file exists in conda, so it uses that version, instead of the system version. Unfortunately, libmsodbc.sql requires a later version than conda installs by default. Therefore, updating the conda libgcc allows the local reference to the conda version to work. – Nadir Sidi Aug 10 '17 at 21:34

score 2 · Answer 4 · answered Oct 24 '17 at 15:15

The following suggestions may help to solve the problem:

Make sure the drive configuration INI file exists odbcinst -j (check odbcinst.ini).
Make sure the filepath to configured driver from your INI file (run: odbcinst -j) exist and has read and executable permission flags (O_RDONLY|O_CLOEXEC).
If you still got file not found error, despite the file exists, the problem could be related to libgcc version mismatch as per nehaljwani's GitHub comment. The solution is to update your libgcc by running conda update libgcc command.

^{Related: ODBC Driver 13 for SQL Server can't open lib on pyodbc while connecting on AWS E2 ubuntu instance.}
For macOS, see: Installing ODBC via HomeBrew.

score 1 · Answer 5 · answered May 16 '18 at 06:59

Maybe it is a bit late, but I leave this scripts that worked for me.

My problem was the same as yours and I tested all the options such as changing the driver location, making a symbolic link, modify /etc/*.ini files, etc... nothing worked.

My problem, running python 3.6, pyodbc package in a docker container from alpine was the library libssl1.0.0

Here you will find my installation script for pyodbc Debian 8 (alpine) docker image using the driver v13

DRIVER={ODBC Driver 13 for SQL Server}

The command I run for database connection was:

import pyodbc
connection_string = 'DRIVER={ODBC Driver 13 for SQL Server};'
connection_string += 'SERVER={0};DATABASE={1};UID={2};PWD={3};'.format(host,dbname,user,pwd)
connection = pyodbc.connect(connection_string)

score 1 · Answer 6 · answered Nov 14 '18 at 11:09

I solve this problem after installing libssl1.0.0.

First, I setup my connection string in this way:

    cnxn = pyodbc.connect('DRIVER={/usr/local/lib/libmsodbcsql.13.dylib};   
    SERVER=myServerIP,1433;DATABASE=myDBName;UID=sa;PWD=dbPassword')

Then, I installed libssl1.0.0:

    echo "deb http://security.debian.org/debian-security jessie/updates main" >> /etc/apt/sources.list
    apt-get install libssl1.0.0

Finnaly, I setup the locales:

    apt-get -y install locales 
    echo "en_US.UTF-8 UTF-8" > /etc/locale.gen

After doing these steps, my python module was able to find and connect to database.

score 0 · Answer 7 · answered Oct 20 '17 at 05:39

had the same issue once.. 1.try conda update libgcc(this is because pyodbc installed through pip and conda look for different versions of the file..)..this might have been fixed ..... link:https://github.com/ContinuumIO/anaconda-issues/issues/1639 look for nehaljwani answer .

2.also check the version number of the odbc file correctly in /etc/odbcinst.ini and /etc/odbc.ini ...names should match and also the driver path.

score -1 · Answer 8 · answered Jun 29 '22 at 16:42

Had the same problem on a Mac mini M1 with macOS Mavericks. After installing the driver 18 from Microsoft which supports ARM it still did not work.

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

However isql on the commandline was able to connect to the database.

isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=<MYSERVERNAME>;PORT=<MYPORT>;DATABASE=<MYDATABASE>;UID=<USERNAME>;PWD=<PASSWORD>"

Finally what did the trick was to uninstall pyodbc and install it again.

python -m pip uninstall pyodbc
python -m pip install pyodbc

PyODBC : can't open the driver even if it exists

8 Answers8

Linked