Connecting to SQL Server from SQLAlchemy on a Mac

This post explains how to connect to SQL Server using SQLAlchemy, pyodbc, UnixODBC and FreeTDS on a Mac

Install UnixODBC & FreeTDS

  1. Install via Homebrew
    brew install unixodbc
    brew install freetds --with-unixodbc
  2. Test with FreeTDS from terminal
    tsql -S [IP or Hostname] -U [username] -P [password]
    locale is "en_US.UTF-8"
    locale charset is "UTF-8"
    using default charset "UTF-8"

Configure odbc.ini & odbcinst.ini

  1. Open /usr/local/etc/odbcinst.ini, point to the driver at</h3>
    Driver = /usr/local/lib/
    Setup = /usr/local/lib/
    FileUsage = 1
  2. Open /usr/local/etc/odbc.ini and create new DSN
  3. Test the DSN
    isql MSSQL_DSN username password -v
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    SQL> ^D

Install pyodbc

pip install pyodbc

Connect with sqlalchemy

from sqlalchemy import create_engine

e = create_engine("mssql+pyodbc://username:[email protected]_DSN")

Hopefully you can quickly get connected to your SQL Server instance via Python with the steps above. For me, it has been notoriously more difficult to connect to than other databases like MySQL and PostgreSQL. In fact, I built an open-sourced tool to migrate an entire database (or a handful of tables) from SQL Server, to any other RDBMS like MySQL.

Check out etlalchemy here on github

A couple of notes...

  1. Depending on the SQL Server version you are connecting to, you may need to specify a TDS Protocol Version in /usr/local/etc/freetds.conf:
        # Uncomment below to guess protocol
        # tds version = auto
        tds version = 7.3

Full Compatability Matrix (from

Product TDS Version Comment
Sybase before System 10, Microsoft SQL Server 6.x 4.2 Still works with all products, subject to its limitations.
Sybase System 10 and above 5.0 Still the most current protocol used by Sybase.
Sybase System SQL Anywhere 5.0 only Originally Watcom SQL Server, a completely separate codebase. Our best information is that SQL Anywhere first supported TDS in version 5.5.03 using the OpenServer Gateway (OSG), and native TDS 5.0 support arrived with version 6.0.
Microsoft SQL Server 7.0 7.0 Includes support for the extended datatypes in SQL Server 7.0 (such as char/varchar fields of more than 255 characters), and support for Unicode.
Microsoft SQL Server 2000 7.1 Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used.
Microsoft SQL Server 2005 7.2 Includes support for varchar(max), varbinary(max), xml datatypes and MARS[a].
Microsoft SQL Server 2008 7.3 Includes support for time, date, datetime2, datetimeoffset.
Microsoft SQL Server 2012 or 2014 7.4 Includes support for session recovery.
N/A 8.0 FreeTDS will alias this version to 7.1 for backwards compatibility reasons, but this should be avoided due to future compatibility concerns. See note below on obsolete versions.