Microsoft SQL Server interface in Orchestrator

Overview

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 http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.r…

# 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 http://www.unixodbc.org/

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:

[egServer70]

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.

You can check the mappings here: http://www.freetds.org/userguide/choosingtdsprotocol.htm

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

1>

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 its 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

SQLSETPOSIROW Size.: 8

Set the ODBC driver file:

/usr/local/etc/odbcinst.ini

[FreeTDS]

Description = v0.80 with protocol v8.0

Driver = /usr/lib64/libtdsodbc.so.0.0.0

UsageCount = 2

TraceFile = /tmp/sql.log

Trace = Yes

Make sure that the referenced library exists:

# ll /usr/lib64/libtdsodbc.so.0.0.0

-rwxr-xr-x 1 root root 413600 Feb 27 2012 /usr/lib64/libtdsodbc.so.0.0.0

Set the system data source file:

/usr/local/etc/odbc.ini

[MSDB]

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.

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