Using unixODBC on macOS to Install Microsoft SQL Server ODBC Drivers

Working with Microsoft SQL Server on macOS often requires setting up ODBC (Open Database Connectivity) drivers so you can connect tools and applications to your SQL databases. Fortunately, Microsoft provides official SQL Server ODBC drivers for macOS, and with unixODBC you can manage them efficiently.

This post walks you through the installation process using Homebrew, shows how to configure a DSN, provides a list of the most useful odbcinst commands, covers common troubleshooting steps, and demonstrates connecting with Python.

Installation Using Homebrew

First, make sure you have Homebrew installed on your Mac. Then run the following commands:

brew install unixodbc

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

brew install msodbcsql18 mssql-tools18

This installs:

• unixODBC – The ODBC driver manager used to configure and manage ODBC drivers.
• msodbcsql18 – Microsoft’s ODBC Driver 18 for SQL Server.
• mssql-tools18 – Command line tools like sqlcmd and bcp.

Important Caveat

If you uninstall the SQL Server ODBC driver, the driver registration may not be removed automatically. You need to manually clean up the ODBC configuration file.

After uninstalling, remove the driver entry with:

odbcinst -u -d -n "ODBC Driver 18 for SQL Server"

This ensures no stale configuration is left in your odbcinst.ini.

Listing ODBC Drivers

To confirm that the driver has been installed and registered properly, run:

odbcinst -j

This command shows information about your ODBC driver manager installation and where configuration files are located, including odbcinst.ini (driver registrations) and odbc.ini (DSN definitions).

Useful odbcinst Commands

Here are some of the most common and useful odbcinst commands for managing drivers and DSNs:

Creating a DSN (Data Source Name)

To simplify connections, you can configure a DSN in the odbc.ini file. This lets you reference your SQL Server connection by name instead of specifying all parameters each time.

• Find where your configuration files are located:

odbcinst -j

• Example output:

unixODBC 2.3.12
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
USER DATA SOURCES..: /Users/<username>/.odbc.ini

• Edit your user DSN file (~/.odbc.ini) or system DSN file (/opt/homebrew/etc/odbc.ini).Add an entry like this:

[MyMSSQLServer]
Driver = ODBC Driver 18 for SQL Server
Server = your-sql-server-hostname-or-ip
Port = 1433
Database = your_database_name
Encrypt = yes
TrustServerCertificate = yes

• Save the file.
• Test that the DSN is recognized:

odbcinst -q -s

• You should see:

[MyMSSQLServer]

Testing the DSN

Once the DSN is set up, you can test the connection using sqlcmd:

/opt/homebrew/bin/sqlcmd -S MyMSSQLServer -U <username> -P <password>

If configured correctly, you’ll get a SQL command prompt:

1>

From here you can run SQL queries directly against your SQL Server database.

Troubleshooting Common Issues

Even with everything installed, you may run into some common issues. Here’s how to fix them:

Driver Not Found

Error message:

[unixODBC][Driver Manager] Data source name not found, and no default driver specified

Fix:

• Ensure the driver is installed and registered:

odbcinst -q -d

• Check that the driver name in your odbc.ini matches exactly (e.g., ODBC Driver 18 for SQL Server).

SSL / Encryption Errors

Error message:

SSL Provider: [error details...]

Fix:

• If your SQL Server requires encryption but doesn’t have a valid certificate, add this to your DSN:

Encrypt = yes
TrustServerCertificate = yes

• For stricter security, configure certificates properly instead of using TrustServerCertificate.

“Login Timeout Expired” Error

Error message:

SQLState=HYT00, NativeError=0
[unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired

Fix:

• Verify that SQL Server is reachable (ping the server, check firewall rules).
• If using Azure SQL Database, ensure your client IP is added to the firewall rules.
• Explicitly set the port in odbc.ini:

Port = 1433

Multiple Driver Versions Installed

Sometimes both msodbcsql17 and msodbcsql18 are installed, leading to confusion.

Fix:

• List installed drivers:

odbcinst -q -d

• Ensure your DSN uses the correct one (ODBC Driver 18 for SQL Server).

Using Python with pyodbc

Many developers use the SQL Server ODBC driver from Python via the pyodbc library.

• Install pyodbc (use a virtual environment if possible):

pip install pyodbc

• Connect using the DSN you configured:

import pyodbc

# Using DSN
conn = pyodbc.connect("DSN=MyMSSQLServer;UID=myusername;PWD=mypassword")
cursor = conn.cursor()
cursor.execute("SELECT @@VERSION;")
row = cursor.fetchone()
print(row[0])

• Alternatively, connect with a full connection string (without DSN):

import pyodbc

conn_str = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=your-sql-server-hostname-or-ip,1433;"
    "DATABASE=your_database_name;"
    "UID=myusername;"
    "PWD=mypassword;"
    "Encrypt=yes;"
    "TrustServerCertificate=yes;"
)

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT TOP 5 name FROM sys.databases;")
for row in cursor.fetchall():
    print(row)

Conclusion

With unixODBC and Microsoft’s official ODBC drivers, connecting macOS applications to SQL Server becomes straightforward. Using odbcinst commands, you can manage drivers and DSNs, test them with sqlcmd, and integrate seamlessly into Python projects with pyodbc.

Whether you’re a developer, data engineer, or admin, this workflow gives you a solid foundation to work with SQL Server directly from macOS.