How to install and configure FreeTDS as an ODBC connector to Microsoft SQL Server on CentOS/RHEL for Lasso 9

Introduction

CentOS, Redhat Enterprise Linux (RHEL), and Mac OS X do not include a database connector for Microsoft SQL Server (MSSQL) that web applications and programming languages can use to interact with the data source. Most Windows operating systems already include a connector. This article covers the specific implementation of FreeTDS as an ODBC connector to Microsoft SQL Server on CentOS/RHEL for Lasso 9, but the concepts should apply to any UNIX- based operating system and web programming language.

Prerequisites

It is assumed you have already set up Microsoft SQL Server 2005 or later, its databases and tables, and a user that Lasso will use to connect to SQL Server.

When we configure FreeTDS, we will use an "ODBC-combined" configuration. Read more about ODBC and FreeTDS background and terminology.

Install and configure the EPEL repository

Extra Packages for Enterprise Linux (or EPEL) is a Fedora Special Interest Group that creates, maintains, and manages a high quality set of additional packages for Enterprise Linux, including, but not limited to, Red Hat Enterprise Linux (RHEL), CentOS, Scientific Linux (SL), and Oracle Enterprise Linux (OEL).

EPEL has an "epel-release" package that includes GPG keys for package signing and repository information.

Note

For more information, see: https://fedoraproject.org/wiki/EPEL

1
2
# download and install the 'epel-release' package and its GPG keys
sudo rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

It's a very good practice to verify the authenticity of packages you install.

1
2
# Verify the GPG key
gpg --quiet --with-fingerprint /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

Compare the result to the EPEL-6 key, reproduced below for convenience.

1
2
pub  4096R/0608B895 2010-04-23 EPEL (6) <epel@fedoraproject.org>
      Key fingerprint = 8C3B E96A F230 9184 DA5C  0DAE 3B49 DF2A 0608 B895

Verify that the EPEL repo is configured properly for yum. Your repos should end up like the following.

1
$ cat /etc/yum.repos.d/epel.repo
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[epel]
name=Extra Packages for Enterprise Linux 6 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6

[epel-debuginfo]
name=Extra Packages for Enterprise Linux 6 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch/debug
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

[epel-source]
name=Extra Packages for Enterprise Linux 6 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/6/SRPMS
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
gpgcheck=1

Install freetds

1
sudo yum install freetds

Verify that freetds has been built with the correct settings.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

Configure freetds

Documentation of settings for freetds.conf.

Edit /etc/freetds.conf.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[global]
# TDS protocol version
;   tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

# Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
    host = symachine.domain.com
    port = 5000
    tds version = 5.0

# A typical Microsoft server
[egServer70]
    host = ntmachine.domain.com
    port = 1433
    tds version = 7.0

# MSSQL ServerName
[MyMSSQLServerName]
    host = myhostname.mssqlserver.com
    port = 1433
    tds version = 7.0
    # client charset = UTF-8

Edit /etc/odbc.ini

See documentation Connection attributes that may appear in odbc.ini.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
[ODBC Data Sources]
MyMSSQLServerName = MSSQL Server

[MyMSSQLServerName]
Driver          = /usr/lib64/libtdsodbc.so.0
Description     = MSSQL Server
Trace           = No
Server          = myhostname.mssqlserver.com
Database        = MyDatabaseName
Port            = 1433
TDS_Version     = 7.1

[Default]
Driver          = /usr/lib64/libtdsodbc.so.0

Test the freetds configuration

1
TDSVER=7.0 tsql -H myhostname.mssqlserver.com -p 1433 -U myusername
1
osql -S MyMSSQLServerName -U myusername -P mypassword

Add the ODBC datasource in Lasso Admin

If you do not see the ODBC or SQLServer datasource option after installing the freetds library, you must restart the Lasso Instance.

The Lasso Guide for "SQL Server Data Sources" may be helpful, but note that it is written primarily for Windows users who have the MSSQL Server libraries already installed, so the instructions may differ from what works on UNIX-based systems as described below.

Open the URL: http://www.myserver.com/lasso9/Admin/ds

Click ODBC.

Click Add host.

Enter the following values.

1
2
3
4
5
Name = MyMSSQLServerName
Port = 1433
Enabled = Yes
Username = myusername
Password = mypassword

Click the Add Host button. The user interface is very misleading and it can sometimes take up to 2 or 3 minutes for a host to be added. You should see either a javascript alert if there is a failure, else if successful the page should refresh and display the host. Expand the host to show a list of databases or views for MSSQL.

SQL and Lasso code examples

1
2
3
4
5
6
7
8
9
# set an environment variable and run tsql with arguments
TDSVER=7.0 tsql -H myhostname.mssqlserver.com -p 1433 -U myusername
# enter the password
# issue the following query commands, if connection is successful
# you will need to write your own query adapted to your schema, of course
SELECT TOP 3 [Column1], [Column2], [Column3] FROM [Table] WHERE [Column1] = 'Yes'
go
# 3 records should be returned
exit
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
[
local(sql = "SELECT TOP 3 [Column1], [Column2], [Column3] FROM [mytable] WHERE [Column1] = 'Yes'");
inline(
   -host=(:
      -datasource='odbc',
      -name='MyMSSQLServerName',
      -username='myusername',
      -password='mypassword'
    ),
    -database='mydatabase',
    -table='mytable',
    -sql = #sql,
    -maxrecords=1000) => {^
    '<h1>'+found_count+'</h1>'
    '<h1>'+error_currenterror+'</h1>'
    '<h1>'+action_statement+'</h1>'
    '<h1>'+records_array+'</h1>'
    records => {^
        field('Column1') + ', ' + field('Column2') + ', ' + field('Column3')
    ^}
^}
]

Did you like this article? Please send me a Gratipay, as little as 25¢ per week!

Updates, suggestions and comments regarding this article may be sent to Steve Piercy, web@stevepiercy.com or comment using Disqus.


Written by Steve Piercy in Lasso on Wed, Aug 20, 2014.
Last modified: Wed, Aug 20, 2014
Tags: FreeTDS, ODBC, Microsoft SQL Server, MSSQL, CentOS, RHEL, Lasso

Comments

comments powered by Disqus