OSX MSSQL Server ODBC drivers

OSX to MSSQL Server using FreeTDS and iODBC

The task appears daunting, not helped by the usual documentation overload.

• Step 1. Obtain the FreeTDS source tarball from freetds.org
  Step 2. unpack the tar ball and build using the following commands

 # compiling the current version is straightforward on snow leopard (10.6.6)

 $ ./configure

 $ sudo make

 $ sudo make install

Intermediate files can be removed with $make clean

After installation the configuration file
freetds.conf
will exist in both the current build folder and the default location /usr/local/etc/freetds.conf

It is recommended that these files be renamed .old and copy made to the user profile ~/.freetds.conf The config file should be edited as indicated within the file. Recommended global defaults are;

 tds version = 8 
 port = 1433

The tsql command can now be used to test the connection. For SQL servers using integrated security the user name is specified within single quotes as 'DOMAIN\username'

Examples of tsql connection commands;

 $TDSVER=8 tsql -H myserver.fqdn -p 1433 -U mat -P password
 tsql -H myserver.fqdn -p 1433 -U 'MSSS\mat'

 Note the tcp port must be specified when the hostname -H option is used.

 To use a sever specified in the freetds.conf file

 tsql -S mssql -U 'MSSS\mat'

Once connected, SQL can be entered at the prompt, remembering to USE the correct database and GO execute each command

Configuring ODBC using the iODBC manager

[b]Taken from www.stata.com help resources[/b] This FAQ provides an overview of the steps you need to take to configure ODBC on Unix and Linux. Most steps are common to both platforms, but some are specific to the database to which you are connecting. Before you start configuring the driver manager, make sure you know which database you are using.

Obtaining iODBC

On both Mac and Linux, make sure that your system is running the iODBC driver manager, which stores and manages all the information you need to connect to an ODBC data source. iODBC is an Open Source driver manager that is maintained by OpenLink Software and is released under a dual LGPL/BSD license.

• Linux
  The good news is that most versions of Linux ship with iODBC installed either by default or as an option. If you need to install iODBC, see the documentation for your version of Linux, or if you are comfortable compiling source code, see http://www.iodbc.org for the latest version as well as some generic RPMs.
• Mac OS X
  Mac OS X versions 10.2 and later ship with an iODBC utility, ODBC Administrator, which you can find under Applications:Utilities.

Obtaining unixODBC

If you are using a Linux/Unix machine and Stata 10 or later, you can also connect to a database with the unixODBC driver manager. In Stata, type set odbcmgr unixodbc to use the unixODBC driver manager. To change back to the default iODBC driver manager, type set odbcmgr iodbc.

Creating and configuring an ODBC Data Source

At this point, you should have either iODBC or unixODBC installed and running on your system. To configure an ODBC DSN, you will need to specify several parameters, which may vary, depending on the database. Here are a few of the most common parameters (items in bold are usually required):

• DSN: a DSN of your choosing.
• Database name: the name of the existing database.
• Driver: the driver you will use to communicate with the database (make sure that you use the right driver for your database).
• Server name: the domain name or IP address of the server that hosts the database.
• Username: the username you are using to connect to the database.
• Password: the password corresponding to the username.
• Port number: the port number you are using to connect to the database.

You can configure the ODBC data source either by using the iODBC Administrator utility or by modifying the ODBC configuration files manually.

1. iODBC Administrator Utility
   On both the Mac and Linux platforms, you can configure an ODBC data source using the iODBC Administrator (see http://www.iodbc.org for instructions on downloading and installing this utility). Using the iODBC Administrator, you can configure the following DSN information:
   
   
   • System DSN: An ODBC data source that you want to make available to anyone using the computer.
   • User DSN: A data source specific to a user.
   • Drivers: Links to the available database drivers. For example, if more than one DSN is connecting to a PostgreSQL database, you can define a Driver for all the DSNs to reference. If the driver location ever changes, you can modify the driver information, and all the DSNs will automatically refer to the new location.
2. Modifying ODBC configuration files
   The best way to configure ODBC is to modify the configuration files directly. These configuration files are well documented on the iODBC and unixODBC website. The most important configuration file is the .odbc.ini file (the period that precedes the filename makes this file invisible to most directory listings).
   
   The .odbc.ini file contains the connection parameters for system and user DSNs. The .odbc.ini file for System DSNs is usually stored in the system directory. For user DSNs, it is always stored in the user’s home directory, and there are normally as many .odbc.ini files as there are users.
   
   Here is an example of an actual .odbc.ini that will work on either Mac or Linux/Unix for a PostgreSQL database named 'Bug'. When placed in the user’s home directory, this file provides access to the ODBC database specified:

    
    [ODBC Data Sources]
    Bug = PostgreSQL 
           
    [Bug]
    Debug = 1
    CommLog = 1
    ReadOnly = no
    Driver = /usr/local/lib/psqlodbc.so
    Servername = 64.xxx.xxx.xx
    FetchBufferSize = 99
    Username = kevin 
    Password = passwordforkevin
    Port = 5432
    Database = bugdb
    
    [Default]
    Driver = /home/kst/tmp/libiodbc-3.0.5/odbcsdk/lib/oplodbc.so.1
   

   The Driver, Servername, and Username parameters are common to all database types on both the Mac and Linux/Unix platforms. The names and required information for the other parameters may differ, depending on the type of database. See your database’s documentation for information about specific parameters.

Testing the ODBC DSN

Usually, iODBC ships with a small test application that you can use to test the connection parameters of any DSN you define.