ubuntu tsql working and isql not working

Question

I'm trying to connect MSSQL server to ubuntu 14.04 with FreeTDS and unixODBC.

I can connect using tsql with:

~$ tsql -S MSSQL -U <username> -P <password>

This works correctly.

When trying to use isql i get the following

isql -v MSSQL <username> <password>
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

Here are my configs.

odbc.ini

[MSSQL]
Driver = FreeTDS
TDS_Version = 8.0
Server = <server>
Instance = dev
Database = <database>

odbcinst.ini

[FreeTDS]
Description = FreeTDS
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
Trace = Yes
TraceFile = /tmp/freetds.log
ForceTrace = Yes

freetds.conf

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[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]
        host = <host>
        instance = dev
        tds version = 8.0

I think the issue is being caused by the driver, I don't believe i should get an error saying the driver isn't executable. here is the osql output:

osql -S MSSQL -U <username> -P <password>
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 /home/<user>/.odbc.ini
[MSSQL] found in /home/<user>/.odbc.ini
found this section:
        [MSSQL]
        Driver = FreeTDS
        TDS_Version = 8.0
        Server = <server>
        Database = <database>
looking for driver for DSN [MSSQL] in /home/<user>/.odbc.ini
  found driver line: "  Driver = FreeTDS"
  driver "FreeTDS" found for [MSSQL] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /etc/odbcinst.ini
  found driver line: "  Driver = /usr/local/lib/libtdsodbc.so"
  found driver /usr/local/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/usr/local/lib/libtdsodbc.so is an executable file
"Server" found, not using freetds.conf
Server is <server_name>

Configuration looks OK.  Connection details:

                   DSN: MSSQL
              odbc.ini: /home/<user>/.odbc.ini
                Driver: /usr/local/lib/libtdsodbc.so
       Server hostname: <host>
               Address: <ip_address>

Attempting connection as <username> ...
+ isql MSSQL <username> <password> -v
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

Any help is appreciated, Thanks.

Kyle Beaulieu Kyle Beaulieu · Accepted Answer · 2016-09-05T20:14:10

I found the answer to my question. I needed to add the instance to the server in the odbc.ini file like so

MSSQL]
Driver = FreeTDS
TDS_Version = 8.0
Server = <server\dev>
Database = <database>

This let me execute isql.

ubuntu tsql working and isql not working

1 Answers