Date June 15th, 2016 Author Vitaly Agapov

So let's assume that we need a small keyhole to look into SQL Server database and query some data from it. Maybe we'd like to automate some reporting stuff or to make some web GUI with some information stored in MSSQL. And of course we don't want to deal with Windows.

Fortunately there is such a project as FreeTDS. It gives us a driver for using with ODBC engine. I will show how to configure it and how to retreive some data right from MSSQL. 

Everything written below is true for the case of RHEL/CentOS 7.

Step 1. Install FreeTDS driver

Firstly we should install freetds package. It can be installed from EPEL repository and will get unixODBC by dependency.

yum install freetds

Step 2. Configure ODBC

ODBC (Open Database Connectivity) is a standard API for interacting with any DBMS independently of the operating systems or databases themselves. For further information on ODBC refer here: http://www.unixodbc.org/odbcinst.html.

Foremost we should define the driver in file /etc/odbcinst.ini. To do that add or edit such a section in this file:

[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
Driver=/usr/lib64/libtdsodbc.so.0
UsageCount=1

Check if the driver configured:

# odbcinst -q -d
[FreeTDS]

Then we configure database in /etc/freetds.conf:

[SOME-MSSQL01]
host = <address>
port = 1433
# define port OR instance
# instance = <instancename>
tds version = 8.0
client charset = UTF-8

The list of instances can be obtained with tsql:

tsql -LH <address>

Then we configure the ODBC data source in file /etc/odbc.ini:

[some-db]
Driver = FreeTDS
Description = MSSQL database
# Servername corresponds to the section in freetds.conf
Servername = <SOME-MSSQL01>
Database = <dbname>

Check the data source:

# odbcinst -q -s
[some-db]

Step 3. Query the database

Then we just use isql utility in batch mode to send SQL commands to stdin and get answers from stdout. Very easy:

echo "SELECT * FROM information_schema.tables;" | isql -v -b some-db <username> <password>
echo "EXEC sp_columns <table_name>" | isql -v -b some-db <username> <password>

Yup, this was easy.

Tags: Linux
Category: Linux | No comments »

Comments

Leave a comment