Why does one ODBC connection work and the second doesn't?

Question

I am using ODBC and FreeTDS on Ubuntu 14.04. We now have three servers that may be running the database at any one time (only one at a time).

I tried adding the second server to my freetds.conf and odbc.ini files, but it will never connect, even when specifying the same IP Address for testing.

ODBC.ini file

[Default]
Driver=NewFreeTDS

[MKMSDef]
Driver=NewFreeTDS
serverName=MKMSDefault

[MKMSSecond]
Driver=NewFreeTDS
serverName=MKMSSecond

[VP]
Driver=NewFreeTDS
ServerName=VirtualPrinter

freetds.conf file

[global]
        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

[MKMSSecond]
        host = 10.1.10.71
        database = MKMSDefault
        port = 2638

[MKMSDefault]
        host = 10.1.10.71
        database = MKMSDefault
        port = 2638

[VirtualPrinter]
        host = 10.1.10.22
        database = VP
        port = 2638

command isql MKMSDef user pass connects successfully command isql MKMSSecond user pass fails

I see nothing different between the two to cause this.

Edit After some further testing. It does not appear that it is reading all of the information to know what to connect to. Whatever is specified as "ServerName" in odbc.ini has to be the database name. It also needs to be defined in freetds.conf, but only the IP and Port are required there.

If I connect to a machine that only has one database running, the servername does not matter.

This does not seem like the correct behavior.

score 0 · Answer 1 · answered Apr 28 '17 at 14:19

As you've partly figured out, it is a lot easier if you keep your naming consistent, simple, and in the same order. Here is an example:

freetds.conf

[bob]
host = bob.myserver.com
port = 1433
tds version = 7.2

[fred]
host = fred.myserver.com
port = 1433
tds version = 7.2

[joe]
host = joe.myserver.com
port = 1433
tds version = 7.2

odbc.ini

[bob]
Driver = FreeTDS
Server =bob.myserver.com
Port = 1433
TDS_Version = 7.2

[fred]
Driver = FreeTDS
Server = fred.myserver.com
Port = 1433
TDS_Version = 7.2

[joe]
Driver = FreeTDS
Server = joe.myserver.com
Port = 1433
TDS_Version = 7.2

If you've cleaned up and are still having issues, please include details from isql -v so we can see what error is being produced.

score 0 · Accepted Answer · answered Jan 31 '19 at 17:43

For this specific project, the problem was because it was an ASA database.

Specifying ASA database = MKMSDefault instead of just database = MKMSDefault solved my problem. I somehow missed the differentiation when reading through the documentation.

I know this is old, and I solved the issue some time ago, but thought it could help someone else.

From the FreeTDS.conf User Guide: (http://www.freetds.org/userguide/freetdsconf.htm)

ASA database - Specifies the name of the default database when connecting to an ASA server. A TDS 5.0 login packet has a field called lservname. For most TDS servers, lservname is a user-defined string with no inherent meaning. ASA servers, however, requires that lservname contain a valid database name, and sets that as the default database for the connection. FreeTDS normally fills lservname with the [section] text.. This entry instead sets the database name independently of the [section] name.

Why does one ODBC connection work and the second doesn't?

2 Answers2