python - Can't open lib 'ODBC Driver 13 for SQL Server'? Sym linking issue?

Question

When I try to connect to a sql server database with pyodbc (on mac):

import pyodbc

server = '####'
database = '####'
username = '####@####'
password = '#####'
driver='{ODBC Driver 13 for SQL Server}'

pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+';PWD='+password)

I get the following error:

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

When I path in the actual driver location:

driver='/usr/local/lib/libmsodbcsql.13.dylib'

It starts working!

My odbcinst.ini looks like this:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=1

How can I get my reference to driver='{ODBC Driver 13 for SQL Server}' to start working again?

I initially used this guide to install the driver. And I'm using anaconda on Mac Sierra if that helps?

Answer 1

Running:

odbcinst -j

It yielded:

unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/emehex/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Instead of copying the files to the /etc/ directory (not sure why unixODBC thought they were there) I created a symbolic link to each file:

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

This solved the problem.

Answer 2

In my case, I have a Mac OS and the following commands fixed the problem:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql mssql-tools

Note 1: It might be necessary that you need to install unixodbc in advance to msodbcsql and mssql-tools as the following:

brew install unixodbc

Note 2: If you dont have already brew, the Missing Package Manager for macOS, then you can install it from here: https://brew.sh/

Note 3: You can verifiy your installation as @emehex already mentioned above with the following commands:

odbcinst -j

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

Answer 3

In my case, I had to change the pyodbc database driver string to
driver='{ODBC Driver 17 for SQL Server}'

actually, in my python code pyodbc was expecting ODBC Driver 13 but, as the ODBC Driver version was updated (because of ubuntu update) to current version ODBC Driver 17, the problem had occurred.

Answer 4

In my case, I fixed the problem with three steps as follow:

# Step1: install unixodbc 
brew install unixodbc

# Step2: install Microsoft ODBC Driver for SQL Server on MacOS

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql mssql-tools

# Step3：verify odbcinst configuration path is correct

odbcinst -j

sudo ln -s /usr/local/etc/odbcinst.ini /etc/odbcinst.ini
sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini

Answer 5

I was building a custom image on top of Python and this is the configuration that made it work:

FROM python:3.8 as pyodbc

COPY . /app
WORKDIR /app

# Required for msodbcsql17 and mssql-tools
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update

# Key might change in the future, replace with new one on the logs
RUN apt-key adv --keyserver keyserver.ubuntu.com --recv-keys BA6932366A755776
RUN apt-get update
RUN apt-get install -y software-properties-common
RUN add-apt-repository ppa:deadsnakes/ppa
RUN apt-get update

RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools
RUN apt-get install unixodbc-dev

RUN pip install pipenv
RUN pipenv install --system --deploy

CMD [ "python" , "__init__.py"]

Answer 6

I've tried to establish a connection to a remote MS SQL Server deployed on a (Windows) machine from an external (Linux) machine. It took me some time to realise you need to first install the drivers on the machine that tries establish connection (i.e. Linux in my case)!

If you're using macOS/Linux what you need to do is to simply Install the Microsoft ODBC Driver for SQL Server on Linux and macOS and then follow instructions on Connecting to databases for your particular OS.

Answer 7

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > 
/etc/apt/sources.list.d/mssql-release.list 
apt-get update
ACCEPT_EULA=Y apt-get install msodbcsql17
apt-get -y install unixodbc-dev
sudo apt-get install python3-pip -y
pip3 install --upgrade pyodbc

Use above steps to istall the odbc driver correctly and everything will fall in place.

Answer 8

I have to add that if you are using a different driver (FreeTDS) and in your connection string you omit to mention it, it will default to driver='{ODBC Driver 17 for SQL Server} or something like like that.

So the solution is not to forget driver, you DB settings will look like this: 'default': { 'ENGINE': 'sql_server.pyodbc', 'HOST': '127.0.0.1', 'NAME': 'mydb', 'PORT': '1433', 'USER': 'sa', 'PASSWORD': '*****', 'OPTIONS':{ 'driver': 'FreeTDS', 'host_is_server': True, } }

Answer 9

Installation that worked on the Ubuntu 18.04. I'm not sure if two of the ./bash_profile and ./bashrc exports are needed but I didn't have time to check.

sudo apt-get update
ACCEPT_EULA=Y sudo apt-get -y install msodbcsql17 mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile \
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc \
sudo apt-get -y install unixodbc libc6 libstdc++6 libkrb5-3 libcurl3 openssl debconf unixodbc unixodbc-dev

Then as a driver in connection use ODBC Driver 17 for SQL Server which is matching the current Azure version.