Dev-ish
How to connect (and stay connected) to Azure SQL Database using managed identities in Node.js - Part 2: Testing connection & handling token expiration

How to connect (and stay connected) to Azure SQL Database using managed identities in Node.js - Part 2: Testing connection & handling token expiration

Richard Carrigan's photo
Richard Carrigan
·

7 min read

Table of contents

Quick recap

In How to connect (and stay connected) to Azure SQL Database using managed identities in Node.js - Part 1: Initial Setup, I walked through how you can configure your Node.js web app to authenticate and connect to an Azure SQL database using managed identities...but we're not done yet. If you're like I was the first time I set this up, you probably still have some unanswered questions, such as:

  • Do we have to reconnect to the database with every request?
  • How do we query the database using this new connection?
  • Does the access token last forever, or does it expire eventually?
  • If it expires, what is the process for handling that?

So in Part 2, we're going to go through and address each of these concerns...and more. Let's get started!

Note: If you've landed on this article without first reading part 1, I highly encourage you to go back and read that first, as the concepts and walkthroughs pick up and build on where part 1 left off.

Do we have to reconnect to the database with every request?

No. Thanks to some really smart people, we're able to take advantage of a feature called "connection pooling". Connection pooling is a feature that allows our app to create a set of long-living connections to the database, and to simply reuse them over and over, rather than opening and closing connections to the database for every request. You can read more about connection pooling in the node-mssql docs.

To utilize this feature in our Node.js app, the only change we need to make is to store the SQL connection in the Express app.locals object so we can access it throughout our app. Within your entry point, index.js in my example, replace:

await sql.connect(config);

with:

app.locals.db = await sql.connect(config);

With our connection now saved and accessible throughout our app, we can now access the connection within any Express route through req.app.locals.db, like so:

const pool = req.app.locals.db;

Now we're ready to create our first query.

How do we query the database using this new connection?

To create a simple query so we can test the database connection, let's add the following route right before app.listen:

app.get('/products', async (req, res) => {
  const pool = req.app.locals.db;

  try {
    const queryResult = await pool.query('SELECT * FROM SalesLT.Product');
    res.send(queryResult.recordset);
  } catch (err) {
    console.log(err);
    res.status(500).send('Uh oh, something went wrong');
  }
});

