Note: please see comments by Ken Collins (metaskills.net) at the bottom of this entry. He mentions a better way to do this without even installing FreeTDS.

You will need FreeTDS to connect to MSSQL Server on Unix-based environments. You MUST use Homebrew to install FreeTDS on the Mac. MacPorts is evil and will not install FreeTDS correctly. Installation of Homebrew is covered excellently in the Homebrew installation docs:
https://github.com/mxcl/homebrew/wiki/installation

Edit the FreeTDS configuration for the FreeTDS library:

brew edit freetds

Replace from “def install” to “end ” with:

def install 
  args = ["--prefix=#{prefix}",
          "--with-tdsver=7.0",
          "--enable-msdblib",
          "--mandir=#{man}"]
 
  system "./configure", *args
  system 'make'
  system 'make install'
end

Install FreeTDS:

brew install freetds

Add an entry to the bottom of your freetds.conf for your MSSQL Server. This file is found at /usr/local/etc/freetds.conf:

[my_sql_server] 
host = <hostip>
port = 1433 
tds version = 7.0

Add the TinyTDS and activerecord-sqlserver-adapter gems to your Rails Gemfile.

  gem 'tiny_tds'
  gem 'activerecord-sqlserver-adapter', :require => false

Run bundler to install the gems:

bundle install

Finally, add the connection to your database.yml file:

development:
  adapter: sqlserver
  mode: dblib
  dataserver: my_sql_server
  database: my_database_name
  username: my_username
  password: xxxxx
  timeout: 5000

A reference for installing UnixODBC (which I don’t recommend), you can follow this tutorial:
MSSQL2005 on Rails on Snow Leopard (the easiest way I know how.)