[freetds] dbi:ODBC:DSN=... odbc.ini UID and PWD ignored ?

[TJ]

On Ubuntu 12.04 and 13.04 I'm seeing a failure to authenticate to an MS SQL Server when UID and PWD are included from the DSN stanza in odbc.ini. When they are added to the connection string the
connection is successful.

Table 4.1 of "Connection attributes" says "The following table defines all possible ODBC connection attributes for the FreeTDS ODBC driver. Which ones you'll need depends on how you set yourself up.
They may appear in your connection string, or in odbc.ini." but my experience doesn't seem to match that.

# apt-cache policy freetds-common tdsodbc unixodbc | grep -B1 Installed
freetds-common:
Installed: 0.91-3
tdsodbc:
Installed: 0.91-3
unixodbc:
Installed: 2.2.14p2-5ubuntu4


I'm using a Perl script for testing which tries these variations of the connection string. The first works, the second fails:

#!/usr/bin/perl

use DBI;

my $DSN = 'Northwind';
my $user = 'INT\TJ';
my $password = 'password';

my $dbh = DBI->connect("dbi:ODBC:DSN=$DSN;UID=$user;PWD=$password", {PrintError => 0});
my $dbh = DBI->connect("dbi:ODBC:DSN=$DSN", {PrintError => 0});
#...

The error is:

$ ./db_test_northwind.pl
DBI connect('DSN=Northwind','HASH(0x1ca7998)',...) failed: [unixODBC][FreeTDS][SQL Server]Login failed for user 'HASH(0x1ca7998)'. (SQL-42000) [state was 42000 now 08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL-08001) at ./db_test_northwind.pl line 10
ERROR: Failed to connect to DSN (Northwind).
ERROR MESSAGE: [unixODBC][FreeTDS][SQL Server]Login failed for user 'HASH(0x1ca7998)'. (SQL-42000) [state was 42000 now 08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL-08001) at ./db_test_northwind.pl line 14.

The system configuration is:

# cat /etc/freetds/freetds.conf
[TJSBS2003]
host = 10.254.1.253
port = 1433

# cat /etc/odbcinst.ini
[FreeTDS]
Description = MS SQL Server via Free TDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 2

# cat /etc/odbc.ini
[Northwind]
Description = Test on MS SQL Server 2000 SP3 on SBS2003
Driver = FreeTDS
TDS_Version = 8.0
Servername = TJSBS2003
Database = Northwind
UID = INT\TJ
PWD = password

[Frediano Ziglio]

2013/4/19 TJ <freetds at iam.tj>

Post by TJ
On Ubuntu 12.04 and 13.04 I'm seeing a failure to authenticate to an MS
SQL Server when UID and PWD are included from the DSN stanza in odbc.ini.
When they are added to the connection string the
connection is successful.
Table 4.1 of "Connection attributes" says "The following table defines all
possible ODBC connection attributes for the FreeTDS ODBC driver. Which ones
you'll need depends on how you set yourself up.

At http://www.freetds.org/userguide/odbcconnattr.htm 4.1 table state
exactly that UID/PWD are not used from odbc.ini (only connection string).
This for security reasons. If your documentation state the opposite
probably was wrong (we fixed documentation some time ago).

Use 7.1 as protocol version (you can also use 7.2), 8.0 is the former
version.

Also pass our driver even as setup, our driver has support for unixODBC and
procide functions to deal with setup stuff.

Regards,
Frediano

