Koozali.org: home of the SME Server
Obsolete Releases => SME 9.x Contribs => Topic started by: Bozely on January 13, 2017, 02:21:03 PM
-
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
tsql -S [computername] -U sa
but connection fails when I attempt to use the freetds.conf alias ([remoteserver] in my examples lower down)
tsql -S [freetdsAlias] -U sa
the error is unhelpful at best
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
# 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
[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
[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://forums.contribs.org/index.php/topic,30342.15.html)
https://wiki.contribs.org/ODBC (https://wiki.contribs.org/ODBC)
http://www.freetds.org/userguide/confirminstall.htm (http://www.freetds.org/userguide/confirminstall.htm)
Any insights in to possible configuration issues would be welcome, thanks.
Bill
-
Maybe obvious, but does the NewServer01 resolve correctly?
-
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
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.
-
http://stackoverflow.com/questions/13066716/freetds-not-using-its-config (http://stackoverflow.com/questions/13066716/freetds-not-using-its-config)
Interesting thread...
-
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...
[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.freetds.org/userguide/freetdsconf.htm)
http://www.unixodbc.org/doc/FreeTDS.html (http://www.unixodbc.org/doc/FreeTDS.html)
http://www.unixodbc.org/odbcinst.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.
-
We recently moved our SQL server ...
Is the new server configured to allow remote sql connections?
-
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.