ODBC Sql Server 17 driver on Mac OS X

Question

In order to execute this python code [that works on Ubuntu 18] on a MAC,

import pyodbc as odbc

sql_conn_irs = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=irs;UID=xxxxx;PWD=xxxxxxxx;')

I installed SQL Server Driver like this:

mm2:~ ivan$ HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools
==> Installing msodbcsql17 from microsoft/mssql-release
==> Installing dependencies for microsoft/mssql-release/msodbcsql17: unixodbc
==> Installing microsoft/mssql-release/msodbcsql17 dependency: unixodbc
==> Downloading https://homebrew.bintray.com/bottles/unixodbc-2.3.7.mojave.bottle.1.tar.gz
==> Downloading from https://akamai.bintray.com/d9/d9f30688c0639d73b9e3a4b4b94d3679b2762e2cf0bff6b2ad64fcd175cc30ca?__gda__=exp=1581885422~hmac=2d3
######################################################################## 100.0%
==> Pouring unixodbc-2.3.7.mojave.bottle.1.tar.gz
🍺  /usr/local/Cellar/unixodbc/2.3.7: 46 files, 1.8MB
==> Installing microsoft/mssql-release/msodbcsql17
==> Downloading https://download.microsoft.com/download/1/9/A/19AF548A-6DD3-4B48-88DC-724E9ABCEB9A/msodbcsql-17.5.1.1.tar.gz
######################################################################## 100.0%
==> odbcinst -u -d -n "ODBC Driver 17 for SQL Server"
==> odbcinst -i -d -f ./odbcinst.ini
==> Caveats
If you installed this formula with the registration option (default), you'll
need to manually remove [ODBC Driver 17 for SQL Server] section from
odbcinst.ini after the formula is uninstalled. This can be done by executing
the following command:
    odbcinst -u -d -n "ODBC Driver 17 for SQL Server"
==> Summary
🍺  /usr/local/Cellar/msodbcsql17/17.5.1.1: 9 files, 2MB, built in 37 seconds
==> Installing mssql-tools from microsoft/mssql-release
==> Downloading https://download.microsoft.com/download/F/D/1/FD16AA69-F27D-440E-A15A-6C521A1972E6/mssql-tools-17.5.1.2.tar.gz
######################################################################## 100.0%
🍺  /usr/local/Cellar/mssql-tools/17.5.1.2: 11 files, 869KB, built in 7 seconds
==> Caveats
==> msodbcsql17
If you installed this formula with the registration option (default), you'll
need to manually remove [ODBC Driver 17 for SQL Server] section from
odbcinst.ini after the formula is uninstalled. This can be done by executing
the following command:
    odbcinst -u -d -n "ODBC Driver 17 for SQL Server"

I then executed this to see what it returns:

mm2:~ ivan$ 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/ivan/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Yet when I cat that file,

mm2:~ ivan$ more /etc/odbcinst.ini
/etc/odbcinst.ini: No such file or directory
mm2:~ ivan$

When I run the python code, I get:

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-8-d2b1c4f70099> in <module>()

---> 11 sql_conn_irs = odbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=irs;UID=xxxx;PWD=xxxxx;')
     12 query = "SELECT * FROM Table_Curve order by Date asc"
     13 dfirs = pd.read_sql(query, sql_conn_irs)

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

Is there extra magic in installing SQL Server drivers on a Mac OS X?

Answer 1

Great resource from the source: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15

"If you installed the v17 msodbcsql package that was briefly available, you should remove it before installing the msodbcsql17 package. This will avoid conflicts. The msodbcsql17 package can be installed side by side with the msodbcsql v13 package."

Install brew and use it to install the drivers you need:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"


brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

brew update

brew install msodbcsql@13.1.9.2 mssql-tools@14.0.6.0

Answer 2

I fixed it with the uninstall e install on mac OS catalina

It was the same error, then what I did, first I was install Brew following this instructions:

https://learn.microsoft.com/pt-br/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

brew install msodbcsql17 mssql-tools

whatout the terminal messages, some itens was deprecated, I used the instructions from terminal and from microsoft site, I use just the lines above.

The installation 1, does't worked, then I was uninstall the package and install again

brew uninstall msodbcsql17 mssql-tools 

brew install msodbcsql17 mssql-tools 

In the fisrt install The terminal no show all Licenses message alerts, the second install the terminal shows two licenses alert:

erik@ERIKs-MacBook-Pro-3 ~ % brew install msodbcsql17 mssql-tools
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 1 tap (homebrew/core).
==> Updated Formulae
broot                                    healpix

==> Installing msodbcsql17 from microsoft/mssql-release
==> Downloading https://download.microsoft.com/download/1/9/A/19AF548A-6DD3-4B48
Already downloaded: /Users/erik/Library/Caches/Homebrew/downloads/b8402326ee28ebb9b25b5c98a4fcfe0ecad17cd385a8ccb3ef5bedb0e097c2e0--msodbcsql-17.5.2.1.tar.gz
The license terms for this product can be downloaded from
https://aka.ms/odbc17eula and found in
/usr/local/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
==> odbcinst -u -d -n "ODBC Driver 17 for SQL Server"
==> odbcinst -i -d -f ./odbcinst.ini
==> Caveats
If you installed this formula with the registration option (default), you'll
need to manually remove [ODBC Driver 17 for SQL Server] section from
odbcinst.ini after the formula is uninstalled. This can be done by executing
the following command:
    odbcinst -u -d -n "ODBC Driver 17 for SQL Server"
==> Summary
🍺  /usr/local/Cellar/msodbcsql17/17.5.2.1: 9 files, 2MB, built in 21 seconds
==> Installing mssql-tools from microsoft/mssql-release
==> Downloading https://download.microsoft.com/download/F/D/1/FD16AA69-F27D-440E
Already downloaded: /Users/erik /Library/Caches/Homebrew/downloads/ff8148cc433f9b643b44c5bbe8adec5a50a170948e0f3ad64069efe2773670c3--mssql-tools-17.5.2.1.tar.gz
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/local/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
🍺  /usr/local/Cellar/mssql-tools/17.5.2.1: 11 files, 869KB, built in 55 seconds
==> Caveats
==> msodbcsql17
If you installed this formula with the registration option (default), you'll
need to manually remove [ODBC Driver 17 for SQL Server] section from
odbcinst.ini after the formula is uninstalled. This can be done by executing
the following command:
    odbcinst -u -d -n "ODBC Driver 17 for SQL Server"

I'm not following this warnings that was showed in this scrip, I go to step for test the connection

erik@ERIKs-MacBook-Pro-3 ~ % sqlcmd -S 192.168.2.103 -U sa -P xxxxx -Q "SELECT @@VERSION" 
                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) 
	Aug 22 2017 17:04:49 
	Copyright (C) 2017 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) (Hypervisor)
                                                                                             

(1 rows affected)