8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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
- Validate Installation
- Create a Driver Entry
- Create Data Source Name (DSN)
- Test Data Source Name (DSN)
Related articles
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 "~/.odbc.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:
- The unixODBC Project home page
- FreeTDS
- Heterogeneous Services (Generic Connectivity) : Oracle to Non-Oracle Database Links Using ODBC
Hope this helps. Regards Tim...