Connection Issue to Netcool (Sybase) Database

[williampratt]

Hello, I'm at a loss here and hoping you can point me in the right direction. I have a Netcool (Sybase) database that uses port 4100 over tcp and tds version 5.0. I have the connection configured as "PrimaryOS" in /etc/freetds.conf which simply specifies the hostname, port, and tds version as 5.0. Connecting via tsql on the command line is successful and queries work as expected. However, I can't get TinyTds to connect. I have tried supplying the dataserver as 'PrimaryOS' along with the username and password but I get "Adaptive Server is unavailable or does not exist". When I go the :host, :port, and :tds_version route instead, I get "Adaptive Server connection failed" which is different, but still not what I want.

I verified that there are not other freetds.conf files on the filesystem but without any debug info as to why it's not connecting, and the fact that tsql connects fine, I'm at a loss. I'm on Fedora 13 using the distro package supplying version .82. I verified that the development package was installed as well and the TinyTds gem installed with no warnings at all.

Any Ideas? Anything else I could provide that would help? Is there any way to get more debug information out of connection issues?

Thanks in advance,
-Bill

[metaskills]

Hey Bill. It might be a few things. First, I have never tested anything lower than tds version 7. Remember too that FreeTDS compile time default is moot. When we talk about tds version, we should only focus on what is in your freetds.conf file and or what you tell TinyTDS to use.

When you pass connection options to the TinyTds::Client, the :tds_version defaults to 80, it gets confusing if you were to look at the constants in that class and these changed in FreeTDS too. It get's even more complicated when you read their code on compile time tds version vs what a conf can take in 0.82 vs 0.91.rc, I always stick with the default and let the client set things, if you stray from that path, you may have to juggle some things in both your :tds_version connection option (which I have never really tested) and what is in your conf. You could be hitting an odd bug. Test with the tsql utility if you can too since it has a version setter too I think.

The second thing you want to look at is that I think some of the features that we rely on for having no conf file and utilizing :host and :port, rely on the latest 0.91.rc version of FreeTDS. You may want to go with :dataserver only to simplify your debugging.

• HTH
  https://github.com/rails-sqlserver/tiny_tds/blob/master/lib/tiny_tds/client.rb#L54

[williampratt]

Ok, you are correct that the version constant shows weirdness I didn't expect. I can stay with a dataserver defined in freetds.conf, but for some reason I get "Adaptive Server is unavailable or does not exist" when I attempt to use it from TinyTds, but from tsql it works every time. Here is the relevant entry from freetds.conf, pseudo code for TinyTds and the command line that works for tsql. Maybe it will help illustrate my issue. Also, is there a way to get more debug info from connection issues via TinyTds?

freetds.conf:

[PrimaryOS]
host = mnsos1.host.dom
port = 4100
tds version = 5.0

tsql command:

[wpratt@billp ~]$ tsql -U wpratt -P -S PrimaryOS
locale is "en_US"
locale charset is "ISO-8859-1"
1> exit

TinyTds:

