Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

jTDS issues with NT authentication & dynamic ports

Help
2012-11-09
2012-11-23
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-09

    Hi - using JTDS 1.3.0 / Java 1.7.0_09 (from Sun / Oracle) on Ubuntu 12.04. Not sure what SQL Server version I'm trying to connect to.

    Issue is that I've been provided the following by our IT department:
    - server name is hostname.domain.com\instance
    - database name is db
    - I must use NT authentication
    - the TCP port is selected dynamically on MSSQL startup (it ain't 1433)

    My understanding is that jTDS should be able to handle this scenario, however I'm seeing "java.sql.SQLException: Network error IOException: Permission denied: connect".

    I'm using the following URL to connect:
    jdbc:jtds:sqlserver://hostname.domain.com/db;domain=DOMAIN;user=USER;password=PASSWORD;instance=instance

    If I snoop the network traffic I see a UDP request on port 1434 & a response which lists a bunch of servers & specifics, but nothing beyond that (i.e.: no attempt to make a TCP connection is seen).

    Note that if I re-try the connection & include the TCP port (which is mentioned in the UDP response) the behavior doesn't change - all I see is the UDP to 1434 & then the error.

    Any ideas ?

     
  • momo
    momo
    2012-11-09

    The error is thrown because the driver was unable to establish a network connection to the remote port (this is why you don't see further network traffic). Any chance you are getting blocked by a firewall?

     
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-12

    Hi - unfortunately not. I can telnet to the port without issue.

    Is it possible that the UDP response on port 1434 returns a listing of number of servers (not just one), and as a result jTDS is maybe picking one server @ random ?

     
  • momo
    momo
    2012-11-13

    Hi,

    if I understand you correctly, different instances are running on the machine you are trying to connect. My first question: how many instance are running? In bug #552 a problem has been reported that seems to occur when too many instances are running on a server, but until now I haven't been able to reproduce the problem with 10+ instances running (and that's the maximum my test server is able to handle). And second: could you please try connecting to few of the other instances, just to make sure that also fails.

    Cheers,
    momo

     
    Last edit: momo 2012-11-13
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-14

    Thanks for the follow-up on this.

    For reference here's the UDP data returned when I send the connection request according to wireshark:

    ....ServerName;CPPR01;InstanceName;CPPR01;IsClustered;Yes;Version;10.0.1600.22;tcp;54974;np;\CPPR01\pipe\MSSQL$CPPR01\sql
    \query;;ServerName;BIRPTPR01;InstanceName;BIRPTPR01;IsClustered;Yes;Version;10.0.1600.22;tcp;54974;np;\BIRPTPR01\pipe\MSSQL$BIRPTPR01\sql
    \query;;ServerName;BIRPTPR02;InstanceName;BIRPTPR02;IsClustered;Yes;Version;10.0.1600.22;tcp;54974;np;\BIRPTPR02\pipe\MSSQL$BIRPTPR02\sql
    \query;;ServerName;BIETLPR01;InstanceName;BIETLPR01;IsClustered;Yes;Version;10.0.2531.0;tcp;54974;np;\BIETLPR01\pipe\MSSQL$BIETLPR01\sql
    \query;;ServerName;ITSMPR01;InstanceName;ITSMPR01;IsClustered;Yes;Version;10.0.1600.22;tcp;63716;np;\ITSMPR01\pipe\MSSQL$ITSMPR01\sql\query;;

    ... I can telnet to each server:tcpPort value mentioned above, apart from the very first one. However as mentioned I never see jTDS even attempt the TCP connection, so I'm not sure this is the issue ...

    Anything about the above response look odd / unsupported ?

     
  • momo
    momo
    2012-11-15

    Well, the discovery response packet looks quite normal. At least I cannot identify anything unusual. I'm still not sure what's happening here, but maybe you could try the jTDS version attached and see if it works?

    Cheers,
    momo

     
    Attachments
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-16

    Thanks, this did get us somewhere, but still not exactly where we aught to be.

    The good news is that I see the TCP connection attempt in wireshark. The bad news is that it's to port 1433 (should be 63716, as per the discovery response). Any ideas why ?

     
    Attachments
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-16

    As an aside: the fact that I come to an open-source project with an issue / beg & in less than a week I'm getting attempts at a fix is nothing short of awesome - this really is impressive - thanks :-)

     
  • momo
    momo
    2012-11-16

    First off, thanks! Now please click the 'donate' button and enter any amount that will get me a home somewhere in the Caribbean ;-) Joking aside, I'd really be glad to help, but looks like we are still some steps away from getting things to work.

    But now lets see if we can get this fixed. Could you please post a complete dump of all the network packets sent by the SQL Server as response to the driver's discovery request? A base64-encoded dump would be perfect, but I think any form that isn't garbaged by the sourceforge forum software is okay (if you like, you could also send me an email to ickzon@sourceforge.net and attach a binary dump). I'll then try to emulate this here and hopefully I can reproduce and fix the problem you encounter.

    Cheers,
    momo

     
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-19

    Here's the hex byte dump for the full packet response:

    d4bed9610f4c0021a0786eff0800450002c07f2500007e11f00c0a028e350a0228c2059ad2bd02acf78d05a1025365727665724e616d653b4350505230313b496e7374616e63654e616d653b4350505230313b4973436c757374657265643b5965733b56657273696f6e3b31302e302e313630302e32323b7463703b35343937343b6e703b5c5c4350505230315c706970655c4d5353514c244350505230315c73716c5c71756572793b3b5365727665724e616d653b4249525054505230313b496e7374616e63654e616d653b4249525054505230313b4973436c757374657265643b5965733b56657273696f6e3b31302e302e313630302e32323b7463703b35343937343b6e703b5c5c4249525054505230315c706970655c4d5353514c244249525054505230315c73716c5c71756572793b3b5365727665724e616d653b4249525054505230323b496e7374616e63654e616d653b4249525054505230323b4973436c757374657265643b5965733b56657273696f6e3b31302e302e313630302e32323b7463703b35343937343b6e703b5c5c4249525054505230325c706970655c4d5353514c244249525054505230325c73716c5c71756572793b3b5365727665724e616d653b424945544c505230313b496e7374616e63654e616d653b424945544c505230313b4973436c757374657265643b5965733b56657273696f6e3b31302e302e323533312e303b7463703b35343937343b6e703b5c5c424945544c505230315c706970655c4d5353514c24424945544c505230315c73716c5c71756572793b3b5365727665724e616d653b4954534d505230313b496e7374616e63654e616d653b4954534d505230313b4973436c757374657265643b5965733b56657273696f6e3b31302e302e313630302e32323b7463703b36333731363b6e703b5c5c4954534d505230315c706970655c4d5353514c244954534d505230315c73716c5c71756572793b3b

     
    Last edit: Dave Carpeneto 2012-11-19
  • momo
    momo
    2012-11-19

    Thanks a lot! I'll have a look at this tomorrow.

    Cheers,
    momo

     
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-19

    note that I had to re-pate (1st paste was a subset; what's there now is the full Eth/IP/UDP frame). Lemme know if you'd like it in another format :-)

     
  • momo
    momo
    2012-11-20

    Well, injecting your response into the driver yields the expected result: a connection is tried to be established to port 63716. I just added some debug logging (stdout) to the driver attached. Could you please retry to connect and report what is being logged?

    Cheers,
    momo

     
    Last edit: momo 2012-11-20
    Attachments
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-20

    OK, well fantastic & odd news - the connection seems to work without issue (!!!):

    discovery response: ServerName;CPPR01;InstanceName;CPPR01;IsClustered;Yes;Version;10.0.1600.22;tcp;54974;np;\C
    PPR01\pipe\MSSQL$CPPR01\sql\query;;ServerName;BIRPTPR01;InstanceName;BIRPTPR01;IsClustered;
    Yes;Version;10.0.1600.22;tcp;54974;np;\BIRPTPR01\pipe\MSSQL$BIRPTPR01\sql\query;;ServerNam
    e;BIRPTPR02;InstanceName;BIRPTPR02;IsClustered;Yes;Version;10.0.1600.22;tcp;54974;np;\BIRP
    TPR02\pipe\MSSQL$BIRPTPR02\sql\query;;ServerName;BIETLPR01;InstanceName;BIETLPR01;IsCluster
    ed;Yes;Version;10.0.2531.0;tcp;54974;np;\BIETLPR01\pipe\MSSQL$BIETLPR01\sql\query;;ServerN
    ame;ITSMPR01;InstanceName;ITSMPR01;IsClustered;Yes;Version;10.0.1600.22;tcp;63716;np;\ITSM
    PR01\pipe\MSSQL$ITSMPR01\sql\query;;
    look up port of instance itsmpr01
    discovered port 63716
    connecting using TCP/IP
    host: itsmpr01.#####.net
    port: 63716
    bind address:
    login timeout: 0
    establish connection
    socket connected successfully

    ... and you can see from wireshark that the connection is on the right port :-)

    And everything else works exactly as it aught to beyond the connection :-D

    This really is brilliant - I'm not tied to deploying on windows & using the ODBC connector !!! Thanks soooo much for this.

    Do donations to jTDS route to you directly ? I ain't rich, and this is an unofficial / side project @ work, but this really has bought me a lot, so turn about is fair play :-)

     
    Attachments
  • momo
    momo
    2012-11-20

    Great to hear that! I attached the above driver version just without the debugging code, so you can use that in production until the next jTDS version is released.

    Yes, since I'm the one and only active jTDS developer for quite a while, the donation link routes directly to my paypal account. But please, don't feel in dept to donate. I really was just kidding. Not that I couldn't make good use of a few extra bucks, but indeed you would be the first one ever, donating any money to this project At least since I happened to a accidentally take over the jTDS project a few years ago ;-)

    Cheers,
    momo

     
    Attachments
  • Dave Carpeneto
    Dave Carpeneto
    2012-11-22

    you would be the first one ever, donating any money to this project

    Well there's a first time for everything ;-) I really am just soooo happy that this is an option - I tried to get this to work with PHP, and the official M$SQL JDBC driver (w. Kerberos), but this is the first solution that does both dynamic ports AND NT authentication that I could get to work (outside of ODBC on Windows).

    I can't give too much, but $25 is what I paid for a M$SQL ODBC driver for OSX last year, so I figure that's what your fixes are worth @ a minimum :-)

     
  • momo
    momo
    2012-11-22

    I just received your donation via Paypal :-) Thanks a lot!

    Cheers,
    momo