Discussion:
[freetds] unixODBC+freeTDS: DSN-less connections with defaults
O'Brien, Jason K
2012-10-08 16:09:45 UTC
Permalink
We use DSN-less connections, so have unixODBC installed with an odbcinst.ini entry pointing to freeTDS. This works great, except that I can't find a way to set defaults for port or tds version. We have an existing system that runs on windows, and I'd really like to provide connection string compatibility between *NIX and the existing Windows code (which uses the SQL Server ODBC driver, with default port and tds version).
From everything I can find online, it looks like freetds.conf is the place to do this, but setting "tds version = 7.1" and "port = 1433" under [Global] have no effect. I strace'd the process, and it doesn't look like the TDS ODBC driver tries to read freetds.conf at all. I set the default TDS version when configuring, and that took care of that, but there doesn't seem to be an analogous option for port.
Is there any way to set the default port with the freetds odbc driver?

Thanks,
Jason
This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. -IP1
James K. Lowden
2012-10-08 17:25:26 UTC
Permalink
On Mon, 8 Oct 2012 12:09:45 -0400
Post by O'Brien, Jason K
We use DSN-less connections, so have unixODBC installed with an
odbcinst.ini entry pointing to freeTDS. This works great, except that
I can't find a way to set defaults for port or tds version.
...
Post by O'Brien, Jason K
it doesn't
look like the TDS ODBC driver tries to read freetds.conf at all. I
set the default TDS version when configuring, and that took care of
that, but there doesn't seem to be an analogous option for port.
Is there any way to set the default port with the freetds odbc driver?
Congratulations, your assessment is accurate. :-/

Your shortest path from where you sit may be to set TDSPORT in the
environment.

The second option is to connect to a named instance, even the default,
MSSQLSERVER. That should get the port from port 1434 via UDP if your
server is so configured. Or you could include the port in your
connection string.

Another alternative is to patch tds.h with a new value for TDS_DEF_PORT
on line 435 or so. That's used in
src/tds/config.c::tds_read_interfaces().

I don't see much point in creating a configurable default, certainly
not compiled in, and probably not in a config file. A default protocol
makes sense because most sites (I'd say) use either Sybase or
Mircrosoft, not both, and hence save themselves some setup for the
typical connection. OTOH it's relatively rare that all or even
most servers at a site use the same port, unless it's the vendor's
default port.

HTH.

--jkl
Frediano Ziglio
2012-10-08 17:43:49 UTC
Permalink
Post by James K. Lowden
On Mon, 8 Oct 2012 12:09:45 -0400
Post by O'Brien, Jason K
We use DSN-less connections, so have unixODBC installed with an
odbcinst.ini entry pointing to freeTDS. This works great, except that
I can't find a way to set defaults for port or tds version.
...
Post by O'Brien, Jason K
it doesn't
look like the TDS ODBC driver tries to read freetds.conf at all. I
set the default TDS version when configuring, and that took care of
that, but there doesn't seem to be an analogous option for port.
Is there any way to set the default port with the freetds odbc driver?
Congratulations, your assessment is accurate. :-/
Your shortest path from where you sit may be to set TDSPORT in the
environment.
There is also a compatible syntax like Server=dnsname:port or
Server=dnsname,port (yes, ","!)

For tds version you could try to see what happen if you pass
TDS_Version to MS side, if it ignore it you can add without problems.
Post by James K. Lowden
The second option is to connect to a named instance, even the default,
MSSQLSERVER. That should get the port from port 1434 via UDP if your
server is so configured. Or you could include the port in your
connection string.
Another alternative is to patch tds.h with a new value for TDS_DEF_PORT
on line 435 or so. That's used in
src/tds/config.c::tds_read_interfaces().
I don't see much point in creating a configurable default, certainly
not compiled in, and probably not in a config file. A default protocol
makes sense because most sites (I'd say) use either Sybase or
Mircrosoft, not both, and hence save themselves some setup for the
typical connection. OTOH it's relatively rare that all or even
most servers at a site use the same port, unless it's the vendor's
default port.
HTH.
Frediano
O'Brien, Jason K
2012-10-08 18:10:43 UTC
Permalink
Post by James K. Lowden
On Mon, 8 Oct 2012 12:09:45 -0400
...
Post by James K. Lowden
Post by O'Brien, Jason K
Is there any way to set the default port with the freetds odbc driver?
Congratulations, your assessment is accurate. :-/
Your shortest path from where you sit may be to set TDSPORT in the
environment.
The second option is to connect to a named instance, even the
default, MSSQLSERVER. That should get the port from port 1434 via
UDP if your server is so configured. Or you could include the port
in your connection string.
Thanks, I'll look into these. Setting TDSPORT seems very workable.
Post by James K. Lowden
I don't see much point in creating a configurable default, certainly
not compiled in, and probably not in a config file. A default
protocol makes sense because most sites (I'd say) use either Sybase
or Mircrosoft, not both, and hence save themselves some setup for
the typical connection. OTOH it's relatively rare that all or even
most servers at a site use the same port, unless it's the vendor's
default port.
Agreed, I doubt there's many DBAs with a 'favorite port' running around :) However, as you say, it would be nice to be able to set the default protocol and at least the default port for Sybase/MS, if only to match the vendor's ODBC drivers. Nothing show stopping though, this project has saved us a lot of trouble, and even if I end up modifying/duplicating legacy connection strings, I'm certainly grateful.

Is there a distinct reason freetds.conf isn't loaded in this case, or is it just assumed they will also be conf-less?
Post by James K. Lowden
Post by O'Brien, Jason K
This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. -IP1
James K. Lowden
2012-10-09 01:46:53 UTC
Permalink
On Mon, 8 Oct 2012 14:10:43 -0400
Post by O'Brien, Jason K
Is there a distinct reason freetds.conf isn't loaded in this case, or
is it just assumed they will also be conf-less?
The ODBC driver simply never looks at freetds.conf unless referred
there. The idea is that ODBC users, not to mention DMs, use odbc.ini
to hold DSN properities or don't, as in your case, but instead expressly
include those values in the connection string.

Because FreeTDS ODBC users might also use another of the
FreeTDS libraries, the ODBC property "servername" refers the driver to
freetds.conf. That way the server properties can be captured in just
one place.

AFAIK, there's no standard equivalent the freetds.conf [global] section
for odbc.ini. (FreeTDS doesn't define the file format.) That is
perhaps unfortunate, but surely not the only unfortunate thing about
ODBC.

Regards,

--jkl

Loading...