How to connect (and stay connected) to Azure SQL Database using managed identities in Node.js - Part 1: Initial Setup

Traditionally, connecting to a SQL database within a Node.js app was fairly straightforward:

1. Create new SQL database
2. Set new admin credentials (never keep defaults)
3. Add SQL server admin credentials to .env
4. Use admin credentials to connect to SQL database on server startup
5. Go on vacation!

This method has worked for a long, long time and is great, but there are some drawbacks.

First off, anyone working with your SQL database programmatically, either through your Node.js app or otherwise, would need those credentials. This is a huge security concern because then it becomes difficult to change those credentials frequently without breaking any apps or scripts that use them.

Secondly, allowing apps to use the SQL server admin credentials grants that app FULL permissions to the SQL server, which means any developer on your team could drop the production database inadvertently. Now you could create alternate users within your SQL server and grant those users reduced permissions, but if you're hosting your resources on Azure, there's a better way: managed identities.

What are managed identities?

Managed identities are like user accounts, but for apps. Just like users within your organization (assuming you're using Azure Active Directory for user management), you can add "service principals" to Azure Active Directory and then assign permissions to them via Role-Based Access Controls (RBAC). These "service principals" can come in many forms, but the one we want to focus on right now is the Managed Identity.

How do I create a managed identity for my app?

If you've already deployed, or are planning to deploy, your app to an Azure resource (Function App, App Service Web App, etc.), then you can just turn on the functionality. Refer to Azure services that can use managed identities to access other services for a list of Azure resources that support managed identities.

To create a managed identity for your app:

1. Within the Azure portal, navigate to your app resource

1. On the left sidebar menu, under Settings, click on 'Identity'

1. Turn 'Status' to 'On', hit 'Save', and then select 'Yes' to confirm that you want to enable the system assigned managed identity

Note: System assigned managed identities are recommended as they follow the same lifecycle as the Azure resource, but you can alternatively create a User assigned managed identity if you want it to be separate.

I've created my managed identity...now what?

Now that you have a managed identity created for your app, you'll need to grant the managed identity access to the Azure SQL database.

First, make sure that Azure Active Directory authentication has been enabled on the SQL server. To verify this:

1. Within the Azure portal, navigate to the SQL server (not the database), and click on either 'Azure Active Directory in the left sidebar menu, or 'Active Directory admin' in the features section

1. Click 'Set admin', search for and select an Azure Active Directory user, and hit 'Save'

Once Azure Active Directory authentication has been set up, you'll need to add the managed identity as a user in the database and assign it the roles it will need so it can perform the operations it is meant to perform

To add the managed identity into the database:

1. Log into the Azure portal using the Azure Active Directory credentials, if not already

2. Navigate to the SQL database and click 'Query editor (preview)' from the left sidebar menu, and then click on 'Continue as [AAD user here]...' under 'Active Directory authentication

Note: At this point, it may be necessary to create a new firewall rule to allow your IP address range access to the SQL server

1. Within the query screen that opens, create the managed identity as a user in the database and indicate that the user information is being provided by Azure Active Directory by running the following SQL command:

CREATE USER [managedidwebapp] FROM EXTERNAL PROVIDER

Make sure to replace managedidwebapp with the name of your Azure app resource

1. Grant the managed identity the database-level roles it needs, such as db_datareader and db_datawriter by running the following SQL commands:

ALTER ROLE db_datareader ADD MEMBER [managedidwebapp]
ALTER ROLE db_datawriter ADD MEMBER [managedidwebapp]

Make sure to replace managedidwebapp with the name of your Azure app resource that you added in the previous step

Using managed identity in Node.js app

You've now successfully created a managed identity for your app, added the managed identity as a database user, and granted that managed identity the necessary permissions for the SQL database. Great job!

However, our work's not quite done yet. After all, what good is the managed identity if you're app isn't actually using it?!

To access and use the managed identity in a Node.js app, first we'll need the @azure/identity package, so run

npm install @azure/identity

within your app directory to add it to the project dependencies.

The specific component we need from this package is the DefaultAzureCredential, as it is the most flexible, by allowing us to use different credential types during local development without making any code changes. Let's import that into the entry point of our app as follows:

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

Now that we've imported the DefaultAzureCredential class, we need to create a new instance. Since we're going to need access to this credential instance in other parts of our app, let's add it to Express's app.locals object as follows:

app.locals.credential = new DefaultAzureCredential();

This constructor will look for existing credentials to use, which in our case will be the managed identity when deployed, but what about while we're developing locally? Well in that case, DefaultAzureCredential can use various credentials, such as Azure CLI, VS Code Azure extension, etc. Read more about that in the Microsoft Docs.

The key point worth noting here is that whichever method you use to authenticate locally to Azure Active Directory, you'll need to follow the steps mentioned earlier to make sure that those credentials have the same level of access to the SQL database as your app's managed identity.

Once we've instantiated the credential we need, we'll need to use it to connect to our SQL database. First, install the mssql package if not already installed by running

npm install mssql

You might be able to use a different SQL package, but mssql is maintained by Microsoft and therefore stays more up-to-date with Azure changes, such as managed identities.

With that installed, let's import it into the app entry point as follows:

const sql = require('mssql');

Next, let's decide how we're going to authenticate with the SQL server. There are many different options (at the time of writing this, the available options are: default, ntlm, azure-active-directory-password, azure-active-directory-access-token, azure-active-directory-msi-vm, and azure-active-directory-msi-app-service). To allow us to use DefaultAzureCredential locally and deployed, however, the easiest option to configure is azure-active-directory-access-token. Unfortunately, it does require the extra step of obtaining an access token from Azure Active Directory, so let's do that now.

We'll call the getToken method of the DefaultAzureCredential class and pass in a scope of https://database.windows.net/.default like so:

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

This method returns an object with the token as well as an expiresOnTimestamp property that we'll need later, so that's why we're storing it in Express's app.locals object and naming it tokenObj instead of just token. Also, this method is asynchronous since it is sending a network request to Azure Active Directory, so I find it best to add async to the app.listen callback, and await the result, like so:

app.listen(7000, async () => {
  app.locals.tokenObj = await app.locals.credential.getToken(
    'https://database.windows.net/.default'
  );
  console.log(`Listening on ${process.env.PORT}`);
});

Now that we have our access token, let's create the connection config object that we'll need to provide when we connect to the server:

const config = {
  authentication: {
    type: 'azure-active-directory-access-token',
    options: {
      token: app.locals.tokenObj.token
    }
  },
  server: 'managedidsqlserver.database.windows.net',
  database: 'testdb'
};

Replace the server and database values accordingly

Putting it all together, your 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.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
  };

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

End of Part 1

Success! You should now be able to connect to your Azure SQL database through your Node.js app using the managed identity when deployed, and your chosen authentication method when developing locally. If you ran into any errors, let me know in the comments.

Continue on to How to connect (and stay connected) to Azure SQL Database using managed identities in Node.js - Part 2: Testing connection & handling token expiration, where I'll show you how to use this connection in your queries, and also outline the process for refreshing our access token after it expires.

Till next time!