配置Next.js+Sequelize连接SQL Server（AD密码认证）遇登录失败

问题背景 #

需要配置Sequelize.js与Microsoft SQL Server的连接，使用ActiveDirectoryPassword认证方式，但当前配置认证失败，相同认证信息在Java Spring Boot中可正常工作。

尝试的配置 #

host: '{redacted}.database.windows.net',
dialect: 'mssql',
port: 1433,
dialectOptions: {
    // encrypt: true, // Enable if your server requires SSL
    // trustServerCertificate: true,
    // Authentication: 
    // {
        options: {
            encrypt: true,
            trustServerCertificate: true,
            enableArithAbort: true,
            packetSize: 4096,
            authentication: 'ActiveDirectoryPassword',
            // userName: '{redacted}@svc.xxx.com',
            // password: '{redacted}'
        },
    // },
},

报错信息 #

Unable to connect to the database: AccessDeniedError [SequelizeAccessDeniedError]: Cannot open server "svc.xxx.com" requested by the login.  The login failed.
    at ConnectionManager.connect (/Users/xxx/react/adminportaldemo/node_modules/sequelize/lib/dialects/mssql/connection-manager.js:115:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async ConnectionManager._connect (/Users/xxxx/react/adminportaldemo/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:222:24)
    at async /Users/xxx/react/adminportaldemo/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:174:32
    at async ConnectionManager.getConnection (/Users/xxxx/react/adminportaldemo/node_modules/sequelize/lib/dialects/abstract/connection-manager.js:197:7)
    at async /Users/xxx/react/adminportaldemo/node_modules/sequelize/lib/sequelize.js:305:26
    at async Sequelize.authenticate (/Users/xxx/react/adminportaldemo/node_modules/sequelize/lib/sequelize.js:457:5)
    at async testConnection (/Users/xxx/react/adminportaldemo/.next/server/pages/api/hello.js:1:1248) {
  parent: ConnectionError: Cannot open server "svc.xxxx.com" requested by the login.  The login failed.
      at Login7TokenHandler.onErrorMessage (/Users/xxx/react/adminportaldemo/node_modules/tedious/lib/token/handler.js:186:19)
      at Readable.<anonymous> (/Users/xxx/react/adminportaldemo/node_modules/tedious/lib/token/token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22)
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    code: 'ELOGIN'
  },
  original: ConnectionError: Cannot open server "svc.xxx.com" requested by the login.  The login failed.
      at Login7TokenHandler.onErrorMessage (/Users/xxx/react/adminportaldemo/node_modules/tedious/lib/token/handler.js:186:19)
      at Readable.<anonymous> (/Users/xxx/react/adminportaldemo/node_modules/tedious/lib/token/token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22)
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    code: 'ELOGIN'
  }
}

问题分析 #

当前配置存在两个核心问题：

1. authentication 参数层级错误：Sequelize依赖的tedious库要求认证配置放在dialectOptions.authentication下，而非直接嵌套在options中
2. 关键认证信息缺失：注释掉了用户名和密码，导致无法完成身份校验

正确配置示例 #

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize({
  host: '{redacted}.database.windows.net',
  dialect: 'mssql',
  port: 1433,
  // 方案1：顶层配置用户名密码
  username: '{redacted}@svc.xxx.com',
  password: '{redacted}',
  dialectOptions: {
    encrypt: true,
    trustServerCertificate: true,
    enableArithAbort: true,
    packetSize: 4096,
    // 正确的认证配置层级
    authentication: {
      type: 'ActiveDirectoryPassword',
      // 方案2：在此处配置用户名密码（二选一即可）
      // options: {
      //   userName: '{redacted}@svc.xxx.com',
      //   password: '{redacted}'
      // }
    }
  }
});

额外注意事项 #

• 确保安装了最新版的sequelize和tedious依赖，旧版本可能存在Active Directory认证的兼容性问题
• 若使用Azure SQL数据库，encrypt参数必须设为true，否则会连接失败
• 用户名格式需符合AD域要求（如user@domain.com）

内容的提问来源于stack exchange，提问作者Prakash Barri