Connecting to Microsoft SQL Server from Mac OS X

Posted by Brian in Rails, snacks (March 8th, 2007)

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

10 Responses to ' Connecting to Microsoft SQL Server from Mac OS X '

Subscribe to comments with RSS or TrackBack to ' Connecting to Microsoft SQL Server from Mac OS X '.

  1. on March 8th, 2007 at 7:00 am

    Nice work, Brian. I’m sure I’m going to need this soon.

  2. Steve Erickson said,
    on March 17th, 2007 at 10:40 am

    This is great. Thanks so much for putting this together. Would you mind posting an example Rails database.yml too? Thanks.

  3. vabb_z said,
    on August 7th, 2007 at 11:11 pm

    Can you please help me out while Connecting to Sybase Enterprise Server from Linux(SUSE/Sabayon) . Also an example of database.yml too..Thanks.

  4. doug livesey said,
    on March 19th, 2008 at 2:28 am

    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.

  5. doug livesey said,
    on March 19th, 2008 at 2:50 am

    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.

  6. Thom said,
    on May 22nd, 2008 at 6:39 pm

    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

  7. Tom said,
    on July 25th, 2008 at 11:11 am

    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!

  8. Damien said,
    on October 12th, 2008 at 8:44 pm

    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.

  9. Austin Moore said,
    on May 15th, 2009 at 5:26 am

    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.

  10. Stew said,
    on June 19th, 2009 at 4:37 pm

    This posting is greatly appreciated. It has not only saved me a huge amount of time, it was a great learning exercise.

    Thanks!

Leave a reply

:mrgreen: :neutral: :twisted: :shock: :smile: :???: :cool: :evil: :grin: :oops: :razz: :roll: :wink: :cry: :eek: :lol: :mad: :sad: