Passwordless connection string to Azure SQL database using Azure managed identity

Hallstein Brøtan -

Traditionally, the connection string to the Azure SQL database contains both username and password. Even if you store these credentials in KeyVault, a password needs maintenance/rotation and it might end up in the wrong hands (for instance if sloppy developers keep the password on their development computers).

This blog post will show you how an app can connect to the database without any credentials, using AAD authentication and Managed Identity. It's easier than you may think!

The setup:
My Azure subscription contains the following resources:

  • An app called "my-app"
  • An Azure SQL database called "my-database" on the server "my-sql-server".

I will demonstrate how this app can connect to the database in 5 simple steps.

1. Set an "Azure Active Directory Admin" for the database server.

First up, you need to set an AAD admin for the SQL server. For easier management and flexibility, I recommend it to be an AD group, so I've created a group called grp-sqladmin and added myself as member. The users in this group will get the db_owner role for all databases on the server, so this should be a highly limited group. The main purpose of the AAD admin is to create database specific roles, but we'll come to that.

2. Create a managed identity

It doesn't matter if you use system assigned or user assigned identity. For this example, I will create a system assigned identity for my app.

3. Create external database roles

Passwordless or not, you need a user in the database.

Enable authentication to the database by creating a contained user. This can only be done by entities in the AD group set as AAD admin in step 1.

NB! When using System assigned identity, the contained user must have the same name as the app. When using User assigned identity, the contained user must have the same name as the managed identity.

This script creates the contained user and grants read and write permissions to the specific database table: 

CREATE USER [my-app] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-app];
ALTER ROLE db_datawriter ADD MEMBER [my-app];

4. Install Microsoft.Data.SqlClient v. 3.0.0

Microsoft.Data.SqlClient is the official and recommended driver for SQL Server. Version 3.0.0 was released in June 2021, and it leverages Azure.Identity.

The driver takes care of all the magic happening.  It acquires an access token from AAD, attaches it to the SQL connection, and handles token caching and renewal. No need for any custom code at all.

When an application is connecting to an Azure SQL database using AAD authentication, the database connection string must specify an Authentication keyword. For my example, there are two relevant authentication modes, with description from the Microsoft docs:

  • Active Directory Managed Identity
    Authenticate with an Azure AD identity by using system-assigned or user-assigned managed identity. For apps running on Azure.
  • Active Directory Default
    Authenticate with an Azure AD identity by using password-less and non-interactive mechanisms including Managed Identities, Visual Studio Code, Visual Studio, Azure CLI, etc. For local development, deploy/migrations etc.

5. Change your connection string

Say hello to your new connection string:

Server=my-sql-server.database.windows.net,1433;Database=my-database;Authentication=Active Directory Default
Look ma, no password!

The database connection string contains no secret anymore, and can reside in the appsettings, varying by environment.

And that's it!


Hey, wait a second.. what about migrations?
The process (service principal) running the database migration should be in the grp-sqladmin (see step 1). Then it will have permissions to run migrations, and also the script defined in step 3.

Update 16.sept 2021:
You might encounter the error message Server identity does not have Azure Active Directory Readers permission when running the script for creating database user.

Steps to solve this:

  1. Create a system assigned identity for your Azure SQL server (if not already present).
    az sql server create -g myResourceGroup -n myServer --assign-identity -i
  2. Either assign the role Directory readers to the SQL server, or create an AD group with that role, and put alle the SQL servers in it.

Ref: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal


And.. one more thing, what about local development?
For local development the app is not running under a managed identity. Instead, the developers needs to authenticate to AD explicitly and also have a contained user in the database.

  • Create a user in the database using the script from step 3, either with the same name as the developer AD account, or (more flexible) create a new AD group and create a user in the database with the same name as the AD group. Add the developer to the AD group.
  • Use the connection string from step 5 with Authentication mode = Active Directory Default.
    Perform az login to authenticate with AD, and your locally running app should be able to connect to the database.

Last thing, how about connecting with tools like SSMS or VS Code?
If using SSMS or VS Code to access the database, instead of SQL Server Authentication, choose Azure Active Directory - Universal with MFA. An Azure login prompt will appear, and a successful login will grant access to the database:


Update: Error messages you might encounter

Login failed for user ''.
1. Is the database user the same name as the managed identity?
2. If you get this from the pipeline, did you remember to az login first?

System.ArgumentException: Invalid value for key 'authentication'.
The connection string is unrecognized. Did you remember to do step 4 - install Microsoft.Data.SqlClient?