如何使用knex模块从Node.JS通过Windows身份验证连接到SQL Server [英] How to connect to SQL Server with Windows authentication from Node.JS using knex module

问题描述

我正在尝试使用 knex 和来自我的node.js应用程序的Windows身份验证连接SQL Server.

I am trying to connect SQL Server using knex with Windows Authentication from my node.js application.

配置:

 {
  client: 'mssql',
  connection: {
    database: 'MyDBName',
    host: 'xx.xx.xx.xxx',
    server: 'MY-SERVER_NAME\\SQLEXPRESS',
    options: {
      encrypt: false,
      trustedConnection: true,
    },
  },
}

我没有在配置中添加用户名和密码，因为我为 Windows身份验证添加了trustedConnection: true.

I didn't add username and password in the config as I have added trustedConnection: true for Windows Authentication.

但是我遇到以下错误:

用户"登录失败.

Login failed for user ''.

即使添加添加用户名和密码，我也会遇到相同的错误.

Even if I add add username and password, I get the same error.

任何建议都会有很大帮助.谢谢

Any suggestion will be of great help. Thanks

推荐答案

knex使用mssql，而mssql依次使用tedious或msnodesqlv8. tedious不支持Windows身份验证.默认值为tedious.尝试将tedious与Windows身份验证一起使用会导致... Login failed for user ''..完整的错误消息是:

knex uses mssql which in turn uses either tedious or msnodesqlv8. tedious doesn't support Windows Authentication. The default is tedious. Trying to use tedious with Windows Authentication results in ... Login failed for user ''.. The full error message is :

(node:16568) UnhandledPromiseRejectionWarning: ConnectionError: Login failed for user ''.
    at Connection.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\lib\tedious.js:244:17)
    at Object.onceWrapper (events.js:291:20)
    at Connection.emit (events.js:203:13)
    at Connection.processLogin7Response (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1397:14)
    at Connection.message (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1932:14)
    at Connection.dispatchEvent (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:1084:36)
    at MessageIO.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\connection.js:984:14)
    at MessageIO.emit (events.js:203:13)
    at Message.<anonymous> (K:\testprojects\nodesql\node_modules\mssql\node_modules\tedious\lib\message-io.js:32:14)
    at Message.emit (events.js:208:15)

清楚地表明来源是tedious.

要得到这个，我使用了以下代码段:

To get this I used this snippet :

const sql = require("mssql");
const config  = {
  database: "Master",
  server: "myserver",
  options: {
    trustedConnection: true
  }
};


(async () => {
        await sql.connect(config)
        const result = await sql.query`select name from sys.databases`
        console.dir(result)
})()

docs解释，您需要使用const sql = require("mssql/msnodesqlv8");才能使用msnodesqlv8，例如:

The docs explain that you need to use const sql = require("mssql/msnodesqlv8"); to use msnodesqlv8, eg :

const sql = require("mssql");
const config  = {
  database: "Master",
  server: "myserver",
  options: {
    trustedConnection: true
  }
};

此更改后，查询将运行并生成数据库名称列表

After this change the query runs and produces a list of database names

不幸的是，这对knex没有帮助，因为它直接加载并使用 tedious.尽管代码注释说明了什么，但 msnodesqlv8 仍处于积极维护状态，并且仅发布了4天以前.

Unfortunately, this won't help with knex, as it loads and uses tedious directly. Despite what the code comment says, msnodesqlv8 is actively maintained and had a release only 4 days ago.

这篇关于如何使用knex模块从Node.JS通过Windows身份验证连接到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！