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

Question

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

Vasta

This means the account running the code has no permission to connect. Have you tried connecting to the server using SSMS or Azure Data Studio (which, despite the name, is the cross-platform database client for SQL Server no matter where it runs) – Ladle

I do have the permission. I can access from SQL Server Management Studio using Windows Credential. I think my nodejs application is somehow unable to get the credential – Vasta

BTW ADS is based on VS Code, ie on Electron, ie Node.js. – Ladle

I have no idea about Azure Data Studio – Vasta

Does the code run under your account though, or some other account, eg a service or local account? Does this code run on Windows or Linux? – Ladle

It isn't working for any account. It is a windows system – Vasta

You can use the link to download ADS. In any case, the error means that the client did connect and the server didn't like the account. If knex couldn't get the account's token, there would be a different error. You can check SQL Server's error log from SSMS to see what happened, and what the reason was for the failure. BTW is the exact message Login failed for user '' or did you remove the account name? – Ladle

In the error you can see that Login failed for user ''. It is unable to get the username from my machine – Vasta

There was no account name in the error – Vasta

Are you running using a local account or a domain account? Local accounts are recognizable only on the machine they are created on. When a local account tries to connect to another computer, eg to a file share or SQL Server, the machine's account is used. Local accounts are almost never used to connect from a client to a remote database. – Ladle

Domain Account. We have Windows Authentication in all other applications as well – Vasta

Which packages and versions are you using? Which SQL Server version are you targeting? knex uses mssql which in turn uses either tedious or msnodesqlv8. You could use one of the two drivers directly to see whether they can connect or not. It's one of those that's responsible for actually connecting. This way you can isolate the problem and check whether there's a known bug or an updated version – Ladle

Looks like knex has a hard dependency on tedious which doesn't allow Windows Authentication – Ladle

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

Ladle

Thank you! I have left my laptop in my office... I will try this tomorrow and get back to you. – Vasta

@AdritaSharma unfortunately this won't work with knex because it loads tedious directly The reasoning in the comments no longer holds as msnodesqlv8 is actively maintained – Ladle

Thank You for this detailed explaination. I will try with msnodesqlv8 – Vasta

I used msnodesqlv8 and I am able to connect. Thanks – Vasta

@PanagiotisKanavos Thanks for your answer! But now I am left wondering, is there a working way to get this working with knex? – Selfemployed

Answer 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');
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

Krupp

i get this error from this code TypeError: Driver.Connection is not a constructor – Freud