tediousjs/node-mssql

Connect to sql server 2000 throws an uncaught exception and is terminated

christiaanwesterbeek opened this issue · 16 comments

christiaanwesterbeek commented

I am on a machine with node-mssql installed but also MSSMS. I can connect to a SQL Server (2000) using MSSMS (Microsoft SQL Server Management Studio), but when I use node-mssql to connect to the same SQL Server I don't get an error, but it doesn't connect either. This is my code:

var sql   = require('mssql');
sql.connect({server: "ip", user: "user", password: "password"}, function(err) {
      console.log('sql.connect error', err);
      if (err) return callback(err);
      var request = new sql.Request();
      request.query(sql, callback);
});

The line console.log is never called and instead the node application throws an uncaught exception and is terminated:

TypeError: Uncaught, unspecified "error" event.
    at TypeError (<anonymous>)
    at Connection.EventEmitter.emit (events.js:74:15)
    at Parser.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:490:15)
    at Parser.EventEmitter.emit (events.js:95:17)
    at Parser.nextToken (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:100:14)
    at Parser.addBuffer (folder\node_modules\mssql\node_modules\tedious\lib\token\token-stream-parser.js:66:17)
    at Connection.sendDataToTokenStreamParser (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:675:35)
    at Connection.STATE.SENT_LOGIN7_WITH_STANDARD_LOGIN.events.data (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:146:23)
    at Connection.dispatchEvent (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:573:59)
    at MessageIO.<anonymous> (folder\node_modules\mssql\node_modules\tedious\lib\connection.js:523:22)

When I connect with node-mssql using an ip address of another machine without mssql I get a nice ETIMEOUT, "Failed to connect to 10.101.0.11:1433 in 15000ms"

PS: I changed a lot of content in this issue since I originally wrote it 20 minutes ago!

christiaanwesterbeek commented

I think it has something to do with the fact that I'm connecting to a SQL 2000 machine. Are the login credentials sent differently over tds for 2000 server?

patriksimek commented

It looks like a Tedious token stream error. There is no tool in node-mssql to debug drivers atm, so please, go to node_modules/node-mssql/lib/tedious.js and add this line of code right after the c = new tds.Connection(cfg); on line 300.

c.on('error', function(err) { console.error(err) });

It should print the error to the console. Please, post it here so I could fix it. Thanks.

christiaanwesterbeek commented

I did exactly what you told me and in the console I now get: "Unrecognized token 0 at offset 134"

patriksimek commented

Thanks, this is what I was afraid of. I need one more kindness from you.

At the same line add:

c.on('debug', function(msg) { console.log(msg) });

And to you connection config add:

var config = {
    options: {
        debug: {
            packet: true,
            data: true,
            payload: true,
            token: true,
            log: true
        }
    }
}

This should produce a large amount of debug information about what's happening. I need this to figure out what is wrong with token stream parser. Thanks again.

christiaanwesterbeek commented

Here's the debug info you requested. I am happy to provide you more information or help you out with more iterations on this:

connected to DW01:1433

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
  0000  00001A00 06010020 00010200 21000103 00220004 04002600 01FF0000 00010001  ....... ....!... ."....&. ........
  0020  02000000 000000  .......
  PreLogin - version:0.0.0.1 1, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin

Received
  type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x0025, spid:0x0000, packetId:0x01, window:0x00
  0000  00001500 0601001B 00010200 1C000103 001D0000 FF080002 FE000002 00  ........ ........ ........ .....
  PreLogin - version:8.0.2.254 0, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(0)

Sent
  type:0x10(LOGIN7), status:0x01(EOM), length:0x00B2, spid:0x0000, packetId:0x01, window:0x00
  0000  AA000000 04000074 00100000 00000000 900D0000 00000000 A0000008 88FFFFFF  .......t........ ........ ........
  0020  09040000 5E000500 68000200 6C000A00 80000700 8E000000 8E000000 8E000700  ....^... h...l... ........ ........
  0040  9C000000 9C000700 01020304 0506AA00 0000AA00 0000AA00 00000000 00007600  ........ ........ ........ ......v.
  0060  73003800 31003400 73006100 86A583A5 53A543A5 86A583A5 E2A533A5 E2A5B2A5  s.8.1.4. s.a..... S.C..... ..3.....
  0080  54006500 64006900 6F007500 73005400 65006400 69006F00 75007300 74006500  T.e.d.i. o.u.s.T. e.d.i.o. u.s.t.e.
  00A0  73007400 5F006700 7400  s.t._.g. t.
  Login7 - TDS:0x74000004, PacketSize:0x00001000, ClientProgVer:0x00000000, ClientPID:0x00000D90, ConnectionID:0x00000000
           Flags1:0xA0, Flags2:0x00, TypeFlags:0x00, Flags3:0x08, ClientTimezone:-120, ClientLCID:0x00000409
           Hostname:'vs814', Username:'my_username', Password:'my_password', AppName:'Tedious', ServerName:'undefined', LibraryName:'Tedious'
           Language:'undefined', Database:'test_gt', SSPI:'', AttachDbFile:'', ChangePassword:''
