Topic: tsql fails when using FreeTDS alias to connect

[Bozely]

We recently moved our SQL server to which I had been using FreeTDS to connect successfully. Since moving the server to a new host I have been unable to successfully reconnect.

I am able to run the below code and connect to the sql instance with no errors

Code: [Select]

tsql -S [computername] -U sa
but connection fails when I attempt to use the freetds.conf alias ([remoteserver] in my examples lower down)

Code: [Select]

tsql -S [freetdsAlias] -U sa
the error is unhelpful at best

Code: [Select]

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
        Adaptive Server connection failed
There was a problem connecting to the server

My odbcinst.ini file points to the same driver that always worked
I have a custom template for my host file that points to my server and pings are successful

My freetds.conf files is as follows

Code: [Select]

# Microsoft SQL Server 2008R2 on a Server 2008R2 machine
[remoteserver]
host = NewServer01
port = 1433
tds version = 8.0

My odbc.ini file is as follows

Code: [Select]

[remoteserver]
Driver = FreeTDS
Description = Connection to database DBS on server server01
Trace = No
Server = NewServer01
Database = db_55054
Port = 1433
TDS_Version = 8.0

tsql -C outputs

Code: [Select]

[root@rotaserv01 ~]# tsql -C
Compile-time settings (established with the "configure" script)
             Version: freetds v0.91
             freetds.conf directory: /etc
             MS db-lib source compatibility: yes
             Sybase binary compatibility: yes
             Thread safety: yes
             iconv library: yes
             TDS version: 4.2
             iODBC: no
             unixodbc: yes
             SSPI "trusted" logins: no
             Kerberos: no

Googled lots, followed Nick Crittens posts on this forum and read the manual but I'm banging my head against a brick wall here.

https://forums.contribs.org/index.php/topic,30342.15.html
https://wiki.contribs.org/ODBC
http://www.freetds.org/userguide/confirminstall.htm

Any insights in to possible configuration issues would be welcome, thanks.

Bill

[guest22]

Maybe obvious, but does the NewServer01 resolve correctly?

[Bozely]

Yes, I can ping NewServer01, I have a custom template fragment which lists it in the host file.

I am also able to successfully connect using

Code: [Select]

tsql -S NewServer01 -U sa
Connection fails when trying to connect via the ODBC DSN name listed in the freetds.ini and odbc.ini files.

I've played around with instance names and escaping back slashes where they appear but no dice.

[guest22]

http://stackoverflow.com/questions/13066716/freetds-not-using-its-config

Interesting thread...

[Bozely]

With a rested set of eyes on this I came in and removed the underscore from TDS_Version and expanded the templates and it started working immediately...

My odbc.ini now looks like this...

Code: [Select]

[remoteserver]
Driver = FreeTDS
Description = Connection to database DBS on server server01
Trace = No
Server = NewServer01
Database = db_55054
Port = 1433
TDS Version = 8.0

Other useful references from my travels
http://www.freetds.org/userguide/freetdsconf.htm
http://www.unixodbc.org/doc/FreeTDS.html
http://www.unixodbc.org/odbcinst.html

Thanks for the reference RequestedDeletion, I had seen this but had had no joy with the suggestions. I'm unsure how fussy the parser is for the configuration files but this particular configuration seems to work for me.

[mmccarn]

We recently moved our SQL server ...

Is the new server configured to allow remote sql connections?

[Bozely]

Yes, remote connections were even working from the SME, just not using the DSN names specified in the config files, which is what I was using to connect via PHP.

In my case I was simply using the remoteserver DSN specified in the ODBC MSSQL how to.