5

I am trying to connect to an MSSQL server from a Jupyter notebook using pyodbc and unixODBC, under macOS 10.15.4. I have just upgraded OSX to Catalina and my previously working configuration has broken.

I try to connect with:

pyodbc.connect('Driver={ODBC Driver 13 for SQL Server};Server=xxx;Database=xxx;uid=xxx;pwd=xxx;')

which throws an error:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libmsodbcsql.13.dylib' : file not found (0) (SQLDriverConnect)")

But this file exists, and is pointed to by the driver in obdcinst.ini, and links seem correct.

To track this through, I run:

$ odbcinst -j

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

The driver file exists, and is as follows:

$ more /etc/odbcinst.ini

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

The indicated Driver is a symlink - this is the 'file not found' file in the error:

$ ls -al /usr/local/lib/libmsodbcsql.13.dylib

lrwxr-xr-x  1 johnmorgan  admin  54 Apr 15 08:13 /usr/local/lib/libmsodbcsql.13.dylib -> ../Cellar/msodbcsql/13.1.9.2/lib/libmsodbcsql.13.dylib

and the symlink points to the actual library file:

$ ls -al /usr/local/Cellar/msodbcsql/13.1.9.2/lib/libmsodbcsql.13.dylib

-r--r--r--  1 johnmorgan  admin  2456360 Jan 29  2018 /usr/local/Cellar/msodbcsql/13.1.9.2/lib/libmsodbcsql.13.dylib

So as far as I can tell, all the drivers, files and links are all correct.

So why do I get a 'file not found' error, and how can I fix this?

I am aware of others with this problem, eg Can't open lib 'ODBC Driver 13 for SQL Server'? Sym linking issue?, but this has not helped me resolve my issue.

JohnM
  • 91
  • 1
  • 6
  • Does this solve your issue ? https://unix.stackexchange.com/a/370373 – michaeldel Apr 15 '20 at 07:32
  • Thank you for the link. Unfortunately it has not helped. The error message generated by sqlcmd is different to that issue - I run sqlcmd and get `$ sqlcmd -S 10.8.0.22` `Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: The operation or option is not available.` `Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.` The other suggestions there involve repairing links, but brew finds nothing to repair, and my links all seem to be correct, as indicated in my question. – JohnM Apr 15 '20 at 23:27
  • Hello JohnM: Did you find a solution? I ran in the same issue and do not have a clue how to solve it... – Pet Aug 27 '20 at 16:03
  • I had a similar issue today. I had msodbcsql13 and 17 installed side by side for a long while and it was all working fine with my connection string pointing to 13. Not sure about the actual cause but it stopped working with this error and the way I fixed it was to point my connection string to use Driver 17 instead of 13 and it worked magically. – Jithu Oct 28 '20 at 17:21
  • I did eventually resolve this issue, although I cannot reconstruct all the steps I took. I can say it appears to have been a problem linking the drivers. My notes record the final steps, but there was more involved than I recorded. I did move to Driver 17, and final steps were: $ HOMEBREWNOENVFILTERING=1 ACCEPTEULA=Y brew install msodbcsql17 mssql-tools Warning: microsoft/mssql-release/mssql-tools 17.5.2.1 is already installed, it's just not linked You can use `brew link mssql-tools` to link this version. $ brew link mssql-tools – JohnM Oct 28 '20 at 21:49

1 Answers1

2

I had the same issue. I tried to locate the dylab file and went to /usr/local/lib/ and did not find the file. So I searched libmsodbcsql.13.dylib in Finder and luckily enough, found it at:

/opt/homebrew/Cellar/msodbcsql/13.1.9.2/lib/

My next action was to change the directory in the odbcinst.ini file which I found at

/opt/homebrew/etc/odbcinst.ini

Even after updating the correct directory in the .ini file, my project still shows file not found.

Next thing I did is I installed msodbcsql 17:

HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y
brew install msodbcsql17 mssql-tools

Strange enough, it worked after installing msodbcsql 17. I checked the .ini file, it was updated including the new msodbcsql 17 along with msodbcsql 13 intact inside.

Logan Wayne
  • 6,001
  • 16
  • 31
  • 49