CODEWITHSUNDEEP
sql-servernode.jsnpm
F0r3v3r-A-N00b
F0r3v3r-A-N00b

Reputation: 3003

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

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();

Upvotes: 55

Views: 120174

Answers (14)

radoczsanyi_
radoczsanyi_

Reputation: 76

None of the answers mentioned, but this config lets you enter Windows credentials.

const sql = require("mssql");

const config = {
  server: "localhost",
  database: "YOUR_DB_NAME",
  options: {
    encrypt: true,
    trustServerCertificate: true,
  },
  authentication: {
    type: "ntlm",
    options: {
      domain: "localhost", // use this or leave blank
      userName: "YOUR_WINDOWS_USERNAME",
      password: "YOUR_WINDOWS_PASSWORD",
    },
  },
};

const connection = sql.connect(config, (error) => {
  if (error) {
    console.log(error);
    return;
  }
  console.info(`connected to ${config.database}`);
});

Upvotes: 0

SEMICS
SEMICS

Reputation: 431

For anyone using ES6 (ECMAScript), I got it working using the following steps.

  1. Download Microsoft ODBC Driver 18 from Microsoft.
  2. Install mssql and msnodesqlv8 (npm install mssql msnodesqlv8)

Then you can use this driver to connect to the sql server

import sql from 'mssql/msnodesqlv8.js';

// Connect to SQL Server using Windows Auth
const conn = new sql.ConnectionPool({
    connectionString: 'Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=master;Trusted_Connection=yes;TrustServerCertificate=yes'
});

conn.connect()
    .then(function () {
      console.log("Connected to SQL Server");
    })
    .catch(function (err) {
      console.log(err);
    }
    );

Upvotes: 5

Zahid Ali
Zahid Ali

Reputation: 81

this worked for me

const sql = require("mssql/msnodesqlv8");

const conn = new sql.ConnectionPool({
    database: "DB name",
    server: "server name",
    driver: "msnodesqlv8",
    options: {
        trustedConnection: true
    }
});
conn.connect().then((err) => {
    if(err) throw err;
    else console.log("connected");
    
    const req = new sql.Request(conn)
    req.query("select * from table", function(error, res){
        console.log(res)
    })
});

Upvotes: 1

Ashwini Jindal
Ashwini Jindal

Reputation: 831

For me

I used connection setting as below

"server":"",
"domain":"", //sepcify domain of your user 
"port": ,
"user":"", // enter username without domain
"password":"",
"database":""

and the TS code

import * as sql from 'mssql';

const pool = await new sql.ConnectionPool(connection).connect();
const result = await pool.request()
            .query(`SELECT count(idpart) part_computed FROM demo.PARTs;`);
pool.close();
return Promise.resolve(result.recordset);

Upvotes: 5

imatwork
imatwork

Reputation: 573

This version doesn't need a username or password.

To use windows authentication I installed mssql and msnodesqlv8.

Then in my app.js file:

const mssql = require('mssql/msnodesqlv8');

Note it is mssql not sql if you're using this example.

var config = {
  database:'YOUR DATABASE NAME',  
  server: 'localhost\\SQLEXPRESS',
  driver: 'msnodesqlv8',
  options: {
    trustedConnection: true,
    enableArithAbort: true
  }
};

You need to change the database name in config. Other than that it should work. My example:

app.get('/', function (req, res) {

        mssql.connect(config, function (err) {
    
            if (err) console.log(err);
            var request = new mssql.Request();
            request.query('select * from dbo.visit', function (err,  result) {
                if(err) console.log(err);
                console.log(result);
            });
    
        });
    
 });

Upvotes: 0

Dave
Dave

Reputation: 3301

I've tried many variations and this is my complete solution.
I'm using SQL server Express.
I'm connecting, in the first instance, to the MASTER database only.
You only NEED to change "YOURINSTANCE\\SQLEXPRESS".
(Be sure to maintain the double-slash above!!!)
I'm using INTEGRATED SECURITY too.
The query relies on nothing at all (in your database).
You need to add your node packages
  ==> NPM INSTALL MSSQL and
  ==> NPM INSTALL msnodesqlv8
Hopefully, your connection issues will be a thing of the past.
Maybe.
Please.

// More here -> https://www.npmjs.com/package/mssql
var sql = require('mssql/msnodesqlv8');
var config = {
  connectionString: 'Driver=SQL Server;Server=YOURINSTANCE\\SQLEXPRESS;Database=master;Trusted_Connection=true;'
};
sql.connect(config, err => {
  new sql.Request().query('SELECT 1 AS justAnumber', (err, result) => {
    console.log(".:The Good Place:.");
    if(err) { // SQL error, but connection OK.
      console.log("  Shirtballs: "+ err);
    } else { // All is rosey in your garden.
      console.dir(result);
    };
  });
});
sql.on('error', err => { // Connection borked.
  console.log(".:The Bad Place:.");
  console.log("  Fork: "+ err);
});

Upvotes: 18

Rajesh Kumar
Rajesh Kumar

Reputation: 622