irb(main):002:0> TinyTds::Client.new(:dataserver => 'PrimaryOS', :username => 'wpratt', :password => '')
TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist
from /usr/lib/ruby/gems/1.8/gems/tiny_tds-0.4.4/lib/tiny_tds/client.rb:64:in connect' from /usr/lib/ruby/gems/1.8/gems/tiny_tds-0.4.4/lib/tiny_tds/client.rb:64:ininitialize'
from (irb):2:in `new'

[metaskills]

Try adding these to the TinyTds::Client.new to see which one works.

:tds_version => '70'
:tds_version => '42'
:tds_version => '100'
:tds_version => '46'
:tds_version => 'unknown'

[williampratt]

Ok, I turned on debugging for freetds in freetds.conf and I see that when connecting via TinyTds, it's seeing the dataserver, but for some reason it's overriding the port I have specified in freetds.conf and defaulting it back to 1433. I'm not sure why but I'm looking into that now. Here is the info from the dump file in case you are curious:

[wpratt@billp Download]$ cat /tmp/freetds.dump
log.c:190:Starting log file for FreeTDS 0.82
on 2011-05-18 17:07:44 with debug flags 0x4fff.
iconv.c:363:iconv to convert client-side data to the "UTF-8" character set
iconv.c:516:tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
net.c:210:Connecting to 172.16.4.31 port 1433 (TDS version 5.0)
net.c:264:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:299:getsockopt(2) reported: Connection refused
util.c:334:tdserror(0x8ead6d8, 0x8eca9b8, 20009, 115)
dblib.c:7782:dbperror(0x8eca208, 20009, 115)
dblib.c:7835:20009: "Unable to connect: Adaptive Server is unavailable or does not exist"
dblib.c:5627:dbgetuserdata(0x8eca208)
dblib.c:5627:dbgetuserdata(0x8eca208)

[williampratt]

Ok, strangely enough, even with it specified in the conf, the port is ignored and set to 1433 (odd since the default to 5.0 is 5000). If I specify the :port option as 4100 along with the dataserver, it works and I am now connected. It still does not work with raw host , port options.

I think this is a bug, but I haven't really looked that the source yet to see if I can trace it down.

-Bill

[metaskills]

Very odd, perhaps there is a TDSPORT env set? About the only thing I can think of.

[williampratt]

Fyi, TDSPORT is not set:

[wpratt@billp ~]$ echo $TDSPORT

[wpratt@billp ~]$

[williampratt]

Btw, bug or not, I greatly appreciate how rapidly you respond. It's refreshing :)

[metaskills]

Thanks, it is always good to know people are using these tools too! Let me know if you find anything else. One thing I found out recently too is that I had dns entries in my etc/hosts file for names of my dataserver. FreeTDS does all this crazy stuff and I think, if it finds a DNS entry for a name, then it never even get's to a conf file. Hence, why you could have gotten a default port. In that case a :dataserver of "PrimaryOS:4100" may work too.

• Ken

[williampratt]

Since it would be nice to not require users of my tool to have a freetds.conf file, I was playing with the dataserver set to host:port. It's almost working. I'm testing the various tds_version options and when I try '70' it completely crashes irb. I tried wrapping it in a begin / rescue block but no good. It just go boom :(

[williampratt]

I just noticed that the end of the dump file has this line just before it crashes. I didn't notice it because my terminal was scrolled up:

[wpratt@billp Download]$ cat /tmp/freetds.dump
log.c:190:Starting log file for FreeTDS 0.82
on 2011-05-18 17:58:04 with debug flags 0x4fff.
iconv.c:197:names for ISO-8859-1: ISO-8859-1
iconv.c:197:names for UTF-8: UTF-8
iconv.c:197:names for UCS-2LE: UCS-2LE
iconv.c:197:names for UCS-2BE: UCS-2BE
iconv.c:363:iconv to convert client-side data to the "UTF-8" character set
iconv.c:516:tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
net.c:210:Connecting to 172.16.4.31 port 4100 (TDS version 6.0)
net.c:264:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:303:tds_open_socket() succeeded
util.c:162:Changed query state from DEAD to IDLE
login.c:551:Unknown protocol version!

[metaskills]

Use 0.91 (currently in 0.91RC2) if you want to use host/port or dataserver with host name. You can build your own static lib of TinyTDS with FreeTDS/libicon using the Miniportile system, see project README. This process also by default includes 0.91RC2 now too.

As far as connecting to sybase, that should be resolved since I did ticket #30. So please use master branch 0.5.0 release.

[metaskills]

Note, I just saw this come across on the FreeTDS mailing list, is it related?
http://lists.ibiblio.org/pipermail/freetds/2011q4/027476.html

--- dblib.c.ori Thu Aug 18 02:54:40 2011
+++ dblib.c Thu Nov 10 13:45:56 2011
@@ -936,6 +936,9 @@ dbsetlversion (LOGINREC * login, BYTE ve
    case DBVER60:
        login->tds_login->tds_version = 0x700;
        return SUCCEED;
+   case DBVERSION_100:
+       tds_set_version(login->tds_login, 5, 0);
+       return SUCCEED;
    case DBVERSION_71:
        tds_set_version(login->tds_login, 7, 1);
        return SUCCEED;

[williampratt]

It's been a little while since I've looked into this. Let me review this again and I'll let you know. Thanks for the heads up.

-Bill

On Nov 10, 2011, at 6:05 AM, Ken Collins wrote:

Note, I just saw this come across on the FreeTDS mailing list, is it related?
http://lists.ibiblio.org/pipermail/freetds/2011q4/027476.html

--- dblib.c.ori   Thu Aug 18 02:54:40 2011
+++ dblib.c   Thu Nov 10 13:45:56 2011
@@ -936,6 +936,9 @@ dbsetlversion (LOGINREC * login, BYTE ve
  case DBVER60:
      login->tds_login->tds_version = 0x700;
      return SUCCEED;
+ case DBVERSION_100:
+     tds_set_version(login->tds_login, 5, 0);
+     return SUCCEED;
  case DBVERSION_71:
      tds_set_version(login->tds_login, 7, 1);
      return SUCCEED;

---

Reply to this email directly or view it on GitHub:
#22 (comment)

Sincerely,
William Pratt

Mobile: 925.785.1106
AIM: aggriv80r
Yahoo!: wallrat1975
Skype: william.r.pratt
Web: billpratt.net
Photography: flickr.com/williampratt

Photographers deal in things which are continually vanishing and when they have vanished there is no contrivance on earth which can make them come back again.
--Henri Cartier Bresson

[metaskills]

Cool, thanks. I created #62 that talks specifically to this too.