title: Error connecting to MS SQL Server using pyODBC, unixODBC and FreeTDS (on a Mac)
body: <p>I am getting an error when trying to connect to a MS SQL server in python, using the pyODBC --> unixODBC --> FreeTDS --> MS SQL stack.  I’ve spent a lot of time on this, and there are some great resources if you’ve reached this question with more fundamental problems getting a connection to work <a href="http://www.cerebralmastication.com/2013/01/installing-debugging-odbc-on-mac-os-x/" rel="nofollow">here</a> and <a href="https://gist.github.com/Bouke/10454272" rel="nofollow">here</a>.  </p>
  
  <p>However, my question is regarding an error that (I think) is very close to the finish line of this very frustrating experience.  Specifically, this code in jupyter notebook: </p>
  
  <pre><code>pyodbc.connect(
      'DRIVER=/usr/local/lib/libtdsodbc.so;'
      'SERVER=MyServerIP;'
      'PORT=1433;'
      'DATABASE= DatabaseName;'
      'UID=MyUsername;'
      'PWD=MyPassword')
  </code></pre>
  
  <p>Is giving me this error: </p>
  
  <pre><code>---------------------------------------------------------------------------
  Error                                     Traceback (most recent call last)
  &lt;ipython-input-7-d6b29b647116&gt; in &lt;module&gt;()
        1 pyodbc.connect(
  ----&gt; 2     'DRIVER = /usr/local/lib/libtdsodbc.so;'
        3     'SERVER = MyServerIP;'
        4     'PORT = 1433;'
        5     'DATABASE = DatabaseName'
  
  Error: ('HY000', '[]  (20013) (SQLDriverConnect)’)
  </code></pre>
  
  <p>And if I replace 'DRIVER=/usr/local/lib/libtdsodbc.so;' with 'DRIVER=FreeTDS;' I get: </p>
  
  <pre><code>---------------------------------------------------------------------------
  Error                                     Traceback (most recent call last)
  &lt;ipython-input-12-607f0d66e615&gt; in &lt;module&gt;()
        1 pyodbc.connect(
  ----&gt; 2     'DRIVER=FreeTDS;'
        3     'SERVER= MyServerIP;'
        4     'PORT=1433;'
        5     'DATABASE= DatabaseName;'
  
  Error: ('00000', '[00000] [iODBC][Driver Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnect)')
  </code></pre>
  
  <p>Which leads me to believe there is something wrong with the unixODBC --> FreeTDS connection because of the reference to iODBC.  Said differently, unless I specifically provide a path to the FreeTDS driver, it seems to ignore my odbcinst.ini and odbc.ini files, which reference FreeTDS and its location as my driver (see below)</p>
  
  <p>When running tsql and isql from the terminal, both yield good connections with the server.  </p>
  
  <p>However, when I run the osql, I get the following error:</p>
  
  <pre><code>$ osql -S MyServerIP -U MyUsername -P MyPassword
  checking shared odbc libraries linked to isql for default directories...
  /usr/local/bin/osql: line 53: ldd: command not found
  error: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/strings: can't open file:  (No such file or directory)
  osql: problem: no potential directory strings in "/usr/local/bin/isql"
  osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
  isql strings are:
  checking odbc.ini files
      reading /Users/myname/.odbc.ini
  [MyServerIP] not found in /Users/myname/.odbc.ini
      cannot read "/odbc.ini"
  osql: error: unable to locate MyServerIP in any odbc.ini
  </code></pre>
  
  <p><strong>Background on my setup</strong></p>
  
  <p>My connection was built (and rebuilt and rebuilt) using the two resources linked in the first paragraph and my complete set up looks like this:</p>
  
  <p><strong>Environment</strong></p>
  
  <p>Mac OSX 10.11.5</p>
  
  <p>Microsoft SQL Server 2012 – AWS EC2 instance (cloud)</p>
  
  <p>Anaconda 4.0</p>
  
  <p>Python 3.5.1</p>
  
  <p>Jupyter Notebook 4.1.0</p>
  
  <p><strong>Connection Stack</strong></p>
  
  <p>unixODBC – installed using homebrew</p>
  
  <p>FreeTDS – installed using homebrew with the command: `$ brew install </p>
  
  <p>freetds --with-unixodbc`</p>
  
  <p>pyODBC 3.0.10 – installed using conda install</p>
  
  <p>MS SQL – AWS EC2 instance (cloud)</p>
  
  <p><strong>Reference Files</strong></p>
  
  <p>My freetds.conf file reads like this:</p>
  
  <pre><code>[MYSERVERNAME]
      host = MyServerIP
      port = 1433
      tds version = 7.3
      client charset = UTF-8
  </code></pre>
  
  <p>My odbcinst.ini  file reads like this: </p>
  
  <pre><code>[FreeTDS]
  Description = TD Driver (MSSQL)
  Driver = /usr/local/lib/libtdsodbc.so
  Setup = /usr/local/lib/libtdsodbc.so
  FileUsage = 1
  </code></pre>
  
  <p>My odbc.ini file reads like this:</p>
  
  <pre><code>[MYSERVERNAME]
  Driver = FreeTDS
  Server = MyServerIP
  Port = 1433
  </code></pre>
  
  <p>I am at a complete loss, having spent far more hours than I should have on this.  If anyone has any suggestions, I’d be forever grateful.  </p>
  
  <p>Thanks.</p>