Windows Authentication requires specifying username/password

[tvrprasad]

Tedious requires me to specify username/password for Windows Authentication even though I'm on the same domain. I don't need to specify username/password in this scenario with ADO.NET. I assume that driver is getting the Kerberos token from Credential store.

Is this capability not currently available with Tedious? Or is there some config that would make Windows Authentication work without having to specify username/password?

@arobson @arthurschreiber - Thoughts please.

[tvrprasad]

I investigated this some. What I've learnt so far is that support for Windows Integrated Authentication uses SSPI and NodeJS currently does not support SSPI. There is a node-sspi npm package but that only has support for server side. Also it only works for a HTTP server. It's a native implementation with JavaScript binding. Client side SSPI implementation would also need to be native code with JavaScript bindings.

My proposal is to build a client side SSPI support in a new npm package with an API that's suitable for consumption by Tedious and have Tedious take a dependency on the package for SQL Server Windows Integrated Auth.

Would appreciate any thoughts. Thanks.

[arthurschreiber]

Yup, that sounds reasonable. I don't think the binary module should be part of tedious, so 👍 on the idea of having it as a separate npm module.

[tvrprasad]

Cool. First cut will likely have support only for Windows. Are we ok with a Windows only feature to start with?

Windows and Linux have different API for supporting Windows Integrated Authentication. We should be able to build support for both platforms into one package. But wondering if there is precedent for dependencies on different packages for Windows vs Linux.

[arthurschreiber]

Cool. First cut will likely have support only for Windows. Are we ok with a Windows only feature to start with?

Yes, that's fine. I imagine this will be an optional feature, so only supporting Windows at first is fine. 👍

[tvrprasad]

I'm sharing a short snippet using the ClientSspi class interface I have in mind. Please share any feedback on the shape of the API.

ClientSspi = require('ClientSspi');

serverName = 'servername.example.com';

// Server name should be the only configuration. Windows SSPI APIs will get
// the tokens to be presented to the server to authenticate the logged in user.
clientSspi = new ClientSspi(serverName);

// authprotocol parameter can take three values.
// Negotiate, Kerberos, Ntlm
// Negotiate: With this option client will negotiate with the server
// on security protocol using SPNEGO.
//
// If nothing is specified, the first supported protocol will be used. The
// protocols will be attempted in the sequence listed above.
clientSspi.initialize(authprotocol, function(errorCode, errorString) {
  if (errorCode || errorString) {
    throw('SSPI intialization failed: ', errorCode, ': ', errorString);
  }

  var sspiServerResponse = new Uint8Array([]);
  var sspiDone = false;

  while (!sspiDone) {
    // This call gets the next set of bytes to send to the server as part of the
    // SSPI dance.
    clientSspi.getNextSspiBlob(sspiServerResponse, function (sspiClientResponse, isDone, errorCode, errorString) {
      if (errorCode || errorString) {
        throw('SSPI intialization failed: ', errorCode, ': ', errorString);
      }

      sspiDone = isDone;

      // This function will send the sspiClientResponse to the server and invokes the
      // callback when the response from the server becomes available.
      SendSspiBlobToSqlServerAndGetResponse(sspiClientResponse, function (serverResponse, errorString) {
        if (errorString) {
          throw(errorString);
        }

        sspiServerResponse = serverResponse;
      });
    });
  }
});

[tvrprasad]

I have a stub implementation of the API at https://github.com/tvrprasad/sspi-client.

@arthurschreiber Please make a quick pass when you get a chance. Send me any feedback or open issues against the repository. Once I fill in the implementation, I plan to use that to implement Windows Integrated Authentication in Tedious.

[tvrprasad]

I threw something together to integrate sspi-client to tedious and see if it works and it does! I was able to connect using ntlm, kerberos and negotiate security packages without specifying password!

@arthurschreiber you can find the hack here. This is not ready for PR by any means, but let me know if you see issues at a high level.
https://github.com/tvrprasad/tedious/tree/windows-integrated-auth-draft

[Suraiya-Hameed]

Reopening this issue to track Windows integrated auth till it's made pluggable, more detail in f5a2260f.

[kevinkuszyk]

@v-suhame what's the latest with this? Are you just waiting on #624?

Is there anything I can do to help get it moving?

[Suraiya-Hameed]

