How to Access MS SQL Server from a unixODBC(FreeTDS) on CentOS 6.x ?

How to Access MS SQL Server from a unixODBC(FreeTDS) on CentOS 6.x ?

We assume that we have a recent version of unixODBC installed on your system and that we have MS SQL Server running somewhere & SQL Server should allow remote access and support connections using TCP/IP.

The communication protocol used by client software to communicate with MS SQL Server is called Tabular Data Stream (TDS). we are using FreeTDS


Download the latest version of FreeTDS from internet, 

Step 1: Un-compressing the tar file
# tar -zxvf freetds-0.63.tar.gz
# cd freetds-0.63

Step 2: Building FreeTDS
Now we need to configure with TDS version and path to unixodbc directory.
# ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
# make
# make install

Step 3: Register ODBC driver
Create a new file odbcinst.ini under /usr/local/etc/ directory.

[FreeTDS]    
Description = v0.63 with protocol v8.0    
Driver = /usr/local/freetds/lib/libtdsodbc.so   
Setup=/usr/lib64/libtdsS.so
FileUsage=1

Note : if your having 32 bit OS(i386 or i586) you should add "Setup=/usr/lib/libtdsS.so"

Now install the FreeTDS driver by
# odbcinst -i -d -f /usr/local/etc/odbcinst.ini

Step 4: Creating ODBC data source
Create a new file odbc.ini under /usr/local/etc/ directory.
[SQLServer]
Driver  = FreeTDS
Description = This is the Live Database
Trace   = No
Server  = 30.220.24.189
Port   = 1433
Database  = MainZoneCentral

Note: if we need log for the freetds, we can change Trace = Yes and redirect the logs to needed folder.

Note; we have executed previous commands as root (denoted by leading '#' character on given commands) but here we execute the command as a regular user. This is significant.
All users of the system should be accessable to the FreeTDS and the ODBC Drivers. So create the DSN as the user who is going to be using it.

$ odbcinst -i -s -f odbc.ini

Step 5: Check the connection
# tsql -S 30.220.24.189 -U sa -P pwd@123
where
-S Server IP
-U Username
-P Password

Sample Output
[java@ssserver ~]$ tsql -S 30.220.24.189 -U sa -P pwd@123
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

which means your able to connect Successfully

Step 6: Enabling in PHP
edit the php.ini file in etc folder   
   
#vim /etc/php.ini and add

extension = odbc.so

Save and close the file

Restart the Apache(httpd) Service to reflect the changes
#service httpd restart

Now your sphinx index is able to connect the MS SQL Server and fetch the records as indexes :)

EXTRA SECTION
Additional Info
odbcinst -j              # To check the ODBC version and configuration.
odbcinst -q -d           # View loaded drivers.
odbcinst -q -s           # View DSN entry
tsql -LH 22.33.55.44       # To list MSSQL server instant name,version etc.,
osql -S DSN -U Username -P ‘Password’ #list the configuration files loaded to connect ODBC
tsql -S DSN -U Username -P ‘Password’ #To check the connection