The communication protocol used to communicate with the MS SQL Server is called Tabular Data Stream (TDS).
FreeTDS is an open source project to provide an implementation of the TDS protocol for Unix-based systems.
FreeTDS supports an ODBC API. The required Ruby ODBC packages are provided here by unixODBC.
Install the gcc compiler:
# yum -y install gcc
# wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
# rpm -ivh epel-release-6-8.noarch.rpm
# yum install freetds freetds-devel
dbd-odbc-0.2.5.gem is already installed by default with Orchestrator.
We still need to install the ruby-odbc gem.
Install the unixODBC package with:
# tar xvfz unixODBC-2.3.2.tar.gz
# cd unixODBC-2.3.2
# make install
Install the Ruby ODBC package:
# cd /opt/aspera/orchestrator
# gem install vendor/gems_generic/ruby-odbc-0.99994.gem
Building native extensions. This could take a while...
Successfully installed ruby-odbc-0.99994
1 gem installed
Installing ri documentation for ruby-odbc-0.99994...
Installing RDoc documentation for ruby-odbc-0.99994...
ERROR: While executing gem ... (NoMethodError)
undefined method `file_name' for nil:NilClass
The ruby-odbc gem installation is done.
The FreeTDS configuration file is /etc/freetds.conf
Add a section for your MS SQL Server such as:
host = 172.16.2.173
port = 1433
tds version = 7.0
Host is the name or IP address of the SQL Server.
Port 1433 is the default port for connecting to MS SQL Servers.
The tds version relates to the version of SQL server you are using.
$ telnet <IP_OR_MSSQL_SERVER_NAME> 1433
Check you can connect with the FreeTDS driver:
# TDSVER=7.0 tsql -H 172.16.2.173 -p 1433 -U Orchestrator -P Titi
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-
Specify the SQL server address with the –H option, the database user name with the –U option and the password with the –P option.
If you get that 1> prompt, it’s a good sign!
Check where are the default config files:
# odbcinst -j
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Set the ODBC driver file:
Description = v0.80 with protocol v8.0
UsageCount = 2
TraceFile = /tmp/sql.log
Trace = Yes
Make sure that the referenced library exists:
-rwxr-xr-x 1 root root 413600 Feb 27 2012 /usr/lib64/libtdsodbc.so.0.0.0
Set the system data source file:
Driver = FreeTDS
Description = scheduall
Trace = No
Server = 172.16.2.173\BACKOFFICE
Port = 1433
Database = SCHEDUALL_LIVE
Driver must refer to a section name in odbcinst.ini
Server is the name or IP address of the SQL Server.
Port is the database communication port.
Database is the database name.
Copy /usr/local/etc/odbc.ini into /etc/odbc.ini
Copy /usr/local/etc/odbcinst.ini into /etc/odbcinst.ini
Test the connection with the isql client (installed with unixODBC).
Pass the data source name that you defined in odbc.ini along with your username and password.
Finally test the database query plugin to make sure that the DB queries work well.