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?

score 94 · Accepted Answer · edited Jul 18 '18 at 19:41

94

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.

edited Jul 18 '18 at 19:41

fscore

2,567
7
40
74

answered Jun 14 '17 at 13:52

emehex

9,874
10
54
100

2

Good solution - to answer your question. unixODBC defaults to search for `odbc.ini` and `odbcinst.ini` in `/etc`. It will also check the user's home directory for `.odbc.ini` and `.odbcinst.ini`. (FreeTDS does the same with `freetds.conf` / `.freetds.conf`, for the record.) – FlipperPA Jun 16 '17 at 20:37
8

This works (although I had to uninstall and reinstall the driver https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server), but there's a small typo: ```sudo ln -s /usr/local/etc/odbc.ini /etc/odbc.ini``` – alexvicegrab Jan 25 '18 at 19:34
2

This error can also occur if you have updated to a new version of the driver. If the filed are present check the content of /etc/odbcinst.ini ie: $ cat /etc/odbcinst.ini [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.0.so.1.1 UsageCount=1 Then update the content of your scipt to use the new driver. In this case the driver was updated from version 13 to version 17. I suggest you add this information for completeness. – earnshae Feb 16 '18 at 16:37
35

ln: failed to create symbolic link '/etc/odbcinst.ini': File exists – Sheshank S. Feb 17 '19 at 18:41
1

file exists error means odbcinst.ini already exsits in the /etc folder. rename the file to odbcinst.ini_old and try the ln -s command again and see if that works – phill Apr 18 '20 at 20:21
It seems better to me to change the location.... https://stackoverflow.com/questions/41585274/how-to-edit-the-path-in-odbcinst-j – ntg Oct 19 '21 at 10:51
@alexvicegrab This should be its own answer imo. This worked for me although somewhat lazy that microsoft does not detect your ubuntu version in the script. – jason m Oct 25 '21 at 19:49
2

Note that for M1 macs, the above symlinks might be invalid. This is because the homebrew directory on M1s is `/opt/homebrew` instead of `/usr/local`. Thus the paths should be adjusted accordingly. – Dimitris Paraschakis Apr 06 '22 at 14:37

Rene B. · Answer 2 · 2020-07-02T07:11:23.707

58

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

edited Jul 02 '20 at 07:11

answered Oct 04 '18 at 16:18

Rene B.

6,557
7
46
72

1

Thank you very much for this quick fix (Y) – eAlie Nov 15 '18 at 14:51
3

I also had to add driver details in the connection settings `connection = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};" "Server=someHost;" "Database=dbname;" "uid=SA;pwd=passowrd")` – eAlie Nov 16 '18 at 15:40
1

This is outdated as brew does not support the --no-sandbox optin anymore (since start of 2019). The implemnented fix is to drop that option. see https://github.com/Microsoft/homebrew-mssql-release/issues/29 – A. Rabus May 02 '19 at 08:21
@A.Rabus Thanks for the comment. The post is updated with your suggestion. – Rene B. May 02 '19 at 08:34
2

We have a presentation in a few hours and my program won't work on mac. This saved our lives. – lueenavarro Jun 24 '20 at 04:29
@ReneB. when I run brew install unixodbc i get the following error: "Error: No similarly named formulae found. Error: No available formula or cask with the name "unixodbc". Could this be because I am running macOS Big Sur? – mpvalenc Mar 25 '21 at 04:23
1

Thank you @ReneB., this worked great. I found my code preferred version 17 as opposed to 13. – Ferhat Aug 26 '21 at 10:11

score 51 · Answer 3 · edited Nov 19 '19 at 21:01

51

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.

edited Nov 19 '19 at 21:01

Vladislav Povorozniuc

2,149
25
26

answered Feb 18 '18 at 07:17

Uzzal Podder

2,925
23
26

5

Works for me: `driver={ODBC Driver 17 for SQL Server};` – Ralf Hundewadt Mar 07 '19 at 14:40
work on SQL Server 2019 - driver={ODBC Driver 17 for SQL Server}; – Jiraheta May 27 '21 at 07:18

score 18 · Answer 4 · answered Aug 07 '19 at 07:00

18

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

answered Aug 07 '19 at 07:00

KevinQiang

341
2
3

3

Note that for M1 macs, the above symlinks might be invalid. This is because the homebrew directory on M1s is `/opt/homebrew` instead of `/usr/local`. Thus the paths should be adjusted accordingly. – Dimitris Paraschakis Apr 06 '22 at 14:37
Make sure to change the homebrew path for M1 macs. Thanks @DimitrisParaschakis – mathisfun Feb 16 '23 at 16:18

Evandro Pomatti · Answer 5 · 2020-04-23T10:34:19.447

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"]

I'm getting a `404 Not Found [IP: 91.189.95.85 80]` any idea about this? — MikeSchem, Sep 01 '21 at 00:53
@MikeSchem not really. Maybe post a new question with your problem and @ me or link here. — Evandro Pomatti, Sep 01 '21 at 01:25

score 5 · Answer 6 · answered Feb 13 '20 at 10:46

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.

score 2 · Answer 7 · answered Oct 29 '18 at 07:34

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, } }