It worked for me need to install msnodesqlv8 and mssql. also .......:)

    var dbConfig = {
      driver: 'msnodesqlv8',  
      server: "DESKTOP-66LO4I3",
      database: "FutureHealthCareWeb",
      user: "sa",
      password: "pass@123",
      options: {
        trustedConnection: true
    },
    debug: true,
    parseJSON: true
    }; 
    var sql = require('mssql/msnodesqlv8');

      sql.connect(dbConfig, function (err) {
      if (err) { console.log(JSON.stringify(err)+'..............') }
      else {
        console.log('Connected')
      }
    }
    );

Upvotes: 1

vandana agrawal
vandana agrawal

Reputation: 11

Below code is working for me......

const sql = require('mssql/msnodesqlv8')
// config for your database
var config = {
    driver: 'msnodesqlv8',
    server: 'serverNAme\\SQLEXPRESS', 
    database: 'Learn' , 
    options: {
        trustedConnection: true
    }
};

Upvotes: 1

John
John

Reputation: 1247

I could only get a Trusted Connection working using msnodesqlv8 (limited to Windows environments) with a connection string (rather than a config object).

const sql = require("msnodesqlv8");

const connectionString = function(databaseName) {
    return "Server=.;Database=" + databaseName + ";Trusted_Connection=Yes;Driver={SQL Server Native Client 11.0}";
}

sql.query(connectionString("DatabaseName"), "SELECT * FROM dbo.Table1" , (err, recordset) => {
    if(err) {
        // Do something with the err object.
        return;
    }

    // else
    // Do something with the recordset object.
    return;
});

Upvotes: 3

Олег Шиловский
Олег Шиловский

Reputation: 11

I've just add domain: "DNAME", in config, and as result this config helps me connect to MS SQL with windows auth.

const config = {
            driver: 'msnodesqlv8',
            domain: "DNAME",
            user: 'username',
            password: 'pass',
            server: '7.6.225.22',
            database: 'DBNAME',
            requestTimeout: 3600000, //an hour
            options: {
                trustedConnection: true
            },
            debug: true,
            parseJSON: true
        };

Upvotes: 0

Aaron Ballard
Aaron Ballard

Reputation: 601

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 [email protected]. 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
});

Upvotes: 39

getglad
getglad

Reputation: 2562

I have never been able to get mssql + windows auth to work for any of my projects. Try edge and edge-sql - it has worked for me. Be sure you install all the required packages.

https://github.com/tjanczuk/edge

https://github.com/tjanczuk/edge-sql

From there, it's pretty steamlined.

var edge = require('edge');
var params = {
  connectionString: "Server=YourServer;Database=YourDB;Integrated Security=True",
  source: "SELECT TOP 20 * FROM SampleData"
};  
var getData = edge.func( 'sql', params);

getData(null, function (error, result) {
   if (error) { console.log(error); return; }
   if (result) {
    console.log(result);
   }
   else {
    console.log("No results");
   }
 });

EDIT

Well... 10 days after my original answer, apparently mssql added Windows Auth to the package. They heard our cries :) See here. I have not tested it yet, but it is officially in my backlog to test integration. I will report back.

FWTW, if mssql fits your needs, I would go with it, as 1) edge-sql has been dormant for 2 years and 2) the primary contributor has said he has left projects like this "in the caring hands of Microsoft", since he no longer works there.

EDIT 2

This keeps getting upvotes and there are comments saying some of the other answers' code examples either aren't working or aren't working on Windows.

This is my code using mssql, working on Windows, with msnodesqlv8 also installed:

var sql = require('mssql/msnodesqlv8');
var config = {
  driver: 'msnodesqlv8',
  connectionString: 'Driver={SQL Server Native Client XX.0};Server={SERVER\\NAME};Database={dbName};Trusted_Connection={yes};',
};

sql.connect(config)
.then(function() {
 ...profit...
})
.catch(function(err) {
  // ... connect error checks
});

Upvotes: 28

Jon Koala
Jon Koala

Reputation: 498

I have been struggling too for some time about how to use mssql + Windows Auth, here is how i got it to work on my project.

As pointed out in the mssql documentation, you need msnodesqlv8 installed too.

npm install msnodesqlv8

Now, following on Aaron Ballard's answer, you use it like this:

const sql = require('mssql/msnodesqlv8')

const pool = new sql.ConnectionPool({
  database: 'database',
  server: 'server',
  driver: 'msnodesqlv8',
  options: {
    trustedConnection: true
  }
})

pool.connect().then(() => {
  //simple query
  pool.request().query('select 1 as number', (err, result) => {
        console.dir(result)
    })
})

As a note, i tried to add this as a comment on Aaron's answer, as mine is just a complement/update to his, but i don't have enough reputation to do so.

Upvotes: 33

MAFAIZ
MAFAIZ

Reputation: 691

I struggled to connect with mssql server which run in remote windows server using windows authentication mode . Then i found the solution just used like below code. 

sql.connect("Data Source=172.25.x.x,1433;User Id=CSLx\\Name;Password=xxxxxx1234;Initial Catalog=giveTHedataabseNamel;Integrated Security=True",function(err){ }

Upvotes: 0

Related Questions