MSodbcSQL18 is not supported on Macos 14

[cagrimunyas]

Following the installation guide on https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

MacOs Version : macOS Sonoma 14.1
Python version : 3.11.4
ODBC Driver : Microsoft ODBC 18
pyodbc version : 5.0.1
-->Installed pyodbc using pip install --no-binary :all: --no-cache-dir --force-reinstall pyodbc

Even though the note on the page says it now supports Mac silicon natively, upon installation there is always the following error:
('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/opt/homebrew/lib/libmsodbcsql.18.dylib' : file not found (0) (SQLDriverConnect)")

otool -L "/opt/homebrew/lib/libmsodbcsql.18.dylib" returns :

/opt/homebrew/lib/libmsodbcsql.18.dylib:
/opt/homebrew/opt/msodbcsql18/lib/libmsodbcsql.18.dylib (compatibility version 0.0.1, current version 3.2.1)
/System/Library/Frameworks/CoreFoundation.framework/Versions/A/CoreFoundation (compatibility version 150.0.0, current version 1770.255.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1292.60.1)
/opt/homebrew/lib/libodbcinst.2.dylib (compatibility version 3.0.0, current version 3.0.0)
/System/Library/Frameworks/Kerberos.framework/Versions/A/Kerberos (compatibility version 5.0.0, current version 6.0.0)
/usr/lib/libiconv.2.dylib (compatibility version 7.0.0, current version 7.0.0)
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 904.4.0)

Also tried https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16#troubleshooting as I have the exact error. But the recommended solution seems to be a workaround rather than a fix as it didn't work for me.

[cagrimunyas]

Tried running odbcinst -j

unixODBC 2.3.9
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /Users/{USERNAME}/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

*{USERNAME} is my local username

Als ran the following python code :

import pyodbc 
print(pyodbc.drivers())

The output :
['ODBC Driver 18 for SQL Server']

[cagrimunyas]

Similar issues :

• pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'driver' : file not found (0) (SQLDriverConnect)") mssql-docker#448
• ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") mkleehammer/pyodbc#717

[v-chojas]

Can you try running isql -v -k '<your connection string here>' and see if it gives the same error?

[yoelf22]

What I have observed is the following:

import pyodbc
ImportError: dlopen(/opt/homebrew/lib/python3.11/site-packages/pyodbc.cpython-311-darwin.so, 0x0002): symbol not found in flat namespace '_SQLAllocHandle'

[yoelf22]

Can you try running isql -v -k '<your connection string here>' and see if it gives the same error?

isql -v -k 'DRIVER={ODBC Driver 18 for SQL Server};SERVER=MYSERVER;PORT=1443;DATABASE=MY_DATABASE;UID=USER;PWD=PASSWOR’D

[S1T00][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
[08001][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2AF9
[08001][Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to MYSERVER. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[01S00][Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute
[ISQL]ERROR: Could not SQLDriverConnect

[v-makouz]

port is not a valid connection string attribute, port is specified with a comma after server name or IP, i.e. SERVER=MYSERVER,1443. Although TCP Provider: Error code 0x2AF9 indicates that the server itself can't be accessed, so it may be a wrong name or firewall.

[cagrimunyas]

Can you try running isql -v -k '<your connection string here>' and see if it gives the same error?

Sorry for the late reply @v-chojas here's the result of the query :

[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLDriverConnect

Connection string format that I can use in windows with ODBC Driver :

"mssql+pyodbc://{USERNAME}:{PASSWORD}@{URL},{PORT}/{DBNAME}?driver=ODBC Driver 18 for SQL Server"

[yoelf22]

Thanks for this. I gave up and resorted to metabase instead. Importantly, and maybe this could help others,I created a docker network, mounted the two containers (metabase and mssql) on it, and used the mssql container as the host name in the connection string (a little different and much easier in netabase)Regards,------------------------YoelOn 27 Dec 2023, at 8:14, Çağrı Münyas ***@***.***> wrote: Can you try running isql -v -k '<your connection string here>' and see if it gives the same error? Sorry for the late reply @v-chojas here's the result of the query : [IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified [ISQL]ERROR: Could not SQLDriverConnect Connection string format that I can use in windows with ODBC Driver : "mssql+pyodbc://{USERNAME}:{PASSWORD}@{URL},{PORT}/{DBNAME}?driver=ODBC Driver 18 for SQL Server" —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: ***@***.***>

[cagrimunyas]

Hi @v-chojas
I saw this issue recently mkleehammer/pyodbc#1116 . After following the comments in the issue, I was able to connect to my SQL db on Azure using isql -v -k command. However, the issue on pydobc still exists while I'm trying to use it within my python project.

[cagrimunyas]

Tried deleting my old python environment recreated new one with python 3.12.1
Instead of installing pyodbc directly from my projects requirements file. Installed it via
pip3 install --no-binary :all: pyodbc and it finally resolved on my device macOS Sonoma running on M1 Max.

[cagrimunyas]

AS of today pyodbc supports Mac ARM directly https://github.com/mkleehammer/pyodbc/releases/tag/5.1.0