Support windows authentication for MSSQL Sever

[zeagord]

Is your feature request related to a problem? Please describe.

Current sequelize module doesn't support sql server windows authentication which is preventing the usage in several environments.

Describe the solution you'd like Option to use Windows Authentication in some environments

Why should this be in Sequelize Short explanation why this should be part of Sequelize rather than a separate package.

Describe alternatives you've considered Tried the module sequelize-msnodesqlv8 but it is not maintained anymore or works for me

[papb]

Do you have any idea on how this could be implemented? Pull requests are welcome!

[zeagord]

I honestly don't know how to do it. But can take a look and see how this can be done.

[papb]

Anything that you can find helps! Thanks!

[chupacabra71]

sorry for the noob comment, but is there a way to rely on the tedious connection for this? i can connect to MSSQL using windows authentication with tedious using this connection config

var Connection = require('tedious').Connection
var config = {
  server: 'SERVER',
  authentication: {
    type: 'ntlm',
    options: {
      domain: 'DOMAIN',
      userName: 'USER',
      password: 'PASS'
    }
  },
  options: {
    encrypt: true,
    database: 'DB_NAME'
  }
}
var connection = new Connection(config)

[liamzhang40]

sorry for the noob comment, but is there a way to rely on the tedious connection for this? i can connect to MSSQL using windows authentication with tedious using this connection config

var Connection = require('tedious').Connection
var config = {
  server: 'SERVER',
  authentication: {
    type: 'ntlm',
    options: {
      domain: 'DOMAIN',
      userName: 'USER',
      password: 'PASS'
    }
  },
  options: {
    encrypt: true,
    database: 'DB_NAME'
  }
}
var connection = new Connection(config)

const config = {
  host: process.env.DB_SERVER,
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
        domain: process.env.DB_DOMAIN,
        userName: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
      },
    },
    options: {
      instanceName: process.env.DB_INSTANCE,
    }
  },
};

const sequelize = new Sequelize(
  process.env.DB_NAME,
  null,
  null,
  config,
);

As per well-hidden here, https://github.com/vishwasjois/sequelize/blob/988e754c6eef323b1a9dc11f5bee3fb535579da8/docs/upgrade-to-v5.md#dialect-specific

[chupacabra71]

well-hidden indeed. Thank you!

[papb]

I hoped it wouldn't be this hard to find...

So, happy to know there is a way, I will leave this open as a docs issue so that we show how to do it somewhere. Thanks everyone!

[troyfitzwater]

I'm pretty new to using ORMs and db drivers with node, but would I still need to use a username and password if I'm doing Windows Authentication?

I've been using msnodesqlv8 to connect with Windows Authentication through trusted_connection but I need to use a Linux environment now.

[elierrgm]

@troyfitzwater were you able to find how to use Windows Authentication (or an alternative, I do not think you can use Windows Authentication in linux) through trusted connection (not having to provide the password) in Linux?

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[ghost]

sorry for the noob comment, but is there a way to rely on the tedious connection for this? i can connect to MSSQL using windows authentication with tedious using this connection config

var Connection = require('tedious').Connection
var config = {
  server: 'SERVER',
  authentication: {
    type: 'ntlm',
    options: {
      domain: 'DOMAIN',
      userName: 'USER',
      password: 'PASS'
    }
  },
  options: {
    encrypt: true,
    database: 'DB_NAME'
  }
}
var connection = new Connection(config)

const config = {
  host: process.env.DB_SERVER,
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
        domain: process.env.DB_DOMAIN,
        userName: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
      },
    },
    options: {
      instanceName: process.env.DB_INSTANCE,
    }
  },
};

const sequelize = new Sequelize(
  process.env.DB_NAME,
  null,
  null,
  config,
);

As per well-hidden here, https://github.com/vishwasjois/sequelize/blob/988e754c6eef323b1a9dc11f5bee3fb535579da8/docs/upgrade-to-v5.md#dialect-specific

You are an excellent human. I've been searching for a way to configure this for such a long time. Thank you so much!

[LtFinTutuola]

sorry for the noob comment, but is there a way to rely on the tedious connection for this? i can connect to MSSQL using windows authentication with tedious using this connection config

var Connection = require('tedious').Connection
var config = {
  server: 'SERVER',
  authentication: {
    type: 'ntlm',
    options: {
      domain: 'DOMAIN',
      userName: 'USER',
      password: 'PASS'
    }
  },
  options: {
    encrypt: true,
    database: 'DB_NAME'
  }
}
var connection = new Connection(config)

const config = {
  host: process.env.DB_SERVER,
  dialect: 'mssql',
  dialectOptions: {
    authentication: {
      type: 'ntlm',
      options: {
        domain: process.env.DB_DOMAIN,
        userName: process.env.DB_USERNAME,
        password: process.env.DB_PASSWORD,
      },
    },
    options: {
      instanceName: process.env.DB_INSTANCE,
    }
  },
};

const sequelize = new Sequelize(
  process.env.DB_NAME,
  null,
  null,
  config,
);

As per well-hidden here, https://github.com/vishwasjois/sequelize/blob/988e754c6eef323b1a9dc11f5bee3fb535579da8/docs/upgrade-to-v5.md#dialect-specific

I broke my head on this for half a day... You are a fucking HOLY man, bro!

[ylerjen]

@troyfitzwater were you able to find how to use Windows Authentication (or an alternative, I do not think you can use Windows Authentication in linux) through trusted connection (not having to provide the password) in Linux?

This should be possible by generating a keytab. More info about this is available on MS official documentation: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver16