Requesting tables from MS Sql - Node.js web service

Question

I am in need of assistance in terms of generating a request to my Microsoft SQL Server via a web service (Built in Node.js).

To summarize the problem at hand, it is as follows: I am currently logging into a MS Sql Server Management with a windows auth account - that is all find and dandy however, I am now trying to build a web service that allows for selects and transacts of some tables which is where I am running into issues now specifically in terms of logging in and pulling data to the web service.

Code

var express = require('express'); var app = express();
app.get('/',function(req,res) {
  const sql = require('mssql');
  // Connection Path
  const myServerPath = String("xxxx\\WS1SQLEXPRESS");
  // Connection String Parameter
  const config = {
      // User Login Details - Windows Auth or General User Account
      user    : 'xxxx-xxx\\AdrianH',
      password: 'xxxxxx',
      // Server path to connect to
      server  : myServerPath,
      // Database
      datebase: 'plex',
      options : {
        trustedConnection: true
      }
  };
  sql.connect(config,function(err) {
    if (err) console.log(err);
    // Create Request Object
    var request = new sql.Request();
    // Query the Database
    request.query('USE plex; SELECT * FROM [plex].[dbo].[tblWorkCenters]',function(err,recordset) {
      if (err) console.log(err)
      // send records as response
      res.send(recordset);
    });
  });
});
// Start Server and listen on //http://localhost:8001/
var server  = app.listen(3213,function(){
  console.log('Server is running...');
});

I have hid sensitive information, here is the error code

{ ConnectionError: Login failed for user ''.
    at Connection.tedious.once.err (C:\Users\adrianh\node_modules\mssql\lib\tedious.js:244:17)
    at Object.onceWrapper (events.js:286:20)
    at Connection.emit (events.js:198:13)
    at Connection.processLogin7Response (C:\Users\adrianh\node_modules\tedious\lib\connection.js:1397:14)
    at Connection.message (C:\Users\adrianh\node_modules\tedious\lib\connection.js:1932:14)
    at Connection.dispatchEvent (C:\Users\adrianh\node_modules\tedious\lib\connection.js:1084:36)
    at MessageIO.messageIo.on (C:\Users\adrianh\node_modules\tedious\lib\connection.js:984:14)
    at MessageIO.emit (events.js:198:13)
    at Message.message.on (C:\Users\adrianh\node_modules\tedious\lib\message-io.js:32:14)
    at Message.emit (events.js:203:15)
  code: 'ELOGIN',
  originalError:
   { ConnectionError: Login failed for user ''.
       at ConnectionError (C:\Users\adrianh\node_modules\tedious\lib\errors.js:13:12)
       at Parser.tokenStreamParser.on.token (C:\Users\adrianh\node_modules\tedious\lib\connection.js:735:29)
       at Parser.emit (events.js:198:13)
       at Parser.parser.on.token (C:\Users\adrianh\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
       at Parser.emit (events.js:198:13)
       at addChunk (C:\Users\adrianh\node_modules\readable-stream\lib\_stream_readable.js:297:12)
       at readableAddChunk (C:\Users\adrianh\node_modules\readable-stream\lib\_stream_readable.js:279:11)
       at Parser.Readable.push (C:\Users\adrianh\node_modules\readable-stream\lib\_stream_readable.js:240:10)
       at Parser.Transform.push (C:\Users\adrianh\node_modules\readable-stream\lib\_stream_transform.js:139:32)
       at doneParsing (C:\Users\adrianh\node_modules\tedious\lib\token\stream-parser.js:80:14) message: 'Login failed for user \'\'.', code: 'ELOGIN' },
  name: 'ConnectionError' }
{ ConnectionError: Connection is closed.
    at Request._query (C:\Users\adrianh\node_modules\mssql\lib\base.js:1399:37)
    at Request._query (C:\Users\adrianh\node_modules\mssql\lib\tedious.js:546:11)
    at Request.query (C:\Users\adrianh\node_modules\mssql\lib\base.js:1335:12)
    at C:\Users\adrianh\Desktop\JEC_Current_Projects\WebService\WCWebServiceIOS.js:30:13
    at _poolCreate.then.catch.err (C:\Users\adrianh\node_modules\mssql\lib\base.js:287:7)
    at process._tickCallback (internal/process/next_tick.js:68:7) code: 'ECONNCLOSED', name: 'ConnectionError' }

** An interesting note to make is in --

(C:\Users\adrianh\node_modules\tedious\lib\token\stream-parser.js:80:14) message: 'Login failed for user \'\'.', code: 'ELOGIN' },
      name: 'ConnectionError' }

It doesn't actually seem to pass my login information - any help will be greatly appreciated thank you.

There are two methods of connecting to SQL Server, windows identity and SQL login. When using Windows identity, you cannot specify a username/password, identity of the user running the application is used behind the scenes. To specify username/password you must use (and create) SQL login. More info: learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/… — Alex
– Alex, Commented Jun 26, 2019 at 2:12

Ed Bangga · Accepted Answer · 2019-06-26 05:41:04Z

1

If you are trying to connect mssql via windows authentication in node JS use this module.

var mssql = require('mssql/msnodesqlv8

Sample:

var mssql = require('mssql/msnodesqlv8')

var dbConfig = {    
    server: 'server',
    driver: 'msnodesqlv8',
    database: 'DBDATA', 
    port: '1433',
    options: {
        trustedConnection: true,              
        debug: {
            packet: false,
            payload: false,
            token: false,
            data: false
        },     
    }
};

answered Jun 26, 2019 at 5:41

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Hudson Over a year ago

Thank you very much!! edit I removed the port number as I am connecting to a named instance but still - thank you very much.

Collectives™ on Stack Overflow

Requesting tables from MS Sql - Node.js web service

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related