Passwordless connection string to Azure SQL database from .NET Core API

5 min readApr 29, 2020

How do you store a database connection string in your Azure application? Hardcoded in a config file? Or perhaps in Application settings? Key Vault? For all these scenarios, you need to store a user login/password or at least a secret to your Key Vault.

In this post, I’ll show you how to implement a “passwordless connection string” with a managed identity in Azure.

What is a managed identity?

It is just an identity assigned to a service in the Azure cloud. It is stored in your Azure Active Directory.

A service with an enabled managed identity will use locally available endpoint, which is used by this service to retrieve a token from the Azure Active Directory. This is then used to access other Azure services (such as Azure SQL database).

You don’t need to store any keys, tokens, secrets, or passwords in your app. The whole process is handled internally by Azure.

The process looks as follows:

Here — https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities — you can check which Azure services support a managed identity.

How to implement a passwordless connection string in .NET Core application?

You can implement a passwordless connection string with these 3 steps:

enabling a managed identity in Azure Webapp
configuring Azure SQL
implementing small changes in your application

On twitter (and my other social media) I’m sharing interesting (in my opinion ;)) stuff about Cloud, DevOps, Software Development and Technical Leadership.

Enable a managed identity in Azure Webapp

In the Azure portal, navigate to your web application.
Select Settings | Identity from the menu.
Change the identity status to On and click Save.

Done! You have just enabled a managed identity for your web application.

Configure Azure SQL to use web app identity

First, you need to set up Active Directory admin for your SQL server. Without this, you won’t be able to add a managed identity created for the web app as you will see the following error:

Principal ‘lgmidemo’ could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

In the Azure portal open SQL server settings (server, not database).
Select Settings | Active Directory admin from the menu.
Click a “Set admin” button, find your user and save it as an admin 🙂

Now, open Microsoft SQL Server Management Studio and login to your database.

In the Connect to Server window, provide your server name.
For Authentication, select Azure Active Directory — Universal with MFA. Log in with the account you have added as AD admin for SQL Server (you will provide the password in another window from your Azure AD).

Once connected, open a new query and run the following code:

CREATE USER lgmidemo FROM EXTERNAL PROVIDER 
ALTER ROLE db_owner ADD MEMBER lgmidemo

lgmidemo is the name of your managed identity (web app) that you created earlier
db_owner is the role you would like to assign to this web app

That’s all 🙂

Configure the .NET Core application to use a managed identity

First set your passwordless connection string:

"SqlConnectionString": "Data Source=<YOUR SQL SERVER>.database.windows.net; Initial Catalog=<YOUR SQL DATABASE>;"

In my case, it is:

"SqlConnectionString": "Data Source=lgmidemosql.database.windows.net; Initial Catalog=testdb;"

Now, let’s retrieve an access code from the managed identity endpoint.
We will use AzureServiceTokenProvider class from Microsoft.Azure.Services.AppAuthentication NuGet package.

To make it easier, I’ve created DbConnectionInterceptor for Entity Framework connection:

public class MiConnectionInterceptor : DbConnectionInterceptor
{
    private const string AzureSqlResourceId = "https://database.windows.net/";
    private readonly string _tenantId;
    private readonly AzureServiceTokenProvider _tokenProvider;

    public MiConnectionInterceptor(string tenantId)
    {
        _tenantId = string.IsNullOrEmpty(tenantId) ? null : tenantId;
        _tokenProvider = new AzureServiceTokenProvider();
    }

    public override async Task<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        var sqlConnection = (SqlConnection)connection;
        sqlConnection.AccessToken = await GetAccessTokenAsync();

        return result;
    }

    private async Task<string> GetAccessTokenAsync()
    {
        return await _tokenProvider.GetAccessTokenAsync(AzureSqlResourceId, _tenantId);
    }
}

The most important piece of code here is the GetAccessTokenAsync() method which retrieves a token for connecting Azure SQL ( AzureSqlResourceId).

var managedIdentityInterceptor = new MiConnectionInterceptor(appSettings.ManagedIdentityTenantId);
services.AddDbContext<MiDbContext>(o =>
    o.UseSqlServer(appSettings.SqlConnectionString).AddInterceptors(managedIdentityInterceptor));

The complete code can be found in my GitHub repository — https://github.com/lukaszgasior/passwordless-azure-db

Once you deploy your application to the Azure website, your application will be able to connect the Azure SQL database. Without providing any passwords!

Run solution locally

But, how to run this locally?

You can add your Active Directory account to the SQL database the same way you added the Web application identity (with a SQL query). You don’t need to do anything if you are using the account you’ve provided earlier as an Active Directory admin.

If you are logged in for this account in Visual Studio you will be able to access your SQL database from your PC.
You can check if you are using the proper account in the Visual Studio settings — Tools | Options | Azure Service Authentication:

In order to run this solution locally, it is good to provide your Azure AD tenant to make sure you will use the right Active Directory. In my sample code, you can use ManagedIdentityTenantId variable for this purpose.

What do you think of this solution?
How do you provide connection strings in your applications?

Do you like this post? Share it with your friends!
You can also subscribe to my RSS channel for future posts.

Originally published at https://gasior.net.pl on April 29, 2020.