Tomasz Bartkowiak · Answer 8 · 2019-10-24T07:52:07.693

2

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.

edited Oct 24 '19 at 07:52

answered Oct 23 '19 at 17:42

Tomasz Bartkowiak

12,154
4
57
62

1

that "Microsoft ODBC Driver for SQL Server for linux/macos" link was helpful, thanks – Loknar Jan 21 '20 at 11:03

score 1 · Answer 9 · answered Dec 15 '21 at 08:34

I was struggling with a similar issue.

Firstly, I was following this instruction:

I thought the error was in that I used driver 17 and not driver 13 - as per the connection string visible in the Azure Portal.

After playing around with it for a long time, getting this error for driver 13: " pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libmsodbcsql.13.dylib' : file not found (0) (SQLDriverConnect)") "

and getting a timeout error for driver 17.

I finally found the solution in this hack. The problem was OpenSSL where two versions confused up.

I'm running the app in a vertual environment in VS Code on my Mac and deploying it to Azure Web App.

mjeday · Answer 10 · 2022-01-31T17:39:41.420

I simply built image top of python:3.7-alpine

FROM python:3.7-alpine

COPY . /app/.
WORKDIR /app

# Install curl
RUN apk add --no-cache curl
RUN apk update && apk add curl

# Install the Microsoft ODBC driver Linux.Follow the mssql documentation: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15
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

# 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

# Install other libs
RUN pip install --upgrade pip
RUN pip install -r requirements.txt

RUN apk add python3 python3-dev g++ unixodbc-dev
RUN python3 -m ensurepip
RUN pip3 install --user pyodbc

# Run script
ENTRYPOINT [ "python", "-u", "run.py"]

When setting connection inside the code, you need to specify the driver version that you've installed. For instance in my case:msdobcversion=17 so driver should be DRIVER={ODBC Driver 17 for SQL Server}

# Make connection
server = os.environ["server"]
database = os.environ["database"]
username = os.environ["UID"]
password = os.environ["PWD"]
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

score 0 · Answer 11 · answered Oct 29 '19 at 07:06

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.

score 0 · Answer 12 · answered Aug 04 '21 at 06:08

I know everyone had the same stupid error ones: but I remind, I spent 30 mins just reading this branch... error was odd space at the end of connection string

     SQLALCHEMY_DATABASE_URI: str = "mssql+pyodbc://sa:tt@localhost:1433/babylon_pacemaker?Trusted_connection = no&driver=ODBC+Driver+17+for+SQL+Server " - odd space :(

Funny that Alembic works well, but SQLAlchimy has an error.

score 0 · Answer 13 · answered Jun 22 '22 at 02:44

Thank you Uzzal your solution solved my problem:

Blockquote 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.

https://stackoverflow.com/a/48849351/7694643

It's work for me. I've added it as env. variable:

score 0 · Answer 14 · answered Sep 02 '22 at 13:14

In my case after running below commands with sudo user, i used DRIVER={ODBC Driver 17 for SQL Server}, which resolved my problem.

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

score 0 · Answer 15 · answered Oct 26 '22 at 19:45

0

Users on Arch Linux can install specific packages from the AUR

1. aur/msodbcsql17 17.10.1.1-1 (+3 0.66) 
Microsoft® ODBC Driver 17 for SQL Server®

2. aur/msodbcsql 18.0.1.1-1 (+24 0.38)
Microsoft® ODBC Driver 18 for SQL Server®

answered Oct 26 '22 at 19:45

Jithin Johnson

352
1
10

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

15 Answers15

Linked

Related