[Question] What is the authentication detail if set trustedConnection to be true?

[Gong-Allen]

After reading some documentation I know I can use below configuration to enable Windows Authentication

var config = {
    driver: "msnodesqlv8",
    server: "localhost",
    database: "MyDatabase",
    options: {
        trustedConnection: true
    },
};

So what is the authentication protocol for this Windows Authentication? Thanks.

[TimelordUK]

i would not use this, simply use a connection string

const mssql= require('mssql/msnodesqlv8')
const {runQuery} = require("./query");
const connectionString  = "Driver={ODBC Driver 17 for SQL Server};Server=(localdb)\\node;Database=scratch;Trusted_Connection=yes;"

async function runner() {
    const res = await runQuery(mssql, connectionString, 'select top 3 * from syscolumns')
    console.log(JSON.stringify(res, null, 4))
}

runner().then(() => {
    console.log('done')
})

query.js

async function runQuery(mssql, connectionString, sqlString) {
    const config = {
        connectionString: connectionString
    }

    const pool = await mssql.connect(config)
    const res = await pool.query(sqlString)
    await pool.close()
    return res
}

exports.runQuery = runQuery

[Gong-Allen]

Hi @TimelordUK ,
Thanks for your response, but I am not asking how to use Windows Authentication, I am asking the protocol of this Windows Authentication, is it Kerberos?

[unsay]

While this is an old issue, it's worth addressing. I've learned a lot about the state of Node and Windows Auth.

Windows Authentication's protocol is not a function of this library. It is, however, lower in the stack with the connection string's driver:

• RECOMMENDED {ODBC Driver 17 for SQL Server}, {ODBC Driver 18 for SQL Server}
• RECOMMENDED OLE DB
• DEPRECATED {SQL Server Native Client} (also known as SQLNCLI and SQLNCLI1)¹

As a function of the drivers, the NTLM protocol is deprecated, Kerberos is the preferred protocol. NTLM will be disabled by default in upcoming Windows Server and client releases.

As of now, SQL Server always prefers Kerberos over NTLM, however, NTLM is used as a fallback by SQL Server to ensure connectivity², though will no longer be the case soon when NTLM is disabled by default.

Footnotes

1. SQL Server Native Client deprecation notice ↩

2. SQL Server NTLM fallback scenarios ↩