@kevinkuszyk Thanks for the offer to help 😃 Looping in @arthurschreiber to get latest update.

[sxpati2]

Is windows integrated authentication on Linux server available now?

[Suraiya-Hameed]

@sxpati2 Windows integrated authentication, as the name suggest is juts for Windows OS. For Linux it would be Kerberos Integrated auth. Tedious currently doesn't support Windows or Kerberos integrated auth, it is in our future plan.

[jssuttles]

I'd just like to check in on this. It seems like there's been a major refactor. Is there a way that I can just loop msnodesqlv8 into tedious? I thought msnodesqlv8 would just drop into sequelize but it didn't.

[IanChokS]

@arthurschreiber It looks like the PR #497 Windows Integrated Authentication was merged that allows window authentication without needing username/password, but I can't seem to find that anymore in the latest tedious version. Just wondering what happened to that feature?

[rendmath]

Has the situation changed since then ? Or has that feature been temporarily lost in the refactor ?

[heychazza]

Has the situation changed since then ? Or has that feature been temporarily lost in the refactor ?

I'd love to know as well!

[yosiasz]

Greetings, what is the word with this feature? thanks!

[hassaananjum]

Hello, any update on this feature or any workaround I can use, I desperately need this feature.

[MichaelSun90]

Hi @hassaananjum , just did some research on this. The support for windows integrated authentication was added into tedious version 2.1.0. But got removed due to some conflict with Azure support within #635 . Not sure if you want to try an older version of tedious - 2.1.0 that has this feature? This is not ideal but may resolve you need for this feature for now.

[hassaananjum]

Hey guys, I still have no luck solving this issue, really need a solution, based on the discussions above and the code by @tvrprasad and @Suraiya-Hameed I tried to create something in the latest tedious build here https://github.com/hassaananjum/tedious/tree/integrated-auth-with-sspi-client, but it doesn't seem to work, I get the login failed issue. Anyone have any ideas what might be wrong?

Unfortunately, I can't use older versions of tedious library.

[4integration]

Any progress on Integrated Security?

[4integration]

import { Connection, Request, TYPES } from "tedious";

var config = {
  server: "server01-tst.company.net",
  domain: "COMPANY",
  options: {
    database: "my_test",
    port: 50001,
    trustServerCertificate: true,
  },
  authentication: {
    type: "ntlm",
    options: {
      userName: "myuser",
      password: "*****************",
      domain: "COMPANY",
    },
  },
};

console.log("Testing");

var connection = new Connection(config);
connection.on("connect", function (err) {
  // If no error, then good to proceed.
  console.log("Error: " + err);
});

Gives error:

❯ npx ts-node scripts/remove_user_mfa.ts
Testing
Error: Error: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
RequestError: Requests can only be made in the LoggedIn state, not the SentLogin7WithNTLMLogin state
    at Connection.makeRequest (/home/myuser/projects/myctl/node_modules/tedious/src/connection.ts:3104:24)
    at Connection.execSql (/home/myuser/projects/myctl/node_modules/tedious/src/connection.ts:2637:10)
    at selectBuckets (/home/myuser/projects/myctl/scripts/remove_user_mfa.ts:80:14)
    at Connection.<anonymous> (/home/myuser/projects/myctl/scripts/remove_user_mfa.ts:29:3)
    at Connection.emit (node:events:529:35)
    at Connection.emit (node:domain:489:12)
    at Connection.emit (/home/myuser/projects/myctl/node_modules/tedious/src/connection.ts:1902:18)
    at /home/myuser/projects/myctl/node_modules/tedious/src/connection.ts:3474:20
    at processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'EINVALIDSTATE'
}
Error connecting to database: ConnectionError: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
    at Login7TokenHandler.onErrorMessage (/home/myuser/projects/myctl/node_modules/tedious/src/token/handler.ts:268:19)
    at Readable.<anonymous> (/home/myuser/projects/myctl/node_modules/tedious/src/token/token-stream-parser.ts:22:55)
    at Readable.emit (node:events:517:28)
    at Readable.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:335:12)
    at readableAddChunk (node:internal/streams/readable:308:9)
    at Readable.push (node:internal/streams/readable:245:10)
    at next (node:internal/streams/from:98:31)
    at processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ELOGIN'
}
Connection closed