ODBC working only with sudo

Question

I'm having troubles with unixODBC. I've installed the latest version and currently is working only with sudo. To test the ODBC I've used isql. Example:

# isql -v testTable
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

With sudo

# sudo isql -v testTable
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Why is that and how can I use it without sudo?

Note: I didn't used sudo while installed it. And I installed it as a root and trying to use it as a root.

UPDATE

# ls -ltr /usr/local/psql/etc/odbcinst.ini
ls: cannot access /usr/local/psql/etc/odbcinst.ini: No such file or directory
# ls -ltr /etc/odbcinst.ini
-rw-r--r-- 1 root root 740 28 nov  8,04 /etc/odbcinst.ini

Content of /etc/odbcinst.ini

~]# cat /etc/odbcinst.ini


# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver      = /usr/lib/psqlodbcw.so
Setup       = /usr/lib/libodbcpsqlS.so
Driver64    = /usr/lib64/psqlodbcw.so
Setup64     = /usr/lib64/libodbcpsqlS.so
FileUsage   = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver      = /usr/lib/libmyodbc5.so
Setup       = /usr/lib/libodbcmyS.so
Driver64    = /usr/lib64/libmyodbc5.so
Setup64     = /usr/lib64/libodbcmyS.so
FileUsage   = 1

Update /etc/odbc.ini

[myDSN]
Description         = MySQL Connection
Driver              = MySQL
Database            = testTable
User                = mysql
Password            = ******
Port                = 3310
Server              = localhost
Socket              = /var/lib/mysql/mysql.sock

Another update. Without sudo:

odbcinst -j
-bash: /usr/local/bin/odbcinst: No such file or directory

With sudo

sudo 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

Answer 1

Check /etc/odbcinst.ini, /etc/odbc.ini, ~/.odbc.ini and $ODBCINI to see what rights are assigned. I believe the user running the command may not have rights to one of those files, or may not even have the data source defined if it was created under a different user.

Taken from Common Error Messages and What's an odbc.ini and what do I put in it? Further more, check the /etc/odbc.ini and ~/.odbc.ini of the user you added the DSN to, plus the user you are running as. If these are the same, you only need to do this once.

---

Common Error Messages

[iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQLSTATE=IM002

There are several reasons why this message could occur. The best solution is to trace through what's happening: your application has been linked against libiodbc, which has tried to find an odbc.ini file one way or another - either through the ODBCINI environment variable or the fall-back paths (typically /etc/odbc.ini, depending on how it was compiled). You should check that such a file exists in a suitable location, and that it is accessible (particularly if your application runs with different user privileges - such as apache/php running as a www-data user).

Additionally, it could be that iODBC has found a suitable odbc.ini file, but none of the file(s) found contain the DSN you've requested. Check the syntax of your request - is the ODBC connect-string correct, and does the DSN you're requesting exist? See also the section of this FAQ, above, entitled `What's an odbc.ini and what do I put in it?'.

---

What's an odbc.ini

An odbc.ini is the main configuration file in which all your DSNs and much of the ODBC configuration parameters are stored. iODBC has a search-path for finding such a configuration file: first, the environment variable ODBCINI is inspected to see if it points at a suitable file, or ~/.odbc.ini (analagous to User DSNs on Windows) then /etc/odbc.ini ("system-wide") are fall-back locations.

The odbc.ini file comprises 3 parts: a set of ODBC options, a list of DSNs, and then the datasource definitions themselves, thus:

[ODBC Data Sources] 
PostgreSQL native localhost = PostgreSQL native driver 
PostgreSQL OpenLink localhost = PostgreSQL over OpenLink multi-tier

[PostgreSQL native localhost] 
Driver     = /usr/lib/postgresql/lib/libodbcpsql.so 
Host       = localhost 
Server     = localhost 
ServerName = localhost 
Database   = tim 
UserName   = tim 
UID        = tim 
Port       = 5432

[PostgreSQL OpenLink localhost]
Description     = PostgreSQL, over openlink MT 
Driver          = /opt/openlink/lib/oplodbc.so 
ServerType      = PostgreSQL95 
Host            = localhost 
Database        = tim 
Username        = tim 
LastUser        = tim 
User            = tim
FetchBufferSize = 99

[ODBC] 
;Trace = 1 
;TraceFile = /tmp/odbctrace.log ;Debug = 1
;DebugFile = /tmp/odbcdebug.log 

The list of ODBC Data Sources correlate with the file odbcinst.ini (or environment variable ODBCINSTINI): for every value used as a description of the driver there should be a corresponding section in odbcinst.ini listing both the Driver and Setup library (to assist with graphical configuration using iodbcadm-gtk, and also used to display an input box in the event of insufficient options being presented at connection-time).

The list of attributes is driver-dependent, as the above shows: the native postgresql driver calls them different things from openlink multi-tier, so you have to check with your driver's documentation.

With iODBC, assuming you downloaded/installed/compiled it, you also have a GTK-based GUI for configuring your ODBC DSNs, too: run iodbcadm-gtk.