Connect to MS SQL Server and Sybase ASE from Mac OS X and Linux with unixODBC and FreeTDS

Filed under

Although I haven't had need to do so for freelance jobs, I do administer several Windows-based Sybase and MS SQL Servers at my day job. It's helpful to be able to connect directly to these servers from my Mac desktop in addition to using Apple RDC to manage and work against them directly via MS SQL Server Management Studio and Sybase Central. I had previously installed unixODBC and FreeTDS on an OS X 10.4 desktop. This old Tiger was replaced by a new machine running Lion recently. I tried installing unixODBC and FreeTDS using MacPorts on the new machine, but was unable to connect to Sybase servers. I found Vinh Nguyen's blog post which refreshed my memory on the installation steps. Vinh notes problems with MacPorts, too. With a refreshed memory and some trial and error, I was able to get everything up and running again. For future reference, here are the steps from the latest installation and configuration of unixODBC and FreeTDS on OS X Lion.

Installation Requirements

You'll need Apple's developer tools, so be sure you have XCode installed. A commenter on Vinh's post noted errors experienced before installing gawk, which I had already installed via MacPorts. I downloaded all packages with wget from the command line.

Configure and Build unixODBC and FreeTDS from Source

FreeTDS will work with either iODBC or unixODBC, but I've not had luck with iODBC. Build and install unixODBC first and then FreeTDS. I built and installed everything in the standard location, /usr/local, feel free to change the path to suite your needs.

cd /usr/local/src 

unixODBC

Nothing special here.

wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.1.tar.gz
tar xvfz unixODBC-2.3.1.tar.gz cd unixODBC-2.3.1
./configure
make
sudo make install 

FreeTDS

I followed Vinh's recommendation of installing the latest development version, so I grabbed the latest FreeTDS nightly snapshot.

wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz 

Configure the installer with the proper TDS version and path to unixODBC.

./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
make
sudo make install

Check the FreeTDS install with tsql.

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

If you have Sybase or MS db-lib installed locally, check out the FreeTDS documentation for instructions on further configuration options.

Configuration

To get up and running for basic usage, you'll need to define your servers and DSNs in the freetds.conf and odbc.ini files, respectively. Either create and edit these at the system-level in /usr/local/etc or just for you in your home directory. For more advanced language and connection needs, you'll also want to configure locales.conf and pool.conf.

Vinh states that he needed to use TDS version 8.0 for MS SQL Server connections. I found that I needed to use TDS version 5.0 for Sybase connections.

Here's a basic Sybase server DSN defined in ~/.odbc.ini. The default section contains default (surprise) values to be used by servers. Each host's DSN may override or extend the default values.

[default]
Driver=/usr/local/lib/libtdsodbc.so
TDS_Version=5.0
Port=5000
;UseCursor=1
;Trace=Yes
;Tracefile=/tmp/odbc.log
 
[SybaseDSN]
Description=Sybase ASE 12.5 or 15 Server
ServerName=HOSTNAME
Database=DATABASE

And here's the corresponding Sybase server entry in ~/.freetds.conf. The ServerName in the odbc.ini's DSN needs to match a HOSTNAME defined in the freetds.conf file! As with the odbc.ini file's default section, the global definitions in freetds.conf apply to all servers defined, unless the server section defines the value. Uncomment the dump and debug values to log connection output if have problems connecting.

[global]
  tds version = 5.0
  client charset = UTF-8
 
  # Whether to write a TDSDUMP file for diagnostic purposes
  # (setting this to /tmp is insecure on a multi-user system)
  #dump file = /tmp/freetds.log
  #debug flags = 0xffff
  #debug level = 10
 
  # Command and connection timeouts
  #timeout = 10
  connect timeout = 10
 
  # If you get out-of-memory errors, it may mean that your client
  # is trying to allocate a huge buffer for a TEXT field. 
  # Try setting 'text size' to a more reasonable limit
  text size = 64512
 
  # Default TDS version for Sybase 12.5 and 15
  tds version = 5.0
 
  # Default Sybase ASE port on Windows
  port = 5000
 
[HOSTNAME]      
  host = IP.ADDRESS.HERE

Check out the default odbc.ini and freetds.conf configuration files for MS SQL Server connection examples.

unixODBC and FreeTDS Usage

unixODBC provides an isql client, comparable to those which ship with MS SQL Server and Sybase. After configuring your ODBC DSN entries and FreeTDS servers, isql use is straightforward.

isql DSN USERNAME PASSWORD 

Note that isql does not require that you enter "go" after each SQL query or command.

FreeTDS provides the following utilities, among several others.

From the FreeTDS manual:

bsqldb - A non-interactive equivalent of the isql utility programs distributed by Sybase and Microsoft. Like them, bsqldb uses the command "go" on a line by itself as a separator between batches. The last batch need not be followed by "go".

fisql - A complete replacement of the isql utility programs distributed by Sybase and Microsoft. Like them, fisql uses the command "go" on a line by itself as a separator between batches.

fisql -H HOST -S SERVER -U USERNAME -P PASSWORD -D DATABASE -s COLUMNSEPERATOR -i INPUTFILE -o OUTPUTFILE 

After confirming that you can indeed connect to and work against targeted database servers, install your preferred programming languages TDS bindings (Ruby, Python, Perl, C). Java users should check our jTDS.

For PHP users, build PHP with --with-mssql=/usr/local/bin/freetds Sybase users should use PHP's MS SQL functions, which are essentially the same as PHP's Sybase functions.

Good luck!

Share