Configure OS X (Yosemite) to connect to Azure SQL database

Scenario

Lately I've been thinking about the possibility to use SQL Server to develop a Flask web application using my faithful hac*intosh. Surely enough that's not a common stack and there are probably better alternatives (Postgre, MySQL, etc) that go along well with Flask and Python. Nonetheless, I wanted to give it a try, also because, sometimes, customers have very peculiar needs (or should I say they've already payed and own some expensive licenses?! ;-) ).
Anyway, enough talking! Let's walk through what's needed to get Flask up and running on OS X Yosemite with a MSSQL instance on Azure.

Prerequisites

I'll assume you have already installed and properly configured Homebrew and Python 3.4.
For those of you who don't know what I'm talking about, check this link for installing brew and that one for setting up properly a python development environment on Mac OS X.

Requisites

First of all download iODBC, because the iODBC headers were dropped since Mavericks (see here) so the default FreeTDS build that assumes iODBC will find sql.h and other dependencies and the libtdsodbc.so library can be built.
Install freetds:

brew install freetds  

then also install unixodbc:

brew install unixodbc  


Configuration


Configure FreeTDS

Running tsql -C should output something similar to:

Compile-time settings (established with the "configure" script) Version: freetds v0.91 freetds.conf directory: /usr/local/Cellar/freetds/0.91_2/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 7.1 iODBC: no unixodbc: yes SSPI "trusted" logins: no Kerberos: no  

take note of the freetds.conf path and edit it with your preferred text editor. I use vim:

vim /usr/local/Cellar/freetds/0.91_2/etc/freetds.conf

[global] tds version = 7.1
[SERVERNAME] host = HOST.database.windows.net
port = 1433  
database = YOURDATABASENAME  
dump file = /tmp/freetds.log  
client charset = UTF-8  
Test connection
$ tsql -S SERVERNAME -U USERNAME@HOST -P PASSWORD 

Note that @HOST is required. Otherwise the connection ends with an error:

Msg 40531 (severity 11, state 1) from [SERVERNAME] Line 1: "Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match." Error 20002 (severity 9): Adaptive Server connection failed There was a problem connecting to the server  
Configure ODBC driver

Execute odbcinst -j to gather info about ODBC driver configuration:

unixODBC 2.3.2 DRIVERS............: /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini FILE DATA SOURCES..: /usr/local/Cellar/unixodbc/2.3.2_1/etc/ODBCDataSources USER DATA SOURCES..: /Users/stondo/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8  

File /usr/local/Cellar/unixodbc/2.3.21/etc/odbcinst.ini

[FreeTDS] Description = FreeTDS Driver Driver=/usr/local/lib/libtdsodbc.so
Configure ODBC data sources

File /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini

[DATASOURCENAME]
Driver = FreeTDS  
Servername = SERVERNAME  
Port = 1433  
Database = DBNAME  

SERVERNAME is the same as in freetds.conf.

Connect with isql
$ isql -v DATASOURCENAME USER@HOST PASSWORD
+---------------------------------------+ | Connected! | | | | 
sql-statement | | help [tablename] | | quit | | |  
+---------------------------------------+ 
SQL> select current_timestamp  
+------------------------+ | | +------------------------+ |
2015-01-02 09:05:55.593 | +------------------------+  
SQLRowCount returns 1 1 rows fetched SQL>  

Note that @HOST is required. Otherwise the connection ends with an error:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source [37000][unixODBC][FreeTDS][SQL Server]Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net).

Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match, otherwise you'll get:

[ISQL]ERROR: Could not SQLConnect
Conclusion

It's certainly not straightforward to connect to a SQL DB on Azure from OSX, but it can be done!