[freetds] Fwd: Named Instances

Patrick Corbett pcorbe81 at maine.edu
Thu Jan 10 13:16:38 EST 2008


FYI:

I was able to solve this issue! It involved something totally unrelated to
FreeTDS (of course)! For those interested, I had to set the following line
in my php.ini file to:

cgi.force_redirect = 0

I'm not really sure why this works, but the ini file requires it for people
using PHP on IIS.

I am still not able to use either php_mssql.dll or php_dblib.dll using PHP
5.2.5, unfortunately. I believe this is an issue not with FreeTDS but with
PHP itself and I have contacted Frank Kromann to see if he has any
suggestions.

Thanks again everyone... hopefully this helps someone else out there.

On 1/9/08, Patrick Corbett <pcorbe81 at maine.edu> wrote:
>
> Correct, tsql is able to connect fine and run a normal query to sql server
> 2000 on my windows machine. My error log looks good as far as the fact that
> it seems to connect all the way through except that I get an "unknown
> error":
>
> =============================
> net.c:185:Connecting to 204.xxx.xx.xx port 1197 (TDS version 8.0)
> net.c:240:connect(2) returned "Unknown error"
> util.c:120:Changing query state from IDLE to DEAD
> net.c:310:tds_open_socket() failed
> token.c:2217:tds_client_msg: #20009: "Server is unavailable or does not
> exist.".  Connection state is now 4.
> dblib.c:1105:dbclose(02047A30)
> dblib.c:253:dblib_del_connection(025048A8, 02048230)
> mem.c :565:tds_free_all_results()
> dblib.c:294:dblib_release_tds_ctx(1)
> dblib.c:5426:dbfreebuf(02047A30)
> dblib.c:438:dbloginfree(020486B8)
> =============================
>
> There seems to be an issue where packets are not being transferred. I'm
> not an expert on how to read these dumps, so maybe you can see something I
> can't. I do notice that there are a lot of "ack" and "syn" but not a lot of
> actual packets being transferred. Here is a copy of what I think to be the
> important part of my packet sniff between my web server ( 204.228.89.44)
> and the sql server (204.228.89.23) (please excuse the long paste):
>
> ===================================
>
> Frame 36 (47 bytes on wire, 47 bytes captured)
>
>     Arrival Time: Jan  9, 2008 12:51:11.782536000
>     Time delta from previous packet: 0.080231000 seconds
>     Time since reference or first frame: 0.934029000 seconds
>     Frame Number: 36
>     Packet Length: 47 bytes
>
>     Capture Length: 47 bytes
> Ethernet II, Src: 00:30:48:5c:f0:78, Dst: 00:0e:0c:4c:42:e8
>     Destination: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>     Source: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Type: IP (0x0800)
>
> Internet Protocol, Src Addr: 204.228.89.44 (204.228.89.44), Dst Addr: 204.228.89.23 (
> 204.228.89.23)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>
>         .... ...0 = ECN-CE: 0
>     Total Length: 33
>     Identification: 0x5124 (20772)
>     Flags: 0x00
>         .0.. = Don't fragment: Not set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>
>     Time to live: 128
>     Protocol: UDP (0x11)
>     Header checksum: 0x0000 (incorrect, should be 0x9d9b)
>     Source: 204.228.89.44 (204.228.89.44
> )
>     Destination: 204.228.89.23 (204.228.89.23)
> User Datagram Protocol, Src Port: 1802 (1802), Dst Port: ms-sql-m (1434)
>     Source port: 1802 (1802)
>
>     Destination port: ms-sql-m (1434)
>     Length: 13
>     Checksum: 0x3d4e (correct)
> Data (5 bytes)
>
> 0000  04 67 65 6e 00                                    .gen.
>
> Frame 37 (153 bytes on wire, 153 bytes captured)
>
>     Arrival Time: Jan  9, 2008 12:51:11.782998000
>     Time delta from previous packet: 0.000462000 seconds
>     Time since reference or first frame: 0.934491000 seconds
>     Frame Number: 37
>     Packet Length: 153 bytes
>
>     Capture Length: 153 bytes
> Ethernet II, Src: 00:0e:0c:4c:42:e8, Dst: 00:30:48:5c:f0:78
>     Destination: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Source: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>     Type: IP (0x0800)
>
> Internet Protocol, Src Addr: 204.228.89.23 (204.228.89.23), Dst Addr: 204.228.89.44 (
> 204.228.89.44)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>
>         .... ...0 = ECN-CE: 0
>     Total Length: 139
>     Identification: 0x16a7 (5799)
>     Flags: 0x00
>         .0.. = Don't fragment: Not set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>
>     Time to live: 128
>     Protocol: UDP (0x11)
>     Header checksum: 0xd7ae (correct)
>     Source: 204.228.89.23 (204.228.89.23)
>     Destination:
> 204.228.89.44 (204.228.89.44)
> User Datagram Protocol, Src Port: ms-sql-m (1434), Dst Port: 1802 (1802)
>     Source port: ms-sql-m (1434)
>     Destination port: 1802 (1802)
>
>     Length: 119
>     Checksum: 0xf440 (correct)
> Data (111 bytes)
>
> 0000  05 6c 00 53 65 72 76 65 72 4e 61 6d 65 3b 42 4f   .l.ServerName;BO
> 0010  52 3b 49 6e 73 74 61 6e 63 65 4e 61 6d 65 3b 67   R;InstanceName;g
>
> 0020  65 6e 3b 49 73 43 6c 75 73 74 65 72 65 64 3b 4e   en;IsClustered;N
> 0030  6f 3b 56 65 72 73 69 6f 6e 3b 38 2e 30 30 2e 31   o;Version;8.00.1
> 0040  39 34 3b 74 63 70 3b 31 31 39 37 3b 6e 70 3b 5c   94;tcp;1197;np;\
>
> 0050  5c 42 4f 52 5c 70 69 70 65 5c 4d 53 53 51 4c 24   \BOR\pipe\MSSQL$
> 0060  47 45 4e 5c 73 71 6c 5c 71 75 65 72 79 3b 3b      GEN\sql\query;;
>
> Frame 38 (62 bytes on wire, 62 bytes captured)
>     Arrival Time: Jan  9, 2008 12:51:
> 11.789292000
>     Time delta from previous packet: 0.006294000 seconds
>     Time since reference or first frame: 0.940785000 seconds
>     Frame Number: 38
>     Packet Length: 62 bytes
>     Capture Length: 62 bytes
>
> Ethernet II, Src: 00:30:48:5c:f0:78, Dst: 00:0e:0c:4c:42:e8
>     Destination: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>     Source: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Type: IP (0x0800)
> Internet Protocol, Src Addr:
> 204.228.89.44 (204.228.89.44), Dst Addr: 204.228.89.23 (204.228.89.23)
>     Version: 4
>
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>
>         .... ...0 = ECN-CE: 0
>     Total Length: 48
>     Identification: 0x5125 (20773)
>     Flags: 0x04
>         .1.. = Don't fragment: Set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>
>     Time to live: 128
>     Protocol: TCP (0x06)
>     Header checksum: 0x0000 (incorrect, should be 0x5d96)
>     Source: 204.228.89.44 (204.228.89.44
> )
>     Destination: 204.228.89.23 (204.228.89.23)
> Transmission Control Protocol, Src Port: 1803 (1803), Dst Port: 1197 (1197), Seq: 3016324069, Ack: 0, Len: 0
>
>     Source port: 1803 (1803)
>     Destination port: 1197 (1197)
>     Sequence number: 3016324069
>     Header length: 28 bytes
>     Flags: 0x0002 (SYN)
>         0... .... = Congestion Window Reduced (CWR): Not set
>
>         .0.. .... = ECN-Echo: Not set
>         ..0. .... = Urgent: Not set
>         ...0 .... = Acknowledgment: Not set
>         .... 0... = Push: Not set
>         .... .0.. = Reset: Not set
>         .... ..1. = Syn: Set
>
>         .... ...0 = Fin: Not set
>     Window size: 65535
>     Checksum: 0x03ac (correct)
>     Options: (8 bytes)
>         Maximum segment size: 1460 bytes
>         NOP
>         NOP
>         SACK permitted
>
>
> Frame 39 (62 bytes on wire, 62 bytes captured)
>     Arrival Time: Jan  9, 2008 12:51:11.789490000
>     Time delta from previous packet: 0.000198000 seconds
>     Time since reference or first frame: 0.940983000
>  seconds
>     Frame Number: 39
>     Packet Length: 62 bytes
>     Capture Length: 62 bytes
> Ethernet II, Src: 00:0e:0c:4c:42:e8, Dst: 00:30:48:5c:f0:78
>     Destination: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Source: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>
>     Type: IP (0x0800)
> Internet Protocol, Src Addr: 204.228.89.23 (204.228.89.23), Dst Addr: 204.228.89.44 (
> 204.228.89.44)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>
>         .... ...0 = ECN-CE: 0
>     Total Length: 48
>     Identification: 0x16a8 (5800)
>     Flags: 0x00
>         .0.. = Don't fragment: Not set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>
>     Time to live: 128
>     Protocol: TCP (0x06)
>     Header checksum: 0xd813 (correct)
>     Source: 204.228.89.23 (204.228.89.23)
>     Destination:
> 204.228.89.44 (204.228.89.44)
> Transmission Control Protocol, Src Port: 1197 (1197), Dst Port: 1803 (1803), Seq: 1517210115, Ack: 3016324070, Len: 0
>
>     Source port: 1197 (1197)
>     Destination port: 1803 (1803)
>     Sequence number: 1517210115
>     Acknowledgement number: 3016324070
>     Header length: 28 bytes
>     Flags: 0x0012 (SYN, ACK)
>         0... .... = Congestion Window Reduced (CWR): Not set
>
>         .0.. .... = ECN-Echo: Not set
>         ..0. .... = Urgent: Not set
>         ...1 .... = Acknowledgment: Set
>         .... 0... = Push: Not set
>         .... .0.. = Reset: Not set
>         .... ..1. = Syn: Set
>
>         .... ...0 = Fin: Not set
>     Window size: 16384
>     Checksum: 0x9f28 (correct)
>     Options: (8 bytes)
>         Maximum segment size: 1460 bytes
>         NOP
>         NOP
>         SACK permitted
>
>
> Frame 40 (54 bytes on wire, 54 bytes captured)
>     Arrival Time: Jan  9, 2008 12:51:11.789499000
>     Time delta from previous packet: 0.000009000 seconds
>     Time since reference or first frame: 0.940992000
>  seconds
>     Frame Number: 40
>     Packet Length: 54 bytes
>     Capture Length: 54 bytes
> Ethernet II, Src: 00:30:48:5c:f0:78, Dst: 00:0e:0c:4c:42:e8
>     Destination: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>     Source: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>
>     Type: IP (0x0800)
> Internet Protocol, Src Addr: 204.228.89.44 (204.228.89.44), Dst Addr: 204.228.89.23 (
> 204.228.89.23)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>
>         .... ...0 = ECN-CE: 0
>     Total Length: 40
>     Identification: 0x5126 (20774)
>     Flags: 0x04
>         .1.. = Don't fragment: Set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>
>     Time to live: 128
>     Protocol: TCP (0x06)
>     Header checksum: 0x0000 (incorrect, should be 0x5d9d)
>     Source: 204.228.89.44 (204.228.89.44
> )
>     Destination: 204.228.89.23 (204.228.89.23)
> Transmission Control Protocol, Src Port: 1803 (1803), Dst Port: 1197 (1197), Seq: 3016324070, Ack: 1517210116, Len: 0
>
>     Source port: 1803 (1803)
>     Destination port: 1197 (1197)
>     Sequence number: 3016324070
>     Acknowledgement number: 1517210116
>     Header length: 20 bytes
>     Flags: 0x0010 (ACK)
>         0... .... = Congestion Window Reduced (CWR): Not set
>
>         .0.. .... = ECN-Echo: Not set
>         ..0. .... = Urgent: Not set
>         ...1 .... = Acknowledgment: Set
>         .... 0... = Push: Not set
>         .... .0.. = Reset: Not set
>         .... ..0. = Syn: Not set
>
>         .... ...0 = Fin: Not set
>     Window size: 65535
>     Checksum: 0x4c27 (incorrect, should be 0x0bed)
>
> Frame 41 (54 bytes on wire, 54 bytes captured)
>     Arrival Time: Jan  9, 2008 12:51:11.791423000
>
>     Time delta from previous packet: 0.001924000 seconds
>     Time since reference or first frame: 0.942916000 seconds
>     Frame Number: 41
>     Packet Length: 54 bytes
>     Capture Length: 54 bytes
> Ethernet II, Src: 00:30:48:5c:f0:78, Dst: 00:0e:0c:4c:42:e8
>
>     Destination: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>     Source: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Type: IP (0x0800)
> Internet Protocol, Src Addr: 204.228.89.44 (
> 204.228.89.44), Dst Addr: 204.228.89.23 (204.228.89.23)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>         .... ..0. = ECN-Capable Transport (ECT): 0
>         .... ...0 = ECN-CE: 0
>     Total Length: 40
>     Identification: 0x5127 (20775)
>
>     Flags: 0x04
>         .1.. = Don't fragment: Set
>         ..0. = More fragments: Not set
>     Fragment offset: 0
>     Time to live: 128
>     Protocol: TCP (0x06)
>     Header checksum: 0x0000 (incorrect, should be 0x5d9c)
>
>     Source: 204.228.89.44 (204.228.89.44)
>     Destination: 204.228.89.23 (204.228.89.23
> )
> Transmission Control Protocol, Src Port: 1803 (1803), Dst Port: 1197 (1197), Seq: 3016324070, Ack: 1517210116, Len: 0
>     Source port: 1803 (1803)
>     Destination port: 1197 (1197)
>     Sequence number: 3016324070
>
>     Acknowledgement number: 1517210116
>     Header length: 20 bytes
>     Flags: 0x0011 (FIN, ACK)
>         0... .... = Congestion Window Reduced (CWR): Not set
>         .0.. .... = ECN-Echo: Not set
>         ..0. .... = Urgent: Not set
>
>         ...1 .... = Acknowledgment: Set
>         .... 0... = Push: Not set
>         .... .0.. = Reset: Not set
>         .... ..0. = Syn: Not set
>         .... ...1 = Fin: Set
>     Window size: 65535
>     Checksum: 0x4c27 (incorrect, should be 0x0bec)
>
>
> Frame 42 (60 bytes on wire, 60 bytes captured)
>     Arrival Time: Jan  9, 2008 12:51:11.791608000
>     Time delta from previous packet: 0.000185000 seconds
>     Time since reference or first frame: 0.943101000
>  seconds
>     Frame Number: 42
>     Packet Length: 60 bytes
>     Capture Length: 60 bytes
> Ethernet II, Src: 00:0e:0c:4c:42:e8, Dst: 00:30:48:5c:f0:78
>     Destination: 00:30:48:5c:f0:78 (Supermic_5c:f0:78)
>     Source: 00:0e:0c:4c:42:e8 (Intel_4c:42:e8)
>
>     Type: IP (0x0800)
>     Trailer: 000000000000
> Internet Protocol, Src Addr: 204.228.89.23 (204.228.89.23), Dst Addr:
> 204.228.89.44 (204.228.89.44)
>     Version: 4
>     Header length: 20 bytes
>     Differentiated Services Field: 0x00 (DSCP 0x00: Default; ECN: 0x00)
>         0000 00.. = Differentiated Services Codepoint: Default (0x00)
>
>         .... ..0. = ECN-Capable Transport (ECT): 0
>         .... ...0 = ECN-CE: 0
>     Total Length: 40
>     Identification: 0x16a9 (5801)
>     Flags: 0x04
>         .1.. = Don't fragment: Set
>         ..0. = More fragments: Not set
>
>     Fragment offset: 0
>     Time to live: 128
>     Protocol: TCP (0x06)
>     Header checksum: 0x981a (correct)
>     Source: 204.228.89.23 (204.228.89.23
> )
>     Destination: 204.228.89.44 (204.228.89.44)
> Transmission Control Protocol, Src Port: 1197 (1197), Dst Port: 1803 (1803), Seq: 1517210116, Ack: 3016324071, Len: 0
>
>     Source port: 1197 (1197)
>     Destination port: 1803 (1803)
>     Sequence number: 1517210116
>     Acknowledgement number: 3016324071
>     Header length: 20 bytes
>     Flags: 0x0010 (ACK)
>         0... .... = Congestion Window Reduced (CWR): Not set
>
>         .0.. .... = ECN-Echo: Not set
>         ..0. .... = Urgent: Not set
>         ...1 .... = Acknowledgment: Set
>         .... 0... = Push: Not set
>         .... .0.. = Reset: Not set
>         .... ..0. = Syn: Not set
>
>         .... ...0 = Fin: Not set
>     Window size: 65535
>     Checksum: 0x0bec (correct)
>
> ==============================
>
> I would agree that there may very well be something wrong with my
> php_dblib.dll. I was using PHP 5.2.1, but can use a different version if
> necessary, although not 5.2.5 because there is a bug in it that prevents
> me from using php_mssql.dll OR php_dblib.dll (see
> http://bugs.php.net/bug.php?id=43645). I noticed that my version of
> php_dblib.dll is 5.2.2.2, so maybe that is part of the issue? Hopefully
> Mr. Kromann has some insight into this issue?
>
> Thanks once again!
>
>
>
>
>
> On 1/9/08, James K. Lowden < jklowden at freetds.org > wrote:
> >
> > > I've done some more sleuthing. I've found that ports 1433 and 1197 are
> >
> > > open, however, 1434 is not. I can telnet into 1197 and 1434 and the IT
> > > department confirmed that as far as they know, everything is open and
> > > ready to go.
> >
> > OK, you can telnet to the port.  And you can tsql to it too, right?
> >
> >         $ tsql -S ...
> >
> > works?
> >
> > Once tsql connects and works normally, you've solved any network and
> > freetds.conf issues.
> >
> > > I am able to connect normally using php_mssql.dll, but php_dblib.dll
> > is
> > > giving me these issues.
> >
> >         [Paging Dr. Kromann...]
> >
> > The fact that one works and the other not -- with the same script? --
> > puzzles me.  It does sound like something about your php_dblib.dll.
> >
> > > Is there something specific I should be looking for in my dump files?
> >
> > You know how to look for the instance port in the instance info packet.
> > You should see the connection being made to that port next, something
> > like:
> >
> > net.c:288:tds_open_socket() succeeded
> > util.c:162:Changed query state from DEAD to IDLE
> > login.c:734:quietly sending TDS 7+ login packet
> > token.c:312:tds_process_login_tokens()
> > net.c:547:Received header
> >
> > Different versions of FreeTDS will have different log files and I don't
> > remember how much detail would be in yours.  The above is from a recent
> > build.
> >
> > Once the connection is accepted, the server will either accept or reject
> >
> > the login.  If you see all that happen with your "good" session and not
> > with your "bad" one, and they're both using the same host and port, then
> > I
> > don't know what to tell you.  :-(
> >
> > HTH.
> >
> > --jkl
> >
> > > On 12/19/07, James K. Lowden <jklowden at freetds.org> wrote:
> > > > Patrick Corbett wrote:
> > > > > net.c:836:instance info
> > > > > 0000 ... |.l.Serve rName;AB|
> > > > > 0010 ... |C;Instan ceName;d|
> > > > > 0020 ... |ef;IsClu stered;N|
> > > > > 0030 ... |o;Versio n;8.00.1|
> > > > > 0040 ... |94;tcp;1 197;np;\|
> > > > > 0050 ... |\ABC\pip e\MSSQL$|
> > > > > 0060 ... |DEF\sql\ query;;|
> > > >
> > > > That is the server's response.  That's good!  It tells us you
> > > > connected to the address, found the rendevous port (1434), and got a
> >
> > > > reply: to connect to the "BOR" instance at port 1197.
> > > >
> > > > Then you couldn't connect, as though no one was listening at 1197.
> > > >
> > > > I suspect a firewall is blocking you.  Perhaps they opened 1434 (and
> >
> > > > maybe 1433, the default), but are blocking most others, including
> > > > 1197.  You could prove it with a tcpdump trace (you can use
> > windump),
> > > > but it would be better if your network folks worked with you to make
> >
> > > > sure your packets are getting through.
> > > >
> > > > In any case, your freetds.conf is now right or almost right.  I
> > don't
> > > > know
> > > >
> > > > if they want you to use the internal or external address, but as
> > long
> > > > as you're getting a packet like the one above, you've described the
> > > > server correctly to FreeTDS.
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS at lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> >
>
>


More information about the FreeTDS mailing list