How to connect to SQL Server with windows authentication from Node.JS using mssql module
Solution 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 [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
});
Solution 2
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.
Solution 3
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
});
Solution 4
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);
});
Solution 5
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);
Related videos on Youtube
F0r3v3r-A-N00b
Updated on July 09, 2022Comments
-
F0r3v3r-A-N00b almost 2 years
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();
-
Tuan Anh Tran over 8 yearswhere did you put the username?
-
F0r3v3r-A-N00b over 8 yearsI do not put a user name since my SQL Server's authentication method is Windows Authentication. In .Net this is how I would define my connection string: "Server=localhost;Integrated Security=SSPI;Database=mydatabase". Not sure how to implement something similar to this in Node.js though.
-
F0r3v3r-A-N00b over 8 yearsOh and I tested it with SQL server authentication, using the sa account, and it works. But what I need is for it to work in Windows authentication.
-
-
Steven de Salas almost 8 yearsI've not had much luck with Windows Auth on mssql.
-
Raghavendra over 7 yearsare you providing windows password ? we should not provide windows password for window authentication. it should take automatically which user is running the app..
-
MAFAIZ over 7 yearsnode mssql module dont have the option to login with windows authentication for the sake of the implementation i could find the only above way and for further details refer the git hub issue. github.com/patriksimek/node-mssql/issues/338
-
Holt about 7 yearsI realize that this is an older comment but when I try the above code I am getting "Login failed for user ''." Not sure why it isn't pulling in my windows information.
-
getglad almost 7 years@Holt - I have only ever been able to get it to work using an actual connection string. I have added a code example to my answer, if that helps
-
Chris Peacock almost 7 years@getglad Please could you give a specific example for the connection string with actual values? I'm a little confused as to whether the {} should be there in practice, I'm getting "TypeError: Invalid server: undefined"
-
getglad almost 7 years@ChrisPeacock try replacing
var sql = require('mssql');
withvar sql = require('mssql/msnodesqlv8');
- apparently breaking change in github.com/patriksimek/node-mssql#3x-to-4x-changes -
Chris Peacock almost 7 yearsThanks @getglad! That, along with a change to the 'Driver=...' bit fixed it. I didn't realise that the Driver in the connection string referred to an ODBC driver (I didn't realise ODBC even came into it).
-
PapaDiHatti about 6 years@previousdeveloper did u know now how to get this working in linux
-
Dave almost 6 yearsNice work! Everyone should always use that query when testing connections..... select 1 as number
-
Victorio Berra almost 5 yearsCan we use msnodesqlv8 with knexjs?
-
FabianTe over 4 years@VictorioBerra As stated here our chances to use this with knexjs are pretty slim: stackoverflow.com/a/57397602/3647782 Knexjs mssql dialect uses the tedious driver hardcoded and there is no way to simply change it via a config.
-
volume one over 4 yearsWorks with connectionString. Don't know why it doesn't work when the parameters are supplied separately.
-
Dave almost 4 yearsWas it a " Fork: " error, or a " Shirtballs: " error?
-
Dave almost 4 yearsIf it was a "Fork" error, it will be worth double-checking your connection string again.
-
Ariff Naj about 2 yearsits working,, thank , i just need install and add msnodesqlv8