Mark BrodyEric HulbertJeremy SherwoodBrady WilsonThomas AlombroBrian VenturaSarah MohrSteve BrennemanSteve CronburgScott GibsonOpus Interactive Staff

Connect CentOS 5.5 64bit to a Microsoft SQL Server database using unixODBC and FreeTDS

Brady Wilson

Brady Wilson

Chief Technology Officer

 

Posted Friday, July 9th, 2010

 

Brady Wilson

 


Connect CentOS 5.5 64bit to a Microsoft SQL Server database using unixODBC and FreeTDS

 

 

                Versions used: 

                CentOS 5.5 64bit

                MSSQL 2005 SP3

                unixODBC 2.3.0

                FreeTDS 0.82

 

1. Create a working folder and go there

 

# mkdir /home/work && cd /home/work

 

2. Get and build unixODBC

 

# wget http://www.unixodbc.org/unixODBC-2.3.0.tar.gz

# tar -zxvf unixODBC*.tar.gz

# cd unixODBC-2.3.0

#./configure

# make

# make install

 

3. Get and build FreeTDS

 

# wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

# tar -zxvf freetds-stable.tgz

# cd freetds-0.82

 

Add some environment variables

 

# nano /etc/profile

 

Add:

 

#

# TDS

#

SYBASE=/usr/local

LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib

export SYBASE LD_LIBRARY_PATH 

 

Save and exit nano

 

#./configure --with-tdsver=8.0 --with-unixodbc=/usr/local

# make

# make install

 

4. Add FreeTDS driver to unixODBC

 

Build a driver template

 

# nano tds.driver.template

 

Add:

 

[FreeTDS]

Description     = MSSQL Driver

Driver          = /usr/local/lib/libtdsodbc.so

 

Save and exit nano

 

Import the driver

 

# odbcinst -i -d -f tds.driver.template

 

(adds entry in /usr/local/etc/odbcinst.ini)

 

5. Add datasource

 

Build a datasource template

 

# nano tds.datasource.template

 

Add:

 

[your-DSN-name]

Driver = FreeTDS

Description = Northwind sample database

Trace = No

Server = xxx.xxx.xxx.xxx

Port = 1433

Database = your-DB-name

 

Save and exit nano

 

Import the DSN

 

# odbcinst -i -s -f tds.datasource.template -l

 

6. Test

 

# isql you-DSN-name your-user-name your-password

 

If everything jives you should get to a prompt:

 

SQL>

 

From here you can do a select statement against a table to verify you get results.

 

 

Configuration files:

 

unixODBC:

 

/usr/local/etc/odbcinst.ini = driver definitions

/usr/local/etc/odbc.ini = datasource name definitions

/usr/local/etc/freetds.org = FreeTDS configuration file

 

# odbcinst -j - tells you where proper config files live

 

 

Troubleshooting:

 

# nano /usr/local/etc/odbcinst.ini

 

[ODBC]

DEBUG = 1

TraceFile = /home/your-user-name/sqltrace.log

Trace = Yes

 

The sqltrace.log should show you the reasons for any issues you experience

 

 

References:

 

http://www.unixodbc.org/

http://www.freetds.org/

http://www.unixodbc.org/doc/FreeTDS.html

http://www.unixodbc.org/doc/FreeTDS2.html

http://www.easysoft.com/developer/interfaces/odbc/linux.html#env_unixodbc



Tags: CenOS, 64 Bit, Microsoft SQL Server


Comments

You must be a member to post comments

Opus Interactive on Facebook

 

-- CALL US AT --

503-972-6677

1-866-678-7955