How to connect to SQL Server with windows authentication from Node.JS using mssql module

Question

Hi I'm unable to connect to SQL server that is using windows authentication in node js. I'm using the mssql module. The error message is :

[ConnectionError: Login failed for user ''. The user is not associated with a trusted SQL Server connection.]
name: 'ConnectionError',
message: 'Login failed for user ''. The user is not associated with a trusted SQL Server connection.',
code: 'ELOGIN' }

Here is my code:

config = {
    server : "localhost\MSSQLSERVER",
    database : "mydatabase",
    port : 1433
}

function loadDepts() {
    var conn = new sql.Connection(config);
    var request = sql.Request(conn);

    conn.connect(function(err) {
    if (err) {
        console.log(err);
        return;
    }

    request.query("select deptid, deptname from departments", function(err, table) {
        if (err) {
           console.log(err);
           return;
        }
        else {
           console.log(table);
        }

        conn.close();
        });
    });
}

loadDepts();

question from:https://stackoverflow.com/questions/33709807/how-to-connect-to-sql-server-with-windows-authentication-from-node-js-using-mssq

Answer 1

Since this is a fairly visible answer, I wanted to add in a code snippet that worked for me with Trusted Connection. Got to it from getglad's edited answer.

const sql = require("mssql");
require("msnodesqlv8");
const conn = new sql.Connection({
  database: "db_name",
  server: "server_name",
  driver: "msnodesqlv8",
  options: {
    trustedConnection: true
  }
});
conn.connect().then(() => {
  // ... sproc call, error catching, etc
  // example: https://github.com/patriksimek/node-mssql#request
});

Using trusted connection, I was able to execute stored procedures, log the output, and close the connection without any trouble, and msnodesqlv8 has been updated more recently than any of the other drivers (latest release was October 2016 as of 11/3/2016), so that seems to be a safe choice as well.

And here's an example using mssql@4.0.4. The only changes are the initial require, which pull in msnodesqlv8 from within mssql, and sql.Connection is now sql.ConnectionPool. You will also need to change your stored procedure calls since the response is different, noted here. Credit to Jon's answer since he updated mine before I did!

const sql = require("mssql/msnodesqlv8");
const conn = new sql.ConnectionPool({
  database: "db_name",
  server: "server_name",
  driver: "msnodesqlv8",
  options: {
    trustedConnection: true
  }
});
conn.connect().then(() => {
  // ... sproc call, error catching, etc
  // example: https://github.com/patriksimek/node-mssql#request
});