How to Use Managed Identities with Azure SQL Database

Managed identities allow Azure resources to authenticate or authorize themselves with other supported Azure resources.

Oct 26, 2020 • 7 Minute Read

Introduction

Managed identities is a Microsoft Azure feature that allows Azure resources to authenticate or authorize themselves with other supported Azure resources. The appeal is that secrets such as database passwords are not required to be copied onto developers’ machines or checked into source control.

In this guide, you will learn how to use managed identities to connect a .NET app service to Azure SQL Database using managed identities.

Note: Beginning with Microsoft.Data.SqlClient version 2.1.0-preview2 the nuget package provides out of the box support for Managed Identity.

Configure Your Azure Database

In order to allow managed identities to connect to Azure SQL Database, you need to enable Azure Active Directory (AD) authentication and create the managed users in the database.

To enable Azure AD authentication for your Azure SQL Server, make sure there is an Azure AD admin configured for the database server.

Then, enable authentication from your managed identity by creating a contained user. This differs from on-premises SQL Server instances that require both a server login and a database user. First, ensure that a system-assigned identity is set for your app service. Once an identity has been assigned to your app service, run the below SQL to create a contained user with some roles to read and write to the database.

      create user [my-app-service] from external provider;
alter role db_datareader add member [my-app-service];
alter role db_datawriter add member [my-app-service];

create user [my-app-service/slots/staging] from external provider;
alter role db_datareader add member [my-app-service/slots/staging];
alter role db_datawriter add member [my-app-service/slots/staging];
    

Note: my-app-service is a placeholder. You will need to replace it with the name of your own app service. Further, for system-assigned identities, different deployment slots have their own individual identities.

Update Your SQL Connection to Use Managed Identities

Now that you have created the SQL users and assigned them read and write roles, you need to change your app to use managed identities for authentication and authorization.

To start using an Azure App Service managed identity, create a new project and install a few packages.

      mkdir PLSQLManagedIdentity
cd PLSQLManagedIdentity
dotnet new mvc
dotnet add package Microsoft.Azure.Services.AppAuthentication
dotnet add package Microsoft.Data.SqlClient
    

The package Microsoft.Azure.Services.AppAuthentication is an Azure SDK that simplifies authentication.

When you run the code on your development machine, it will use the Azure CLI or Visual Studio login to authenticate. When you run the code in an Azure App Service, it will use the system-assigned identity.

Next add the class SqlAppAuthenticationProvider to your project.

      /// <summary>
/// An implementation of SqlAuthenticationProvider that implements Active Directory Interactive SQL authentication.
/// </summary>
public class SqlAppAuthenticationProvider : SqlAuthenticationProvider
{
    private static readonly AzureServiceTokenProvider _tokenProvider = new AzureServiceTokenProvider();

    /// <summary>
    /// Acquires an access token for SQL using AzureServiceTokenProvider with the given SQL authentication parameters.
    /// </summary>
    /// <param name="parameters">The parameters needed in order to obtain a SQL access token</param>
    /// <returns></returns>
    public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
    {
        var authResult = await _tokenProvider.GetAuthenticationResultAsync("https://database.windows.net/").ConfigureAwait(false);

        return new SqlAuthenticationToken(authResult.AccessToken, authResult.ExpiresOn);
    }

    /// <summary>
    /// Implements virtual method in SqlAuthenticationProvider. Only Active Directory Interactive Authentication is supported.
    /// </summary>
    /// <param name="authenticationMethod">The SQL authentication method to check whether supported</param>
    /// <returns></returns>
    public override bool IsSupported(SqlAuthenticationMethod authenticationMethod)
    {
        return authenticationMethod == SqlAuthenticationMethod.ActiveDirectoryInteractive;
    }
}
    

Then in your Program.cs, register the new SqlAuthenticationProvider using the following as the first line of the main method.

      SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryInteractive, new SqlAppAuthenticationProvider());
    

The two code snippets together will look for SQL connection strings that contain Authentication=Active Directory Interactive. When found, they will use the AzureServiceTokenProvider to fetch an access token to authenticate with Azure SQL Database.

Update Your Connection String

You have now set up all the pieces to authorize your app to your Azure database. Your connection string can be updated to the following:

      Server=xxxxxxx.database.windows.net,1433;Database=yyyyyyyyy;UID=a;Authentication=Active Directory Interactive
    

You only need to specify the server, authentication, and the database name. UID is set to an arbitrary value since it is required for the connection string to pass validation. However, it is not used for system-assigned managed identity and Azure CLI authentication.

Note: If you are using user-assigned identities and not using the global Azure region, you will need to modify the SqlAppAuthenticationProvider class. Refer to Microsoft's implementation of SqlAppAuthenticationProvider, which allocates more memory but is more flexible as reference.

Conclusion

Microsoft Azure has a growing list of services that support managed identities for Azure resources. Using managed identities, can enhance the security of your application since you don't need to manage secrets yourself. Your app can now connect to Azure SQL Database without the need for a username or password. To build on these skills, read about How to Define Azure Role-based Access Control (RBAC).