How to setup nextjs and sequelizejs for Microsoft SQL Server with dialectOption Authentication=ActiveDirectoryPassword

Question

I have to setup sequelizejs connection with Microsoft SQL Server. With Dialect option Authentication=ActiveDirectoryPassword

I have tried the following option:

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}'
            },
        // },
    },

But the authentication is still failing with the following error. I tried the same options with Java Spring Boot, and it worked.

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'
  }
}

Answer 1

I figured it out from some of our internal repositories.

const connectionParams = {
    dialect: 'mssql',
    encrypt: true,
    host: 'xxxx-yyyyyy-nonprod-611cccd5.database.windows.net',
    port: 1433,
    userName: '{redacted}',
    password: '{redacted}',
    dialectOptions: {
        encrypt: true,
        packetSize: 32768,
        options: {
            useUTC: true,
            // dateFirst: 1,
            database: '{redacted}',
            requestTimeout: 50000000,
            connectTimeout: 50000000
        },
        authentication: {
            options: {
                userName: '{redacted}',
                password: '{redacted}'
            },
            type: 'azure-active-directory-default'
        },
        server: 'xxxx-yyyyyy-nonprod-611cccd5.database.windows.net',
        requestTimeout: 50000000,
        connectTimeout: 50000000
    },
    pool: {
        max: 1,
        min: 1,
        // acquire: 60000,
        // idle: 30000
    },
    logging: Boolean(true)
};