Beyond Connection Strings: Securing Azure SQL Database Access with Managed Identity

Modern applications rarely work in isolation. An application typically needs to communicate with multiple services such as databases, storage accounts, and other cloud resources. To establish these connections securely, applications need to authenticate themselves to the services they are accessing.

“Traditionally, this authentication is often implemented using credentials such as usernames, passwords, connection strings, access keys, or client secrets. While this approach works, it introduces an additional responsibility: managing those credentials securely. Credentials need to protected from accidental exposure, rotated periodically, and made available to the application at runtime. Manually management of these credentials can become both a security risk and an operational overhead.”

This is where Azure Managed Identity comes into picture. Managed Identity provides an identity for the Azure resource, such as App Service, which the application can use to obtain Microsoft Entra tokens without having to manage credentials itself. The identity can then be authorized to access downstream Azure resources that support Microsoft Entra authentication, including Azure SQL Database.

In this article, we will walk you through how we implemented this, approach how the authentication flow works, the configurations required on both Azure App Service and Azure SQL Database, and the lessons we encountered along the way.

What are managed identities?

There are broadly two categories of identities: human and machine/non — human identities. Machine identities represent devices and worklaod identities. Within Microsoft Entra workload identities are represented as applications, service principals and managed identities.

“A Managed Identity is an identity that can be assigned to any Azure compute resource such as Azure VM, Azure Kubernetes Cluster, or Azure App Service. Once an identity is assigned to a resource it can be authorized to access downstream dependency resources such as Storage Account, SQL Database, Cosmos DB and so on.”

Types of Managed Identities

Azure provides two types of managed identities: System — Assigned and User — Assigned.

System — Assigned: It is created directly on the resource such as Virtual Machines or App Service. When you enable a system — assigned identity:

1. Azure creates a corresponding service principal in Microsoft Entra ID. This service principal is associated with the lifecycle of the resource. In other words, the identity is created when you enable it on the resource and is automatically removed when the resource itself is deleted.
2. By design, only that specific Azure resource can use the identity to request tokens from Microsoft Entra ID.
3. Once the identity has been created, you can authorize it to have access to one or more resources.
4. The name of the system — assigned service principal corresponds to the name of the Azure resource to which it belongs.

User — Assigned: It can be created independently as a standalone Azure resource. When you enable a user — assigned identity:

1. Azure creates a corresponding service principal in Microsoft Entra ID. The service principal is managed separately from the resources that use it.
2. The identity can be associated with multiple resources.
3. You can authorize the identity to have access to one or more resources.

A Simple Analogy for Managed Identity

To understand Managed Identity, imagine that you own a car and need to get into your garage.

There are several ways you could open the garage door. You could use a traditional key, press a button built into the car, or use a separate remote control.

Now, let’s map this scenario to Azure.

Think of your car as an Azure resource — for example, an Azure App Service. The garage represents another resource that your application needs to access, such as an Azure SQL Database or a Storage Account. The key, built-in button, or remote control represents the different ways your application can establish its identity and gain access to the target resource.

Traditional Credentials: The Physical Key

A traditional username and password, or connection string can be compared to carrying a physical key to your garage.

The key allows you to open the garage, but you are responsible for taking care of it. You need to make sure it is stored safely, prevent it from being copied or exposed, and replace it when necessary.

Application credentials work in a similar way. If an application uses a database password or access key, someone needs to store that secret securely, make it available to the application, and rotate it when required.

This is the problem Managed Identity helps us avoid.

System — assigned identity: The built-in button

Now imagine that your car has a button built directly into it that can open the garage door.

The button belongs to that particular car. If you sell the car, the button goes with it. You don’t take the button out and give it to another car.

This is similar to a system-assigned Managed Identity.

The identity is created specifically for an Azure resource and is tied to that resource’s lifecycle. If the resource is deleted, its system-assigned identity is also removed.

User — assigned identity: The remote control

Now consider a separate garage-door remote control. Unlike the button built into your car, the remote exists independently of the car.

You could use the same remote with different cars, provided those cars are configured to work with it.

This is similar to a user-assigned Managed Identity.

The identity exists as a separate Azure resource and can be assigned to multiple Azure resources.

