I have an Express.js app that connects to the Azure MYSQL DB using Managed Identity. The server.js file imports and calls the setupConnectionPool function, successfully gets access token and connects to the DB. However, after 24 hours the token expires, and none of my solutions to refresh the token have worked so far.
I tried doing setInterval and setTimeout to re-call the setupConnectionPool at the time of expiration, also tried if(accessToken.expiresOnTimestamp < Date.now()), but no success. Last piece of a client project I need resolved, appreciate any ideas!
const mssql = require('mssql');
require('dotenv').config();
const { DefaultAzureCredential } = require("@azure/identity");
async function setupConnectionPool() {
try {
console.log(process.env.AZURE_DEV_MI_CLIENT_ID)
const credential = new DefaultAzureCredential({
managedIdentityClientId: process.env.AZURE_DEV_MI_CLIENT_ID,
});
console.log('credentialll', credential)
let accessToken = await credential.getToken("https://database.windows.net/.default");
console.log('access token', accessToken)
const refreshTime = accessToken.expiresOnTimestamp - Date.now();
console.log('refreshTime', refreshTime)
const refreshToken = async () => {
if(pool) {
console.log('close pool')
await pool.close();
}
console.log('refresh token func call')
try {
console.log('try setupconnectionpool')
await setupConnectionPool();
} catch(e) {
console.log('catch e', e)
}
}
setTimeout(() => {
console.log('Get refresh token - 10000');
refreshToken();
}, (refreshTime - 10000))
const config = {
user: "",
password: "",
server: process.env.DB_SERVER,
authentication: {
type: "azure-active-directory-access-token",
options: {
token: accessToken.token,
},
},
options: {
database: process.env.DB_NAME,
encrypt: true,
port: 1433,
},
};
const pool = new mssql.ConnectionPool(config);
await pool.connect();
console.log('pool', pool);
return pool;
} catch (error) {
console.error("Error setting up connection pool:", error);
throw error;
}
}
module.exports = { setupConnectionPool }
2
Answers
I was able to solve this by adding a check for the pool's _healthy status. If the _healthy status is false or null, then it goes through same steps as already noted in setupConnectionPool function to get a new token and connect. Here is code added to the setupConnectionPool function:
Then in all of my API routes, it is set up to call the setupConnectionPool, where if pool _healthy is true, it keeps same connection, if not get new access token and connects:
According to the document on Refresh Tokens in the Microsoft identity When a client obtains an access token to access a protected resource, it also receives a refresh token. This refresh token is used to acquire new access tokens when the current one expires.
Refresh tokens have a longer lifetime compared to access tokens. The default lifetime is 24 hours for single-page apps and 90 days for other scenarios. Each time a refresh token is used, it replaces itself with a fresh token.
Output:
Refresh tokens can be revoked at any time due to timeouts, revocations, or other factors. Revocation can occur because of changes in credentials, user actions, admin actions, or single sign-out events.
If a refresh token is sent to a redirect URI registered as a single-page app, it expires after 24 hours. Apps must be prepared to rerun the authorization code flow to obtain a new refresh token every 24 hours.
The lifetime of refresh tokens cannot be configured but Sign-in frequency can be managed through Conditional Access policies to define when users need to sign in again.