CODEWITHSUNDEEP
node.jssql-serverconnection-stringwindows-authenticationknex.js
Adrita Sharma
Adrita Sharma

Reputation: 22213

How to connect to SQL Server with Windows authentication from Node.JS using knex module

I am trying to connect SQL Server using knex with Windows Authentication from my node.js application.

Config:

 {
  client: 'mssql',
  connection: {
    database: 'MyDBName',
    host: 'xx.xx.xx.xxx',
    server: 'MY-SERVER_NAME\\SQLEXPRESS',
    options: {
      encrypt: false,
      trustedConnection: true,
    },
  },
}

I didn't add username and password in the config as I have added trustedConnection: true for Windows Authentication.

But I am getting the following error:

Login failed for user ''.

Even if I add add username and password, I get the same error.

Any suggestion will be of great help. Thanks

Upvotes: 0

Views: 9723

Answers (2)

H_R_M
H_R_M

Reputation: 1

I was able to connect using msnodeqlv8 by making a new dialect based on this example

I just added the msnodesqlv8 module and used the following code, hopefully it helps someone out:

require('mssql/msnodesqlv8');
let Knex = require("knex");

let Dialect = require(`knex/lib/dialects/mssql/index.js`);
Dialect.prototype._driver = () => require('mssql/msnodesqlv8');

let sql = Knex({
  client: Dialect,
  connection: {
    server: "<sql server>",
    port: 1433,
    database: "<database name>",
    driver: "msnodesqlv8",
    options: {
        trustedConnection: true
      }
  }
});

Thanks

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131493

knex uses mssql which in turn uses either tedious or msnodesqlv8. tedious doesn't support Windows Authentication. The default is tedious. Trying to use tedious with Windows Authentication results in ... Login failed for user ''.. The full error message is :

(node:16568) UnhandledPromiseRejectionWarning: ConnectionError: Login failed for user ''.
    at Connection.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\lib\tedious.js:244:17)
    at Object.onceWrapper (events.js:291:20)
    at Connection.emit (events.js:203:13)
    at Connection.processLogin7Response (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1397:14)
    at Connection.message (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1932:14)
    at Connection.dispatchEvent (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1084:36)
    at MessageIO.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:984:14)
    at MessageIO.emit (events.js:203:13)
    at Message.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\message-io.js:32:14)
    at Message.emit (events.js:208:15)

Which clearly shows that the source is tedious.

To get this I used this snippet :


const sql = require("mssql");
const config  = {
  database: "Master",
  server: "myserver",
  options: {
    trustedConnection: true
  }
};


(async () => {
        await sql.connect(config)
        const result = await sql.query`select name from sys.databases`
        console.dir(result)
})()

The docs explain that you need to use const sql = require("mssql/msnodesqlv8"); to use msnodesqlv8, eg :

const sql = require("mssql");
const config  = {
  database: "Master",
  server: "myserver",
  options: {
    trustedConnection: true
  }
};

After this change the query runs and produces a list of database names

Unfortunately, this won't help with knex, as it loads and uses tedious directly. Despite what the code comment says, msnodesqlv8 is actively maintained and had a release only 4 days ago.

Upvotes: 5

Related Questions