freeTDS not using its config

Question

I've decided to use FreeTDS driver and unixODBC to manage the PDO connection between my LAMP-based app with a remote MsSQL database. unfortunately it appears that the driver doesn't read the freetds.conf file, nor the environmental variables set either directly via server's CLI or specified in php file by putenv() function.

now some data:

as I ping the server - no packets are lost.
as I telnet the server on 1433 port - the connection is established
as I use the command
```
TDSVER=7.0 tsql -H >IP< -p 1433 -U username
```
I am prompted to enter password and connection is established.

without the TDSVER preceding the command - the connection fails with such a message:

Error 20017 (severity 9):
    Unexpected EOF from the server
    OS error 115, "Operation now in progress"
Error 20002 (severity 9):
    Adaptive Server connection failed
There was a problem connecting to the server

the tsql -C commands echos such an output:

Compile-time settings (established with the "configure" script)
                       Version: freetds v0.91
        freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: yes
   Sybase binary compatibility: no
                 Thread safety: yes
                 iconv library: yes
                   TDS version: 5.0
                         iODBC: no
                      unixodbc: yes
         SSPI "trusted" logins: no
                      Kerberos: no

freetds.conf in the location given above has this entry:
```
[MSSQL]
host = >IP<
port = 1433
tds version = 7.0
```

the ISQL also fails:

isql -v MSSQL
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

my odbc.ini :

[MSSQL]
Description = MS SQL Server
Driver = FreeTDS
TDS_Version = 7.0
Server = >IP<
UID = username
PWD = password
ReadOnly = No
Port = 1433

I suppose the solution is really simple, but i'm just too stupid to find it...

I found this question via ddg: "freetds linux not working," and my problem was I did not have the port specifically defined. — bgStack15, Aug 25 '16 at 12:26

score 29 · Accepted Answer · answered Apr 25 '13 at 16:09

29

I spent a long time today debugging a similar problem. I had set "TDS version" in freetds.conf but it was not being used in my ODBC connection. After reading the freetds source code (connectparams.c:odbc_parse_connect_string) I discovered that:

If your connection string uses "SERVER=" then both freetds.conf and odbc.ini are ignored
If your connection string uses "SERVERNAME=" then the settings in the appropriate freetds.conf server are used
If your connection string uses "DSN=" then the settings in the appropriate odbc.ini DSN are used

odbcinst.ini is a red herring. FreeTDS never checks that for settings.

The settings you specify in the connection string are always respected. It also always respects the environment variables like TDSVER.

answered Apr 25 '13 at 16:09

Max Bolingbroke

1,999
16
18

2

Thanks for the research: it confirms what I was bashing my head against just now: `DBI:ODBC` not reading `/etc/freetds/freetds.conf` when I'm using the `Server=` parameter in my DSN-less connection string (and `strings /usr/lib/odbc/libtdsodbc.so|grep conf` showing the library knows where to locate the config file. Running `strace ... -e open ...` also confirmed no `freetds.conf` is attempted to be read. – kostix Sep 13 '13 at 10:13
1

And example 4.3 [here](http://freetds.schemamania.org/userguide/dsnless.htm) confirms your research. Thanks again. – kostix Sep 13 '13 at 10:15
If I try to connect via PHP with SERVERNAME=... then I get the error: "SQLSTATE[08001] SQLDriverConnect: 0 [unixODBC][FreeTDS][SQL Server]Unable to connect to data source" It only works with SERVER=... Any idea why? – kinske Dec 02 '13 at 12:32
@kinske I get the same error with `SERVERNAME`, while `SERVER` works. – katalin_2003 Mar 24 '16 at 14:15
1

I am connecting with `HOST=xx.xx.xx.xx` and needed a `putenv("TDSVER=8.0");` in my PHP code before connect. – Chuck Jul 20 '17 at 08:15

score 9 · Answer 2 · answered Oct 25 '12 at 13:35

My gut instinct is you need to change your tds version = 7.0 to tds version = 8.0 in your freetds.conf and odbc.ini files AND you need something in your odbcinst.ini file. Here's what I've got working on an Ubuntu 12.04 server talking to a remote MSSQL server:

freetds.conf

# Define a connection to the MSSQL server.
[mssql]
    host = myserver
    port = 1433
    tds version = 8.0

odbc.ini

# Define a connection to the MSSQL server.
# The Description can be whatever we want it to be.
# The Driver value must match what we have defined in /etc/odbcinst.ini
# The Database name must be the name of the database this connection will connect to.
# The ServerName is the name we defined in /etc/freetds/freetds.conf
# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
[mssql]
Description             = MSSQL Server
Driver                  = freetds
Database                = MyDB
ServerName              = myserver
TDS_Version             = 8.0

odbcinst.ini

# Define where to find the driver for the Free TDS connections.
[freetds]
Description     = MS SQL database access with Free TDS
Driver          = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount      = 1

thanks a lot man, i will try to apply your suggestions as i will be @ work tomorrow — khartvin, Oct 25 '12 at 15:20
Anyone have any ideas where to get MSSQL drivers for Mac OS X for FreeTDS? — Carl, Sep 12 '14 at 07:50

score 5 · Answer 3 · answered Mar 07 '13 at 15:25

5

I experienced this exact same problem, but my config was already correctly set up. The problem was that the TDS versions that freetds.conf recognizes has changed in newer versions, but apparently the old versions still work in the TDSVER environment variable. Once I set the version in the config files to 7.1 rather than 8.0 everything started working.

answered Mar 07 '13 at 15:25

jmsq

51
1
2

WORKED FOR ME! Thanks. After hours of testing and googling I couldn't believe this is all that it took to get the system up and running. – Jim Jun 08 '14 at 12:04
I debugged my setup thru `export TDSDUMP=/temp/freetds.log` in `~/.bashrc`, which made it clear that the tds version was not being set by the config file. Once I set the environment variable `export TDSVER=8.0` everything worked. – DragonSpit Jun 25 '19 at 02:33

score 1 · Answer 4 · edited Sep 26 '13 at 14:09

1

The initial TDSVER=7.0 issue was resolved by adding the following at the end of my odbc.ini file:

[Default]
Driver=/usr/local/lib/libtdsodbc.so

edited Sep 26 '13 at 14:09

Alexander Vogt

17,075
13
45
61

answered Sep 26 '13 at 13:40

SCDomingos

11
1

score 0 · Answer 5 · answered Oct 25 '12 at 10:43

0

Instead of connecting to an IP, try to connect to the name of the configuration section? For example:

isql -v MSSQL

answered Oct 25 '12 at 10:43

Andomar

216,619
41
352
379

Do you also have an `odbc.ini` ? See http://stackoverflow.com/questions/8010361/freetds-tsql-connects-isql-fails – Andomar Oct 25 '12 at 10:56
Try to change `Server = 10.2.201.2` in `odbc.ini` to `Server = MSSQL` ? – Andomar Oct 25 '12 at 11:09
i'm pretty sure the reason for that behaviour is in the fact that FreeTDS doesn't use TDSVER=7.0, which is required for Microsoft's SQL Servers – khartvin Oct 25 '12 at 11:12
Try to change `TDS_Version` to `TDS Version`? Though it should have picked that up from the tds config file – Andomar Oct 25 '12 at 11:14
yes it should, i changed, but the result is the same as before. – khartvin Oct 25 '12 at 11:22

freeTDS not using its config

5 Answers5

Linked