9

I am trying to get ODBC Driver 17 for SQL Server to work but somehow it doesn't on my brand new apple M1. Apparently I got unixodbc installed and also the driver, but starting them doesn't work. Looks like the odbc driver is not yet ready for the architecture (see below output). Doesn't even look like an issue with the microsoft driver, but the general lib from unixodbc - am I correct?

Any ideas if it's possible to compile this on my own?

XXX@M1 ~ % odbcinst -j
unixODBC 2.3.9
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/XXX/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
XXX@M1 ~ % sqlcmd
dyld: Library not loaded: /usr/local/lib/libodbc.2.dylib
  Referenced from: /opt/homebrew/bin/sqlcmd
  Reason: no suitable image found.  Did find:
    /usr/local/lib/libodbc.2.dylib: mach-o, but wrong architecture
    /usr/local/lib/libodbc.2.dylib: mach-o, but wrong architecture
    /opt/homebrew/Cellar/unixodbc/2.3.9/lib/libodbc.2.dylib: mach-o, but wrong architecture
    /opt/homebrew/Cellar/unixodbc/2.3.9/lib/libodbc.2.dylib: mach-o, but wrong architecture
nico525
  • 137
  • 1
  • 8

2 Answers2

0

I'm not familiar with odbcinst, but in general when running things from the terminal on Apple Silicon (M1), if the libraries are not universal, you have two choices:

  1. set Terminal to run on Rosetta2, or
  2. prefix your command with arch -x86_64

I would suggest you try

arch -x86_64 odbcinst -j

Read more about this here. (Yes, the link speaks about Flutter, but the solutions are the same.)

pfurbacher
  • 1,789
  • 3
  • 15
  • 23
  • Unfortunately that didn't work out. I think unixODBC is correctly installed for arm64, but the Microsoft ODBC Driver isn't. FreeTDS works, so I'm just waiting a little bit on microsoft to fix that (hopefully that is the underlying issue...) – nico525 Feb 06 '21 at 13:34
  • But what if you install the x86_64 version of unixODBC, use the MS ODBC driver, and run under Rosetta2 while waiting for MS to update the driver? Rosetta2 is pretty fast, maybe even faster than execution speed you had on your older Intel-based Mac. (Just a thought.) – pfurbacher Feb 07 '21 at 18:29
  • 1
    I get this error if I run option 2. ´arch: posix_spawnp: odbcinst: Bad CPU type in executable´ – Giacomo Mar 15 '21 at 20:43
  • I also tried a copy of iTerm with Rosetta and reinstalled homebrew but when I run odbcinst -j I still see: DRIVERS............: /opt/homebrew/etc/odbcinst.ini. I'm completely lost. Any help much appreciated. – Giacomo Mar 15 '21 at 21:26
  • As per the GitHub page of the Microsoft odbc driver for Mac they plan M1 support for the next release. Let’s stay tuned – nico525 Mar 25 '21 at 20:15
0

in any case of using Apple silicon chips you may encounter issues running ODBC drivers; I've managed to make it run on my M1 Max - Follow these steps (location paths may vary...):

  1. Install Homebrew (if not already installed): Open Terminal and execute the following command: /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

  2. Install ODBC Driver Manager via Homebrew: Run the following command in Terminal:

    brew install unixodbc

  3. Install Microsoft ODBC Driver for SQL Server via Homebrew: Execute the following command in Terminal:

    brew tap microsoft/mssql-release GitHub - microsoft/homebrew-mssql-release brew update brew install msodbcsql17 mssql-tools

  4. Set environment variables: Open your shell configuration file (e.g., ~/.bash_profile, ~/.zshrc, etc.) in a text editor. Add the following lines to set the environment variables:

    export PATH="$PATH:/usr/local/opt/mssql-tools/bin"

    export LDFLAGS="-L/usr/local/opt/unixodbc/lib"

    export CPPFLAGS="-I/usr/local/opt/unixodbc/include"

Save the file and close it. Run source <path_to_file> to apply the changes to your current shell session. (the paths may be different for you, so verify that they are correct prior to this step).

  1. Reinstall 'pyodbc': Activate your virtual environment (if using one).

Run: pip install --no-binary :all: pyodbc to reinstall the 'pyodbc' package.

After following these steps, 'pyodbc' should work correctly with the updated ODBC driver manager and SQL Server driver. You can then import and use pyodbc in your Python code without encountering the symbol not found error.

If you're encountering the error message Can't open lib 'ODBC Driver 17 for SQL_Server' : file not found (0) on macOS with Apple Silicon, it could be due to the architecture mismatch between the ODBC driver and the Python interpreter. Check the architecture of your Python interpreter: Open Terminal and execute the following command:

python3 -c "import platform; print(platform.architecture())"

Make sure the output shows arm64 for the architecture. If it shows x86_64, you might need to use a Python interpreter built for Apple Silicon.

  1. Obtain and install the Apple Silicon-compatible ODBC driver: Visit the Microsoft ODBC Driver for SQL Server download page: Download ODBC Driver for SQL Server - ODBC Driver for SQL Server Select the appropriate driver version for macOS with Apple Silicon (ARM64). Follow the installation instructions provided by Microsoft to install the driver.

  2. Verify the driver installation: Run the following command in Terminal: odbcinst -j

Check the value of the ODBCINSTINI environment variable listed in the output. Ensure that the directory mentioned in the ODBCINSTINI variable contains an entry for the installed ODBC driver.

  1. Retry connecting to the SQL Server using 'pyodbc': Update your connection string in your Python code to use the correct driver name. For example:

Using Pyodbc:

 `conn_str = 'DRIVER=<path to ODBC Driver 17 for SQL Server>;SERVER=<server_name>;DATABASE=<database_name>;UID=<username>;PWD=<password>`

connection = pyodbc.connect(conn_str)

Or if your’e using Sqlalchemy:

`conn_str = mssql+pyodbc://<Username>:<Pass>@<Address>:<Port>/<DB_name>?&driver=<Path to ODBC Driver 17 for SQL Server>`

engine = create_engine(url=conn_str, module=pyodbc)

  1. make sure to edit both these files using text editor (your location may vary):

/etc/odbcinst.ini

/etc/odbc.ini

insert these lines:

[ODBC Driver 17 for SQL Server]

Description=Microsoft ODBC Driver 17 for SQL Server

Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib

UsageCount=5 <<(this line is optional)

By following these steps, you should be able to resolve the issue and establish a successful connection to the SQL Server using pyodbc on macOS with Apple Silicon.

Amit Nakash
  • 1