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', ho

Solution 1:

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 usestedious directly. Despite what the code comment says, msnodesqlv8 is actively maintained and had a release only 4 days ago.

Solution 2:

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');
letKnex = require("knex");

letDialect = 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