Bringing the Analogy Back to Azure

The important idea behind the analogy is that the application doesn’t need to carry a secret with it in order to access another resource.

Instead, an Azure resource can have an identity, and that identity can be granted the appropriate permissions on the target resource.

In our case, the Azure App Service is the car, and Azure SQL Database is the garage. We enabled a Managed Identity on the App Service and authorized that identity to access our SQL Database.

There is no SQL password embedded in the application configuration. Instead, Azure provides the application’s identity, and Azure SQL determines what that identity is allowed to access.

“This is the fundamental idea behind Managed Identity: give the workload an identity, grant that identity the required permissions, and remove the need for the application to manage long-lived credentials itself.”

Now that we understand the concept, let’s see how this can be implemented in real — time.

Step — 1: Enable Managed Identity on Azure App Service

Enable Managed Identity

“Enabling Managed Identity does not automatically grant the Azure App Service to Azure SQL Database.”

You’ve only established who the application is. You still need to configure what that identity is allowed to access.

You can also enable managed identity for the app using the az webapp identity assign command, replacing <app-name> with your app name.

az webapp identity assign --resource-group myResourceGroup --name <app-name>

Step — 2: Grant Admin access for Microsoft Entra User to the Database

Enable Microsoft Entra authentication to the Azure SQL database by assigning a Microsoft Entra user as the admin of the Azure SQL server. The Microsoft Entra admin must be a user that is created, imported, synced, or invited into Microsoft Entra ID.

Set Admin for Database

You can also set Microsoft Entra ID user as an admin on the Azure SQL server by using az sql server ad-admin create with the object-id parameter.

az sql server ad-admin create --resource-group myResourceGroup --server-name <server-name> --display-name ADMIN --object-id <entra-id>

Step — 3: Grant permissions to the managed idenity

Get Cloud Odyssey’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To configure this, we connect to the SQL Server using a Microsoft Entra ID user that has been configured as the Entra administrator for the SQL Server.

Once authenticated, we connect to the target database and create a database user representing the App Service’s Managed Identity. We then grant this user only the permissions required by our application.

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;

ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
GO

The first command creates a database user for the Managed Identity. The FROM EXTERNAL PROVIDER clause tells Azure SQL that the identity is managed through Microsoft Entra ID rather than being a traditional SQL user.

The next two commands assign the db_datareader and db_datawriter roles to the identity. This allows the application to read from and write to the database while avoiding unnecessary administrative privileges.

This is an important part of the implementation because authentication and authorization are separate concerns. The Managed Identity allows Azure SQL to identify the application, while the database roles determine what the application is actually permitted to do.

By granting only the permissions our application requires, we follow the principle of least privilege and avoid giving the application’s identity broader access than necessary.

Step — 4: Update the Connection String

With the Managed Identity configured on our App Service and the required permissions granted in Azure SQL Database, the next step is to update the application’s database connection.

Previously, our application used a traditional SQL authentication-based connection string, where the database username and password were included as part of the connection details:

Server=servername;Database=dbname;User Id=username;Password=password

With Managed Identity, we no longer need to provide a SQL username and password in the connection string. Instead, the application connects to the Azure SQL Database using Microsoft Entra authentication and the identity assigned to the App Service.

The connection string is therefore simplified to contain only the SQL Server and database information:

server=tcp:<server-name>.database.windows.net;database=<db-name>;

The important difference is that the connection string no longer contains credentials. The application uses the Managed Identity configured on the App Service to authenticate to Azure SQL.

Step — 5: Modify your Code

In our application, we use DefaultAzureCredential from the Azure.Identity library to obtain an access token for Azure SQL Database. The token is then assigned to the SqlConnection before the connection is used.

The implementation is encapsulated in a small helper class:

using Azure.Core;
using Azure.Identity;
using Microsoft.Data.SqlClient;

public class AzureManagedIdentitySqlConnection
{
    private static readonly string[] _azureSqlScopes =
        new[] { "https://database.windows.net/.default" };

    private static readonly TokenCredential _credential =
        new DefaultAzureCredential();