This route is called whenever a client sends an HTTP GET request to the /products route (e.g. http://localhost:7000/products during local development) and executes a simple query that retrieves all fields for all records from the SalesLT.Product table within the database using the query() method provided by our connection pool instance. It then sends the recordset array, which contains an object representing each record, back to the client.

Because we're querying the database over the network, we have to make the callback function async and then await the result of the query.

Also, because there's always the possibility that our query could fail (network connection drops, service outages, etc.), we're wrapping the entire operation with a try...catch block and sending a 500 status code and error message back to the client if an error occurs.

Note: as a best practice, never send the error directly to the client, as bad actors testing your API could use this information to learn more about the structure of your API and/or database, such as table names, field names, required vs optional fields, etc.

After adding this new route, start your server using npm start or npm run dev depending on your setup, and then navigate to the /products route in your browser, or send a GET request through a service like Postman. If all was successful, you should now see the full products array in the response.

Note: If you run into error messages like Cannot read properties of undefined (reading 'query'), check that you've saved the connection to app.locals.db, but are accessing it in your route using req.app.locals.db. Many other examples online show that you can access app.locals directly, but I have found that to not be the case.

Does the access token last forever, or does it expire eventually?

Ok, so maybe you weren't actually wondering this. I know I wasn't when I first configured this, but when it did expire and I had to troubleshoot the issue, I definitely wished I had asked this question beforehand. Truth be told, this exact issue was a driving force for me to write this series of articles, so that hopefully I can save you from the pain I went through.

At the time of this writing, Microsoft states in their Access tokens and refresh tokens documentation, that the token expires after 1 hour. However, in testing this by logging the app.locals.tokenObj.expiresOnTimestamp property on server startup, I've verified that the access token issued by the @azure/identity package's DefaultAzureCredential getToken() method actually lasts for 24 hours.

If it expires, what is the process for handling that?

So, if the access token only lasts 24 hours, does that mean we'll have to restart our server every 23.5 hours to get a new token? Definitely not. Instead, we need to perform the following steps before processing each incoming request:

  1. Check to see if the token has expired. If expired...
  2. Close the SQL connection
  3. Obtain a new token
  4. Establish a new SQL connection using the new token

I know that sounds like a lot of work, and to an extent it is, but the end result of a stable connection is well worth it.

Check token expiration status

Within our new /products route, add the following if statement at the beginning of the try...catch block:

if (req.app.locals.tokenObj.expiresOnTimestamp < Date.now()) {
}

This uses the built-in expiresOnTimestamp property of the access token object and checks to see if it's less than (aka earlier than) the current timestamp.

Shout out to @KarishmaGhiya for helping me with this part!

Close the SQL connection

Within the above if statement, add the following line:

await sql.close();

Just like that, we've closed the connection to the database!

Note: be sure to close the database connection (sql variable), not the connection pool (req.app.locals.db), as closing the connection pool will not throw any errors, but also will not refresh the connection as expected.

Obtain a new token & establish a new SQL connection

Copy the following code from the app.listen method:

app.locals.tokenObj = await app.locals.credential.getToken(
  'https://database.windows.net/.default'
);

const config = {
  authentication: {
    type: 'azure-active-directory-access-token',
    options: {
      token: app.locals.tokenObj.token
    }
  },
  server: process.env.DB_SERVER,
  database: process.env.DB_NAME
};

app.locals.db = await sql.connect(config);

Within the try block, after closing the SQL connection, paste in the copied code. Make sure to change all instances of app.locals to req.app.locals!

Note: As a DRY optimization, you can also move this code into its own function, such as connectDBAsync(), and then call it from both app.listen and all routes.

Your complete index.js should now look like this:

const express = require('express');
const { DefaultAzureCredential } = require('@azure/identity');
const sql = require('mssql');

const app = express();

app.locals.credential = new DefaultAzureCredential();

app.get('/products', async (req, res) => {
  const pool = req.app.locals.db;

  try {
    if (req.app.locals.tokenObj.expiresOnTimestamp < Date.now()) {
      await sql.close();
      req.app.locals.tokenObj = await req.app.locals.credential.getToken(
        'https://database.windows.net/.default'
      );

      const config = {
        authentication: {
          type: 'azure-active-directory-access-token',
          options: {
            token: req.app.locals.tokenObj.token
          }
        },
        server: process.env.DB_SERVER,
        database: process.env.DB_NAME
      };

      req.app.locals.db = await sql.connect(config);
    }
    const queryResult = await pool.query('SELECT * FROM SalesLT.Product');
    res.send(queryResult.recordset);
  } catch (err) {
    console.log(err);
    res.status(500).send('Uh oh, something went wrong');
  }
});

app.listen(process.env.PORT, async () => {
  app.locals.tokenObj = await app.locals.credential.getToken(
    'https://database.windows.net/.default'
  );

  const config = {
    authentication: {
      type: 'azure-active-directory-access-token',
      options: {
        token: app.locals.tokenObj.token
      }
    },
    server: process.env.DB_SERVER,
    database: process.env.DB_NAME
  };

  app.locals.db = await sql.connect(config);
  console.log(`Listening on ${process.env.PORT}`);
});

Recent Update

Shortly after writing Part 1, I learned that mssql now supports use of the azure-active-directory-default authentication type. Thank you to @MichealSun90, @mShan0, and @dhensby for making this happen! Support for this authentication type means that the entire process of creating, checking, and refreshing tokens is no longer necessary. You can now directly use your DefaultAzureCredential instance and it #justworks!

Conclusion

So that's it! If you've been following along, you should now have a fully functional Node.js API that authenticates to your Azure SQL database using managed identities, and maintains that connection by checking and refreshing tokens as needed.

If you found this two-part series helpful, please consider liking and/or sharing it, so other people can benefit too!

If you ran into any issues while following along, or if you're experiencing an issue related to this, feel free to drop a comment and I'll do my best to answer it, or at least help you find the right resources so you can quickly resolve your issue and get back up and running!

Till next time!

AzureSQLNode.js