Contents
To access a MS SQL Server Database via unixODBC you need a driver. There are several drivers in the market. A free version is FreeTDS.
FreeTDS Installation
Install from rpm
- For RedHat RHEL 7 freetds is available as rpm in the Epel Repository.
- For RedHat RHEL 6 freetds is available as rpm in the Epel Repository, but only the outdated version 0.91
The following examples do not use these packages.
Download
Go to
http://www.freetds.org/
and download the stable release
ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
I got version freetds-0.95.79
Install
In this example the file freetds-patched.tar was downloaded to directory /opt
cd /opt # unpack tar xfz freetds-patched.tar.gz cd freetds-0.95.79 # configure FreeTDS ; tdsver is the default version, if nothing is specified in odbc.ini ; if you have compiled unixODBC from source as shown in this blog, specify --with-unixodbc=/usr/local/ ./configure --silent --with-tdsver=7.0 --with-unixodbc=/usr/local/ # compile make # install - needs root privileges make install
For configuration and compilation you do not need root privileges, only for the last step make install you need root privileges.
First Test
The tsql utility is provided as part of FreeTDS expressly for troubleshooting.
/usr/local/bin/tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.95.79
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 7.0
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: no
OpenSSL: no
GnuTLS: no
For your first connection you need the following informations about your target MS SQL Server database:
- Hostname (or ip address)
- Port number (usually 1433)
- Database name
- Username
- Password
# replace hostname, dbname, user and password with your values: echo -e 'select @@version\nGO' \ | /usr/local/bin/tsql -H hostname -p 1433 -D dbname -U user -P password
locale is "en_US.UTF-8"
locale charset is "UTF-8"
Default database being set to test801
1> 2>
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
(1 row affected)
1>
When you see the version of your SQL Server database, your connection is successfull.
Configure unixODBC
Next step is to integrate the Freetds Driver into unixODBC.
With FreeTDS we have two alternatives:
-
- ODBC-only configuration: all configuration data in the file
odbc.ini
This alternative will be used in the following examples. - ODBC-combined configuration: configuration data in the file
odbc.ini
- ODBC-only configuration: all configuration data in the file
and in freetds.conf
Details: FreeTDS User Guide, Chapter 4. Preparing ODBC
http://www.freetds.org/userguide/prepodbc.htm
Configure dsn in odbc.ini
The file odbc.ini in usually located /usr/local/etc/odbc.ini, /etc/odbc.ini, /etc/unixODBC/odbc.ini.
If you have compiled unixODBC from source as described in the previous article, you will use /usr/local/etc/odbc.ini.
For each connection a datasource name dsn is configured in odbc.ini
; testdsn is the datasource name (not case sensitive) [testdsn] ; driver: unixODBC will use the specified driver driver = /usr/local/lib/libtdsodbc.so ; server: hostname (or ip address) of your MS SQL Server server = hostname ; port: port number on the specified host (usually 1433) port = 1433 ; database: name of database database = dbname ; tds_version: ; 7.0 for Microsoft SQL Server 7.0 ; 7.1 for Microsoft SQL Server 2000 ; 7.2 for Microsoft SQL Server 2005 ; 7.3 for Microsoft SQL Server 2008 tds_version = 7.3
Test the configured dsn
The configured dsn can be tested with isql:/usr/local/bin/isql (if your compiled unixODBC from source) or /usr/bin/isql (unixODBC installed as package)
$ echo "select @@version; | /usr/local/bin/isql -v testdsn username password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 (Build 10586: ) (Hypervisor)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL> quit
Next Step
If the isql Test works, your are ready to configure the Oracle Gateway to ODBC and the Database Link in your Oracle Database: Configure Oracle Gateway for ODBC with FreeTDS