How can I use Microsoft Entra-only authentication for my nextjs application using mssql and Azure SQL Database

Question

I'm building a fullstack application using NextJs and Azure SQL database. The database is Microsoft Entra-only authentication. I'm using NPM packages mssql for querying the database and @azure/msal-browser and @azure/msal-react for single sign on. The signal sign one is working perfect but the database connection is what I'm issue at. Look my db.js code below and please assist me how I can authentic user when the login to the app. Secondly do I have to add every user to the database for it work for them when they access the web app and use it the application. What's the best way I can go about this. I'm new to building full stack with Azure sql database. I try username and password for the database connection and it work fine but the Microsoft Entra-only authentication is what I'm having issue at

Here is my config code (db.js)

const sql = require("mssql");

const config = {
  server: process.env.DB_SERVER,
  database: process.env.DB_NAME,
  options: {
    encrypt: true,
    enableArithAbort: true,
  },
  authentication: {
    type: "azure-active-directory-msi-app-service",
  },
};

const pool = new sql.ConnectionPool(config);

async function connect() {
  try {
    await pool.connect();
    console.log("Connected to SQL Server!");
    return pool;
  } catch (err) {
    console.error("Error connecting to SQL Server:", err);
    throw err; // Re-throw error for proper handling
  }
}

module.exports = {
  connect,
  pool,
};

Answer 1

the node process will connect with the defined service account, not the users, so you do not need to add any users besides the service account. did you configure the hosting enviroment to use the service account?

https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-azure-database?tabs=sqldatabase-sc%2Cuserassigned-sc%2Cnodejs%2Cdotnet-mysql-mi%2Cdotnet-postgres-mi%2Cwindowsclient