Connecting to Microsoft SQL Server from Mac OS X
Updated on July 2nd, 2008.
As you may know, I develop a lot of new web applications using Ruby on Rails, and to completely buy in to the whole “cult that is Rails”, I purchased a Macbook Pro for development. I am really happy with it, but one of the problems I ran across is the fact that there’s no built-in or prepackaged way to connect to Microsoft SQL Server which I use at work for a lot of my projects.
This guide is written from my notes and should give you the tools you need to get up and running. Some of this information comes from the Ruby on Rails wiki, but some things there are a bit misleading.
This now works on Leopard too!
Getting everything ready
We’ll build everything we need from source. Open up your favorite terminal and create the following folders:
mkdir src mkdir /Library/ODBC
We’ll download all of the source files into the src/ folder so we don’t litter up the hard drive with miscellaneous files.
Next, install Xcode and MacPorts, and be sure to update your path per the Macports instructions.
Install wget.
sudo port install wget
Install and configure FreeTDS
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz tar zvxf freetds-stable.tgz cd freetds-0.82 ./configure
Attention Leopard Users!
You need to do an additional step here:
cp /usr/bin/glibtool libtool
Then you can continue on…
make sudo make install cd .. rm -rf freetds-0.82/
To test the configuration, run tsql -C. You’ll see the below output or something similar.
Compile-time settings (established with the "configure" script):
Version: freetds v0.64
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: yes
unixodbc: no
Next, configure FreeTDS by editing /usr/local/etc/freetds.conf. Add the following code to the bottom of this file:
[MY_SERVER_NAME] host = db.mydomain.com port = 1433 tds version = 8.0
The MY_SERVER_NAME placeholder is an identifier for your server. Examples might be PRODUCTION_RAILS_SERVER or something t hat helps you identify it in other configuration files.
Configuring iODBC to play nice
If you’ve ever configured the ODBCUnix packages for Linux then you might be familiar with this section. I tried to use a configuration that I’ve used on Ubuntu before but with no luck. It turns out that there are a few extra steps when it comes to OS X and ODBC with FreeTDS.
iODBC is going to look for the configuration files in /Library/ODBC which was not created on my system. Create that folder if it’s not there.
mkdir /Library/ODBC
Edit the file /Library/ODBC/odbcinst.ini and place the following contents in that file:
[ODBC Drivers] TDS = Installed [TDS] Description = FreeTDS Driver = /usr/local/lib/libtdsodbc.so Setup = /usr/local/lib/libtdsodbc.so
This file basically binds FreeTDS to iODBC and is really, really important to getting this whole thing working. Make sure that you have both the Driver and Setup paths correct (they do in fact point to the same file.)
Finally, tie it all togoether by editing the file /Library/ODBC/odbc.ini and using this code:
[ODBC Data Sources] MY_SERVER_NAME = TDS [MY_DSN] Driver = /usr/local/lib/libtdsodbc.so Description = Description of this database connection Trace = yes TraceFile = /tmp/odbc.log Servername = MY_SERVER_NAME Database = MY_DATABSE_NAME
Test the connection.
iodbctest "dsn=MY_DSN;uid=username;pwd=secret"
You should get some sort of message saying it connected successfully.
. If you got a message saying that the data source name could not be found then you need to check your configuration. If your computer just seems to hang for ever without responding, check your Internet connection and check to see if the DBA has firewalled your machine off.
Getting Rails to work
Assuming that Rails is already installed, you need to install two additional libraries for Ruby.
cd ~/src wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9995.tar.gz tar -zxvf ruby-odbc-0.9995.tar.gz cd ruby-odbc-0.9995 ruby extconf.rb make sudo make install cd .. rm -rf ruby-odbc-0.9995/ wget http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz tar -zxvf ruby-dbi-all-0.0.23.tar.gz cd ruby-dbi-all ruby setup.rb config --with=dbi,dbd_odbc ruby setup.rb setup sudo ruby setup.rb install cd .. rm -rf ruby-dbi-all/ wget http://rubyforge.org/frs/download.php/33959/dbi-0.2.0.tar.gz tar -zxvf dbi-0.2.0.tar.gz cd dbi-0.2.0 ruby setup.rb config --with=dbi,dbd_odbc ruby setup.rb setup sudo ruby setup.rb install cd .. rm -rf dbi-0.2.0
That’s it. That should get everything working so you can connect via Ruby.
. To add additionlal database servers, add them to the freetds.conf file. To add additional DSNs that point to databases on one of the existing servers in freetds.conf, change odbc.ini in the /Library/ODBC folder.
Database configuration in Rails
Here’s the database.yml configuration file for a typicall Rails application using a DSN.
production: database: database_name adapter: sqlserver mode: odbc dsn: MY_DSN username: foo password: password
Nice work, Brian. I’m sure I’m going to need this soon.
This is great. Thanks so much for putting this together. Would you mind posting an example Rails database.yml too? Thanks.
Can you please help me out while Connecting to Sybase Enterprise Server from Linux(SUSE/Sabayon) . Also an example of database.yml too..Thanks.
Hi, Brian — thanks for the article. Just a quickie — are the values ‘MY_SERVER_NAME’, ‘MY_DATABASE_NAME’, and ‘MY_DSN’ supposed to be swapped out for more specific values?
& thanks alot. Doug.
Hi — it seems there is a problem with FreeTDS on OSX Leopard — details here:
http://article.gmane.org/gmane.comp.db.tds.freetds/8992
Cheers, Doug.
Nice article but I cannot get it to work for nothing. The closest I can get is Unable to connect to data source (0) SQLSTATE=08001
Thanks
Thom
Excellent! I would recommend updating the tutorial and dropping the Mac Ports requirement. I followed your tutorial without installing Mac Ports by just downloading the code from a browser rather than using the wget.
I did run into one glitch related to Rails 2.1. I had to install the gem activerecord-sqlserver-adapter in order to get the require ‘dbi’ to work.
Thanks again! You saved me a ton of time!
DUDE! You rock. Thank you thank you thank you! I’d tried to get this working before using the Rails documentation, but (as you say) it was convoluted, and more importantly it didn’t include the “for Leopard users” step. We can now properly integrate our legacy database with our new applications! No more DTS transformations and regular sftp processes. WOO! Next step…. kill windows.
@doug: yes they are supposed to be swapped out – though things will work if you don’t change them (it’s just that for your second database and/or dsn, you’ll have to select something else).
@vabb_z: you can’t be serious about Sybase! Brian’s helped enough as it is and shouldn’t be doing your leg work for you (IMO).
Brian, thank you again – you’ve saved us hours (likely days or even weeks) of continued tedium.
Thanks for the great write-up. The connection seems to work just fine.
I’m having trouble with certain rake commands though, and I’m wondering if anyone else has experienced the same. Namely, the following rake tasks fail:
$ rake db:test:prepare
/opt/local/bin/osql: illegal option — E
/opt/local/bin/osql: illegal option — E
$ rake db:test:purge
/opt/local/bin/osql: illegal option — E
/opt/local/bin/osql: illegal option — E
I assume db:test:prepare fails because it calls db:test:purge which fails. I’m not sure though why the ‘illegal option’ error is coming.
Has anyone else seen this? Did you find a solution? Thanks in advance for any help.
This posting is greatly appreciated. It has not only saved me a huge amount of time, it was a great learning exercise.
Thanks!