How to - MS SQL Server interface



Microsoft SQL Server interface in Orchestrator




Orchestrator uses the Ruby DBI (DataBase Interface) module to provide a database independent interface. It supports MySQL, ODBC and Oracle interfaces.


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.


Dependencies installation


Install the gcc compiler:

# yum -y install gcc


Install FreeTDS:

# wget

# 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.


For this purpose, download unixODBC from

Install the unixODBC package with:

# tar xvfz unixODBC-2.3.2.tar.gz

# cd unixODBC-2.3.2

# ./configure

# make

# 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.



FreeTDS setup



The FreeTDS configuration file is /etc/freetds.conf

Add a section for your MS SQL Server such as:


host =

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.

You can check the mappings here:


Check if you can connect from your given server before trying the FreeTDS driver:

$ telnet <IP_OR_MSSQL_SERVER_NAME> 1433


Check you can connect with the FreeTDS driver:

# TDSVER=7.0 tsql -H -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!


ODBC setup


Check where are the default config files:

# odbcinst -j

unixODBC 2.3.2

DRIVERS............: /usr/local/etc/odbcinst.ini

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



Set the ODBC driver file:



Description = v0.80 with protocol v8.0

Driver = /usr/lib64/

UsageCount = 2

TraceFile = /tmp/sql.log

Trace = Yes


Make sure that the referenced library exists:

# ll /usr/lib64/

-rwxr-xr-x 1 root root 413600 Feb 27 2012 /usr/lib64/


Set the system data source file:



Driver = FreeTDS

Description = scheduall

Trace = No


Port = 1433



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.

# isql -v MSDB Orchestrator Titi


Finally test the database query plugin to make sure that the DB queries work well.


In the plugin, enter ODBC as the Database type, enter your data source name (e.g. MSDB), your database username and password.

Powered by Zendesk