Stop using SQL authentication in Azure apps!

Connecting web apps to a SQL database is an extremely common pattern. Most, if not all, app projects I work on will involve querying SQL in some way.

We just set up a SQL database user with a password, then include this in our connection string to SQL. Easy, right?

Well that's a perfectly viable approach, and will do the job nicely, but it does come with a big risk - credential leakage.

We may only intend to use that database user with our application, but there's nothing stopping something (or someone!) else using it that shouldn't have access.

So how do we avoid this and get rid of database credentials in Azure? Step forward, Managed Identities.

What are Managed Identities?

Managed Identities are a way to register a service in Azure with Microsoft Entra. Entra will then manage that identity (hence the name), and handle any authentication requests to other services.

We can register a managed identity for a resource in Azure and use that to connect to an Azure SQL database. Microsoft Entra authenticates our request and does all the heavy lifting. Not a single password in sight.

Here's how to do it.

1. Assign a managed identity to our service
2. Add the managed identity as a SQL database user
3. Use a connection string that references the managed identity

We're going to use an Azure App Service connecting to an Azure SQL database in the examples below, but the same approach will work for many other Azure services as well.

Assign a Managed Identity to Our Service

We have two choices for the managed identity:

1. System-assigned
2. User-assigned

We won't go into detail of when to use each one in this blog post, but for simplicity we will add a system-assigned identity to our app.

We can do that directly in the Azure portal:

Or alternatively in a Bicep template:

resource appService 'Microsoft.Web/sites@2024-04-01' = {
  identity: {
    type: 'SystemAssigned'
  }
  name: 'my-app-service'
  properties: {
    // ...
  }
  // ...
}

Add the Managed Identity to SQL

Next, we need to add the managed identity as a user in the SQL database.

To do that we can use the following SQL query:

-- Be sure to run this from the database your app will use
-- (not the master database)
CREATE USER [app-service-name] FROM EXTERNAL PROVIDER

Notice there's no password specified here.

This goes off and queries the Microsoft Entra directory for the name of the managed identity, and so we need to be signed in to the database as a Microsoft Entra account to run it.

With a system-assigned managed identity, the name we need to use is the name of the resource. If we were using a user-assigned identity instead, we would replace app-service-name with the name of the identity.

Note that if you have multiple identities with the same name, you can specify which one to use by including the object ID in the query:

CREATE USER [identity-name] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '00000000-0000-0000-000000000000'

We can then assign our database user any roles/permissions we need, just like a regular SQL user:

ALTER ROLE [db_datareader] ADD MEMBER [app-service-name]

Use a Managed Identity Connection String

There are many ways to connect different services to SQL, but commonly within an app we will use a database connection string.

To use the managed identity, we can set Authentication=Microsoft Entra Managed Identity in the connection string, e.g.:

Server=my-server.database.windows.net; Database=my-database; Authentication=Microsoft Entra Managed Identity

Azure will then check the service making the call (our app service in this case), validate the identity, and tell our database who is accessing. Only our app service can access with that identity. No passwords. No keys. No stress.

Bonus Tip

To help reduce the reliance on SQL logins, you can enable 'Microsoft Entra authentication only' on your Azure SQL server. This doesn't enforce managed identities, but does help limit the risk of SQL credentials being leaked.

Wrapping-up

Using managed identities to connect to SQL in Azure helps us reduce the number of credentials we use in our solutions. Not only do we reduce the chance of them being leaked, we also remove a lot of administrative overhead by not needing to rotate passwords.

They're a great way to make our solutions more secure, with very little extra configuration. We can't use them in every single scenario (such as connecting to SQL from outside Azure), but where we can, in my opinion, they're a no-brainer.

Are you using managed identities to connect to SQL in Azure? Have you used them anywhere else in your solutions?

I hope you found this one useful. Catch you next time.