Knex does not seem to pass authentication options through to tedious

[alex-paterson]

Environment

Knex version: 0.95.6
Database + version: MSSQL AzureDB
OS: MacOS

Bug

I have an application that is using both sequelize and knex to talk to an MSSQL database. I believe both of these libraries are using tedious under the hood and are supposed to pass configuration options through to this underlying library. I am trying to use azure-active-directory-password authentication strategy to connect to my Azure-managed database.

I have no problem connecting using sequelize with the following options:

const sequelize = new Sequelize({
  dialect: 'mssql',
  database: 'example',
  host: 'example.database.windows.net',
  dialectOptions: {
    authentication: {
      type: "azure-active-directory-password",
      options: {
        userName: 'user@ADdomain.com',
        password: PASSWORD,
      },
    },
    server: 'example.database.windows.net',
    options: {
      encrypt: true
    }
  }
})

sequelize.authenticate().then(() => {
  console.log('sqlize authed')
}).catch(err => {
  console.log('sqlize auth fail', err)
})

However I am receiving an error when using what I expect to be an equivalent configuration with knex:

const db = knex({
  client: 'mssql',
  connection: {
    port: 1433,
    database : 'example',
    server : 'example.database.windows.net',
    user : 'user@ADdomain.com',
    password : PASSWORD,
    authentication: {
      type: "azure-active-directory-password",
      options: {
        userName: 'user@ADdomain.com',
        password: PASSWORD,
      }
    },
    options: {
      encrypt: true
    },
  }
})

db('a').insert({a: 'b'}).returning('*').then(() => {
  console.log('Success')
});

The resulting error is:

ConnectionError: Cannot open server "ADdomain.com" requested by the login.  The login failed.

I have tried many variations on the configuration object passed to knex with no success. Does knex not support passing these authentication parameters to tedious?

Thanks

[kibertoad]

@alex-paterson Can you replicate working configuration with tedious without sequelize and paste it here?

See https://github.com/knex/knex/blob/master/lib/dialects/mssql/index.js -> _generateConnection() { for what we are currently passing to Tedious. It could be that something is missing there.

[alex-paterson]

@kibertoad Thanks for the response here. Here's the equivalent functional tedious configuration:

const { Connection } = require('tedious')

const connection = new Connection({
  server: 'example.database.windows.net',

  authentication: {
    type: "azure-active-directory-password",
    options: {
      userName: 'user@ADdomain.com',
      password: PASSWORD,
    },
  },

  options: {
    database: 'example',
    encrypt: true
  }
})

[alex-paterson]

Should note:

    "knex": "^0.95.6",
    "sequelize": "^6.3.4",
    "tedious": "^11.0.3"

For the above tests.

[alex-paterson]

The issue is on this line, where authentication.type in the options passed to tedious is set to settings.type instead of settings.authentication.type:

knex/lib/dialects/mssql/index.js

Line 39 in c335fda

type: settings.type || 'default',

Therefore, I was able to successfully authenticate with the database by passing the undocumented parameter connection.type:

const db = knex({
  client: 'mssql',
  connection: {
    type: "azure-active-directory-password", // undocumented option, indirect mapping to tedious option `authentication.type`

    port: 1433,
    database : 'example',
    server : 'example.database.windows.net',
    user : 'user@ADdomain.com',
    password : PASSWORD,
    authentication: {
      type: "azure-active-directory-password",
      options: {
        userName: 'user@ADdomain.com',
        password: PASSWORD,
      }
    },
    options: {
      encrypt: true
    },
  }
})

db('a').insert({a: 'b'}).returning('*').then(() => {
  console.log('Success')
});

[sevcsik]

I just ran into this issue, with a bit of a twist, because the config object was also mapped from environment variables. It's a tough nut to crack, because the Tedious config object has an authentication object, but Knex does not.

I think renaming the type field to authenticationType would make it less confusing.

[KarateCowboy]

Is this still an issue? I may be able to help.