MSSQL Windows Authentication Connection in config/database.ts

[celestialvigor]

I'd like to connect to an MSSQL server which possesses a named instance (SQLEXPRESS) through Windows authentication.
The below code in config/database.ts works for the TCP/IP connection.
What would I need to change in order to switch the connection method to Windows authentication?

mssql: {
      client: 'mssql',
      connection: {
        options: {
          enableArithAbort: true,
        },
        user: Env.get('MSSQL_USER'),
        port: parseInt(Env.get('MSSQL_PORT')),
        server: Env.get('MSSQL_SERVER'),
        password: Env.get('MSSQL_PASSWORD', ''),
        database: Env.get('MSSQL_DB_NAME'),
      },
      healthCheck: false,
      debug: false,
    },

[thetutlage]

@celestialvigor Can you share what exactly Windows authentication is or link to some docs?

[guntribam]

Windows authentication means the account resides in Active Directory for the Domain.
SQL Server knows to check AD to see if the account is active, password works, and then checks what level of permissions are granted to the single SQL server instance when using this account.

[anthrogan]

IIRC node-mssql has issues connecting using windows auth. You need to specify a different driver that's able to collect the logged in user details, tedious doesn't appear to do it. Also there some issues around connecting to SQL Server Express with windows auth and node-mssql.

Try something like below in your config, I'm not overly hopeful it will work. Making a login in the engine/schema is the way forward. You'll get nothing but pain using this connection method, not to mention the security implications.

mssql: {
      client: 'mssql',
      connection: {
        options: {
          enableArithAbort: true,
        },
        port: parseInt(Env.get('MSSQL_PORT')),
        server: Env.get('MSSQL_SERVER'),
        database: Env.get('MSSQL_DB_NAME'),
        driver: 'msnodesqlv8',
        trustedConnection: true
      },
      healthCheck: false,
      debug: false,
    },

[guntribam]

This is a know issue in knex 3376

There's a pr that adress the driver that can perform windows authentication, but is frozen 3806

[guntribam]

After some days with this problem i've found the solution. Just add type: "ntlm"

connection: {
        //@ts-ignore
        type: "ntlm",
        server: "<my-server>",
        port: 1433,
        user: "test",
        password: "test",
        domain: "<my-domain>",
        options: {
            trustServerCertificate: true
        }
      },