    public async Task<SqlConnection> GetAzureManagedIdentitySqlConnection(
        string connectionString)
    {
        var sqlConnection = new SqlConnection(connectionString);

        var tokenRequestContext =
            new TokenRequestContext(_azureSqlScopes);

        AccessToken tokenCredential =
            await _credential.GetTokenAsync(
                tokenRequestContext, default);

        sqlConnection.AccessToken = tokenCredential.Token;

        return sqlConnection;
    }
}

Let’s break down what is happening here.

The first important piece is DefaultAzureCredential:

private static readonly TokenCredential _credential =
    new DefaultAzureCredential();

DefaultAzureCredential provides a unified way for the application to obtain an Azure access token. It can use different authentication mechanisms depending on where the application is running. This is particularly useful for applications that need to run both locally during development and in Azure after deployment.

Next, we specify the scope for which the token is required:

private static readonly string[] _azureSqlScopes =
    new[] { "https://database.windows.net/.default" };

The https://database.windows.net/.default scope indicates that the requested token is intended for Azure SQL Database.

We then create a TokenRequestContext using this scope and request an access token:

var tokenRequestContext =
    new TokenRequestContext(_azureSqlScopes);

AccessToken tokenCredential =
    await _credential.GetTokenAsync(
        tokenRequestContext, default);

Our application is not retrieving a database password or storing a secret. Instead, it requests a short-lived Microsoft Entra access token using the available Azure identity.

Finally, we assign the token to the SQL connection:

sqlConnection.AccessToken = tokenCredential.Token;

The SqlConnection now has the access token it needs to authenticate to Azure SQL Database. When the connection is opened, Azure SQL can validate the token, identify the Managed Identity associated with our App Service, and then apply the database permissions we configured earlier.

Step — 6: Test & Publish

The final step is to test the complete flow and publish the application to Azure App Service. Before publishing, we first verify the application locally to make sure that the database connection and application functionality are working as expected.

After the local validation is complete, we publish the application to our Azure App Service. Once deployed, the application runs using the Managed Identity configured on the App Service.

Step — 7: Troubleshooting

One of the useful lessons from this implementation was that enabling Managed Identity alone does not guarantee that the application will be able to connect to Azure SQL Database.

When a connection fails, there are two important things we verify on the database side:

1. Whether a database user has been created for the Managed Identity.
2. Whether that user has been granted the required permissions.

SQL User Hasn’t Been Created
The first thing we verify is whether the Managed Identity has been created as a user in the target database.

The Managed Identity exists in Microsoft Entra ID, but Azure SQL Database also needs a corresponding database user. If this user has not been created, Azure SQL cannot associate the incoming identity with a database principal.

We can verify this by querying the database:

SELECT name, type_desc
FROM sys.database_principals
WHERE name = '<identity-name>';

If the Managed Identity is not returned by the query, we create the database user using the command mentioned in the above steps.

Once the user is created, we can move on to verifying its permissions.

Incorrect Database Permissions
Creating the database user establishes the identity within the database, but it does not automatically give the application permission to perform database operations.

The next step is to verify which database roles or permissions have been assigned to the Managed Identity. If the user exists but the application still receives an authorization or permission-related error, we verify that the required roles have been assigned to the correct identity and in the correct database.

Conclusion

Managing credentials is an important part of securing any application that communicates with other services. Traditional approaches such as storing usernames, passwords, access keys, or client secrets can work, but they also introduce the additional responsibility of securely storing, protecting, and rotating those credentials.

Azure Managed Identity allowed us to take a different approach. By enabling a system-assigned Managed Identity on our Azure App Service, we gave the application an identity that it could use to authenticate to Azure SQL Database through Microsoft Entra ID.

We then created a database user for that identity and granted it only the permissions required by our application. The application itself no longer needs to carry database credentials in its configuration. Instead, it obtains an access token using the Managed Identity and uses that token to authenticate to Azure SQL.

The biggest takeaway from our implementation is that Managed Identity is not just about removing passwords from a connection string. It provides a way for Azure workloads to have an identity of their own, authenticate to supported services, and access those services based on explicitly assigned permissions.

This resulted in a simpler and more secure authentication model, while also reducing the operational overhead associated with managing database credentials.

References

Microsoft Learn — https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview

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