How to configure an SQL client for MS SQL Server on a RHEL host

Unlike the Oracle client which provides you with the required drivers and tools (e.g. sqlplus) to execute SQL statements against an Oracle database on a variety of platforms, Microsoft does not have such drivers and clients for non-Microsoft platforms. For example, there is no Microsoft driver which you can install on RHEL 4 to allow you configure an SQL interface to Microsoft’s SQL Server.

The goal of ODBC is to solve this very problem by providing a standard software interface for accessing a variety of database management systems on a variety of platforms.

Given below are steps to configure an SQL interface with MS SQL Server on a Red Hat Enterprise Linux 4 (RHEL 4) host:

STEP 1: Verify the installation of unixODBC

unixODBC is provided with Linux by default for recent versions of most Linux distributions. Refer the unixODBC home for more details on unixODBC

rpm -qa | grep unixODBC

If the unixODBC packages are not installed, then download them and install them using rpm

STEP 2: Install FreeTDS

Refer the FreeTDS home for more details on FreeTDS. Download the latest stable release and install it using rpm

STEP 3: Configure a DSN

In order for the unixODBC software to interface with MS SQL Server, the relevant database access details must be provided in a Database Source Name (DSN). To configure a DSN, edit /etc/odbc.ini and add the relevant details. Given below is a sample DSN in /etc/odbc.ini :

[MY_DSN]
Driver=/usr/lib64/libtdsodbc.so.0
Server=192.168.1.1
Port=1433
Database=TEST_DB
TDS_Version=7.0

In the sample DSN above, /usr/lib64/libtdsodbc.so.0 is the absolute path to the FreeTDS driver.

STEP 4: Use isql to interface with MS SQL Server

After successfully completing the 3 steps above, you are now ready to perform operations on the database. To do so, you may use the isql utility that is bundled with the unixODBC package. Given below are few examples on using isql :

# The examples below assume you have a DSN called MY_DSN
# configured in /etc/odbc.ini and the isql utility in your PATH.
#
# Example 1 : Open an interactive session
#
isql MY_DSN username password
 
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
#
# Example 2 : Execute an SQL statement. Assume the statement is in a file
# called test.sql. The last line in test.sql must be a blank line.
#
cat test.sql | isql MY_DSN username password

Refer the isql man page for more details on using the isql utility.