How to make iSQL/FreeTDS behave like a normal client

freetdsisqlodbcsybaseunixodbc

I have a third-party Sybase database, which my users would like to connect to and query from a Linux box, using iSQL. I have installed UnixODBC and FreeTDS. As it is third-party database however, I am not able to perform any GRANTs. The query fails with iSQL on Linux:

[42501][unixODBC][FreeTDS][SQL Server]ASA Error -121: Permission denied: you do not have permission to use the "CREATE PROCEDURE" statement

It appears that the actual SQL that is getting sent by iSQL is CREATE PROCEDURE <some temporary name> AS <the actual sql>. I have read the iSQL and FreeTDS manpages but there is no hint in there about how to disable this behavior.

This works OK in Python:

>>> import Sybase
>>> db = Sybase.connect('host:port', 'user', 'password', 'db')
>>> c = db.cursor()
>>> c.execute('select table_name from systable')
>>> len(c.fetchall())
570

So I believe I have installed the Sybase client libraries correctly. Before I recommend this solution instead, can I as a DBA do anything about fixing iSQL?

Best Answer

I have seen this before. According to the Sybase ASE Documentation:

Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing Adaptive Server and using dynamic SQL is subject to the restrictions of Adaptive Server stored procedures.

However, I'm not sure if it's the unixODBC interface, or the isql tool, that is asking the ASE to treat these as stored procedures / dynamic sql.

Related Question