Post by TJ
They may appear in your connection string, or in odbc.ini." but my
experience doesn't seem to match that.
# apt-cache policy freetds-common tdsodbc unixodbc | grep -B1 Installed
Installed: 0.91-3
Installed: 0.91-3
Installed: 2.2.14p2-5ubuntu4
I'm using a Perl script for testing which tries these variations of the
#!/usr/bin/perl
use DBI;
my $DSN = 'Northwind';
my $user = 'INT\TJ';
my $password = 'password';
my $dbh = DBI->connect("dbi:ODBC:DSN=$DSN;UID=$user;PWD=$password", {PrintError => 0});
my $dbh = DBI->connect("dbi:ODBC:DSN=$DSN", {PrintError => 0});
#...
$ ./db_test_northwind.pl
[unixODBC][FreeTDS][SQL Server]Login failed for user 'HASH(0x1ca7998)'.
(SQL-42000) [state was 42000 now 08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source
(SQL-08001) at ./db_test_northwind.pl line 10
ERROR: Failed to connect to DSN (Northwind).
ERROR MESSAGE: [unixODBC][FreeTDS][SQL Server]Login failed for user
'HASH(0x1ca7998)'. (SQL-42000) [state was 42000 now 08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source
(SQL-08001) at ./db_test_northwind.pl line 14.
# cat /etc/freetds/freetds.conf
[TJSBS2003]
host = 10.254.1.253
port = 1433
# cat /etc/odbcinst.ini
[FreeTDS]
Description = MS SQL Server via Free TDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount = 2
# cat /etc/odbc.ini
[Northwind]
Description = Test on MS SQL Server 2000 SP3 on SBS2003
Driver = FreeTDS
TDS_Version = 8.0
Servername = TJSBS2003
Database = Northwind
UID = INT\TJ
PWD = password

[TJ]

Post by Frediano Ziglio
2013/4/19 TJ <freetds at iam.tj>

Post by TJ
On Ubuntu 12.04 and 13.04 I'm seeing a failure to authenticate to an MS
SQL Server when UID and PWD are included from the DSN stanza in odbc.ini.
When they are added to the connection string the
connection is successful.
Table 4.1 of "Connection attributes" says "The following table defines all
possible ODBC connection attributes for the FreeTDS ODBC driver. Which ones
you'll need depends on how you set yourself up.

At http://www.freetds.org/userguide/odbcconnattr.htm 4.1 table state
exactly that UID/PWD are not used from odbc.ini (only connection string).

At http://www.freetds.org/userguide/odbcconnattr.htm

it says: "The following table defines all possible ODBC connection attributes for the FreeTDS ODBC driver. Which ones you'll need depends on how you set yourself up. They may appear in your connection
string, or in odbc.ini."

The "following table" is Table 4-1, which lists DSN, UID, PWD and WSID.

I suggest that preamble is re-written thus:

"The following tables define all possible ODBC connection attributes for the FreeTDS ODBC driver. Table 4-1 names (keys) may only be used in the connection string (not odbc.ini). Table 4-2 names may
be used in both the connection string and odbc.ini."

What is the recommended solution for system DSNs where policy dictates that application code should not have access to the username and password of the connection?

[James K. Lowden]

On Fri, 19 Apr 2013 17:40:19 +0100

Post by Frediano Ziglio
At http://www.freetds.org/userguide/odbcconnattr.htm
it says: "The following table defines all possible ODBC connection
attributes for the FreeTDS ODBC driver. Which ones you'll need
depends on how you set yourself up. They may appear in your
connection string, or in odbc.ini."
The "following table" is Table 4-1, which lists DSN, UID, PWD and WSID.

Thanks for pointing that out. Upon a time there was only one table.
When I split in two, I thought it was much clearer. Which it was,
until someone looked at it with fresh eyes.
I don't think restating the table titles in the paragraph aids
clarity. I made it "tables define". Have a look and see if you don't
agree.

Post by Frediano Ziglio
What is the recommended solution for system DSNs where policy
dictates that application code should not have access to the username
and password of the connection?

Use Kerberos or Active Directory.

In the days before FreeTDS supported them, we kept passwords in
another database that only the application process could use. If I
couldn't do that, either, another option might be to keep the passwords
in a file on another machine, and use ssh to fetch it.

HTH.

--jkl

P.S. Nice email address you got there!

[TJ]

Post by James K. Lowden
I don't think restating the table titles in the paragraph aids
clarity. I made it "tables define". Have a look and see if you don't
agree.

The confusion is in the later part of the paragraph and, for me as a novice FreeTDS user, gives rise to the same original confusion.

"They may appear in your connection string, or in odbc.ini."

It is the conjunction using "or" that causes the misunderstanding for me. It could be that dropping that entire sentence is the solution since the table titles then become the only source of key
location information.