State change: SentPrelogin -> SentLogin7WithStandardLogin

Received
  type:0x04(TABULAR_RESULT), status:0x01(EOM), length:0x016D, spid:0x003D, packetId:0x01, window:0x00
  0000  E31D0001 07740065 00730074 005F0067 00740006 6D006100 73007400 65007200  .....t.e.s.t._.g.t..m.a. s.t.e.r.
  0020  AB600045 16000002 00260043 00680061 006E0067 00650064 00200064 00610074  .`.E.... .&.C.h.a.n.g.e.d. .d.a.t
  0040  00610062 00610073 00650020 0063006F 006E0074 00650078 00740020 0074006F  .a.b.a.s.e. .c.o.n.t.e.x.t. .t.o
  0060  00200027 00740065 00730074 005F0067 00740027 002E0004 44005700 30003100  . .'.t.e.s.t._.g.t.'.... D.W.0.1.
  0080  000000E3 08000705 0904D000 3400E317 00020A75 0073005F 0065006E 0067006C  ........ ....4... ...u.s._.e.n.g.l
  00A0  00690073 00680000 AB620047 16000001 00270043 00680061 006E0067 00650064  .i.s.h.. .b.G.... .'.C.h.a.n.g.e.d
  00C0  0020006C 0061006E 00670075 00610067 00650020 00730065 00740074 0069006E  . .l.a.n.g.u.a.g.e. .s.e.t.t.i.n
  00E0  00670020 0074006F 00200075 0073005F 0065006E 0067006C 00690073 0068002E  .g. .t.o. .u.s._.e.n.g.l.i.s.h..
  0100  00044400 57003000 31000000 00AD3600 01710000 01164D00 69006300 72006F00  ..D.W.0. 1.....6. .q....M. i.c.r.o.
  0120  73006F00 66007400 20005300 51004C00 20005300 65007200 76006500 72000000  s.o.f.t.  .S.Q.L.  .S.e.r. v.e.r...
  0140  00000800 02F8E313 00040434 00300039 00360004 34003000 39003600 FD000000  ........ ...4.0.9.6..4.0. 9.6.....
  0160  00000000 00  .....
{ name: �[32m'ENVCHANGE'�[39m,
  type: �[32m'DATABASE'�[39m,
  event: �[32m'databaseChange'�[39m,
  oldValue: �[32m'master'�[39m,
  newValue: �[32m'test_gt'�[39m }
{ number: �[33m5701�[39m,
  state: �[33m2�[39m,
  class: �[33m0�[39m,
  message: �[32m'Changed database context to \'test_gt\'.'�[39m,
  serverName: �[32m'DW01'�[39m,
  procName: �[32m''�[39m,
  lineNumber: �[33m149094400�[39m,
  name: �[32m'INFO'�[39m,
  event: �[32m'infoMessage'�[39m }
State change: SentLogin7WithStandardLogin -> Final
connection to DW01:1433 closed
State change: Final -> Final
christiaanwesterbeek commented

PS: let me know if the username of password is there 'hidden' in the stream, so I can change it in my server.

patriksimek commented

Password is somehow encrypted in the stream, but I would recomend you to change the password... Sorry I didn't notice you about that in advance.

christiaanwesterbeek commented

(sorry wrong button)

patriksimek commented

Basicaly all you need to do is to set TDS version in connection config:

var config = {
    options: {
        tdsVersion: '7_1'
    }
}

In detail, strange things happen. The problem is caused by infoMessage token, which is located in the packet before loginack token. There was a breaking change in infoMessage in TDS 7.2 and I don't know which version of TDS to use because this is negotiated later in loginack token.

I will prepare a workaround when I have more time.

christiaanwesterbeek commented

Yes. Adding that option fixed it. Thanks!

Maybe we should add it to the readme.md that this option is needed to use node-mssql with SQL Server version 2000? Because I have used node-mssql from the same machine with version SQL Server version 2008 in my network without a problem.

thabofletcher commented

This came right up when I ran into the same error and googled it, same reason, workaround worked great for me. Thanks!

GreatToad commented

Excuse me.Could I use node-mssql at Access database?If it could,could you give me the demo?

patriksimek commented

Unfortunately you can't.

nyongrand commented

Thanks @patriksimek, you saved my day :)

I was try to connect to SQL Server 2000 too, 3 hours without luck, before I found this page

mythox commented

Thanks @patriksimek, i'm having the same issue until i found this page,

btw, then i have another problem:

message: 'Could not find stored procedure 'sys.sp_helpindex'.',
code: 'EREQUEST',
number: 2812,
state: 62,
class: 16,
serverName: 'dbServerName',
procName: '',
lineNumber: 1,

I can execute exec sp_helpindex, but sys.sp_helpindex from the mssql 2000 query analyzer.

any clue? thankyou.

dsggithub1 commented

yea the option tds saved me, after five hors, thank you very much