8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Linux » Here

Create an ODBC Data Source Name (DSN) on Linux

This article describes how to create an ODBC data source name (DSN) on Linux. The example create a SQL Server data source name on Oracle Linux 7 using the EPEL yum repository.

Installation

Install the ODBC driver manager and a SQL Server driver.

# yum install unixODBC unixODBC-devel freetds -y

The unixODBC package is available from the normal Oracle Linux yum repository. The freetds package is from the EPEL yum repository.

Validate Installation

Validate they are both installed correctly.

# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
#

# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.95.81
             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
                            OpenSSL: no
                             GnuTLS: yes
#

Create a Driver Entry

Add the FreeTDS driver into the "/etc/odbcinst.ini" driver list.

[FreeTDS]
Description=FreeTDS unixODBC Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsodbc.so.0
TDS_Version=7.2
UsageCount=1

Create Data Source Name (DSN)

The data source name (DSN) can be created in the "/etc/odbc.ini" or "~/.odbs.ini" file, depending on if you want it to be a system or user DSN.

[ODBC Data Sources]
MSSQLDEV = MSSQL Server

[MSSQLDEV]
# Reference driver from the "/etc/odbcinst.ini" file.
# Driver          = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the "/etc/odbcinst.ini" file.
Driver=/usr/lib64/libtdsodbc.so.0
Description     = MSSQL Server
Trace           = No
Server          = my-sql-server.example.com
Database        = MYDATABASE
Port            = 1433
TDS_Version     = 7.2
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES

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

You can read about the TDS_Version here, but I found using anything other than 7.2 can give problems against 2008R2 and 2014.

Test Data Source Name (DSN)

Test the data source.

# osql -S MSSQLDEV -U myuser -P mypassword
checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
	trying /tmp/sql ... no
	trying /tmp/sql ... no
	trying /etc ... OK
checking odbc.ini files
	reading /root/.odbc.ini
[MSSQLDEV] not found in /root/.odbc.ini
	reading /etc/odbc.ini
[MSSQLDEV] found in /etc/odbc.ini
found this section:
	[MSSQLDEV]
	Driver          = /usr/lib64/libtdsodbc.so.0
	Description     = MSSQL Server
	Trace           = No
	Server          = my-sql-server.example.com
	Database        = MYDATABASE
	Port            = 1433
	TDS_Version     = 7.1
	
looking for driver for DSN [MSSQLDEV] in /etc/odbc.ini
  found driver line: "	Driver          = /usr/lib64/libtdsodbc.so.0"
  driver "/usr/lib64/libtdsodbc.so.0" found for [MSSQLDEV] in odbc.ini
found driver named "/usr/lib64/libtdsodbc.so.0"
/usr/lib64/libtdsodbc.so.0 is an executable file
"Server" found, not using freetds.conf
Server is "my-sql-server.example.com"

Configuration looks OK.  Connection details:

                   DSN:	MSSQLDEV
              odbc.ini:	/etc/odbc.ini                 
                Driver:	/usr/lib64/libtdsodbc.so.0    
       Server hostname:	my-sql-server.example.com            
               Address:	123.123.123.123               

Attempting connection as banner ...
+ isql MSSQLDEV myuser 'mypassword' -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
#

For more information see:

Hope this helps. Regards Tim...

Back to the Top.