0

I need help with the right libraries to connect Sequelize to MSSQL database using Windows Authentication.

I have a requirement for a client where I cannot use passwords to connect to the database on the server. Their required method of use is to connect to MSSQL database using Windows Authentication.

The problem I have is that we are using Sequelize and the only Dialect using msnodesqlv8 (which supports Windows Authentication) that I was able to find is not maintained any more. https://www.npmjs.com/package/sequelize-msnodesqlv8

Tedious which is the default dialect for Sequelize does not support Windows Authentication without password. It has the option of using ntlm, but it also requires a password.

5
  • Where are you running this code? If running in a browser then it's going to be very difficult to use SSPI/Kerberos anyway (because the user has not used Kerberos to authenticate). With NodeJS it might be possible at some point. Relevant Tedious issues github.com/tediousjs/tedious/issues/660 and github.com/tediousjs/tedious/pull/624
    – Charlieface
    Commented Jun 30, 2022 at 16:16
  • I am trying do to this on Node.js Server side, so far have not found anything concrete, I have looked at those tickets you pointed out before @Charlieface and also this one github.com/tediousjs/tedious/issues/415 but seems like there isn't a good solution. I'm afraid I can't wait as I only have a couple of weeks at max so need some workaround
    – Hassaan
    Commented Jul 1, 2022 at 5:02
  • after days of searching and trying out different things, I still don't have a good solution, I tried implementing something similar to the posts in issue github.com/tediousjs/tedious/issues/415 and pull github.com/tediousjs/tedious/pull/624 but so far no luck, here is my version of the code github.com/hassaananjum/tedious/tree/… I'm sure I'm missing something, just trying to reach out to anyone who can help
    – Hassaan
    Commented Jul 14, 2022 at 11:32
  • If your client was to move to Azure SQL, you might be able to use AAD authentication (also does not require a password) learn.microsoft.com/en-us/azure/azure-sql/database/…
    – Nick.Mc
    Commented Jul 27, 2022 at 8:36
  • Yeah, unfortunately they are a total on-prem client so no Azure and no future plans for the move either.
    – Hassaan
    Commented Aug 2, 2022 at 14:58

2 Answers 2

Reset to default
1

Update: https://www.npmjs.com/package/msnodesqlv8 supports windows authentication. There are some slight changes in the way they handle some data types like BigInt. Other than that, it works pretty well.

Though, I had already created a custom version of tedious driver with sspi-client https://www.npmjs.com/package/@sregger/sspi-client thanks to some legacy code samples and help from Tediousjs community. So I kept it. One word of caution is that if you are using sspi-client, Worker will not work. To use Worker, use custom library https://www.npmjs.com/package/shinobi-worker otherwise you will get the error of "Module did not self-register"

0

I found the solution with this configuration:

database: 'DB_NAME',
  host: 'DB_HOST',
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
      userName: 'DB_USER',
      password: 'DB_PASS',
      domain: 'MY_COMPANY_DOMAIN',
    },
  },
  options: {
    port: 1433,
    requestTimeout: 60000,
  },
},

moreinfo: https://github.com/vishwasjois/sequelize/blob/988e754c6eef323b1a9dc11f5bee3fb535579da8/docs/upgrade-to-v5.md#dialect-specific

Hope this help

Not the answer you're looking for? Browse other questions tagged or ask your own question.