How to connect to an Azure SQL Database from C# using Azure AD

In Microsoft.Data.SqlClient v3.0.0, a new authentication mode Active Directory Default has been released. Let's see what this means when querying an Azure SQL Database from some C# code.

🗨 If you do not have heard about Microsoft.Data.SqlClient, it is the new data provider for Microsoft SQL Server and Azure SQL Database which supports both .NET Framework and .NET Core and replace the old System.Data.SqlClient components.

But first let's talk about how we used to do that before.

The traditional way: using a secret connection string

The tradidional way to connect to an Azure SQL database from an application in C# is to provide to the SqlConnection constructor a connection string that contains a username and a password. The corresponding C# code is quite simple:

var connectionString = "Server=server-testingmsi6499.database.windows.net; Database=database-testingmsi6499;User ID=globalSqlAdmin;Password=MySecretPassword;");

using (var sqlConnection = new SqlConnection(connectionString));
await connection.OpenAsync();

In that case the connection string is a secret we must secure and not just by putting it in some configuration location everyone can have access to, but by storing it in a secured place like Azure Key Vault.

However, even if you secure it appropriately, using a connection string with username/password in it has some disadvantages:

• you need to handle who has access to it (so who has access to the key vault)
• every application or every developer could potentially use the same connection string so auditing is not very convenient (for instance identifying in the database logs which user has run a specific transaction)
• you only control who has access to the connection string in the key vault, not what people do with it (share it by email, store it on their local computer, ...) so not who can really access the database
• you need to handle the rotation of the secret, in other words change the username/password regularly (because you can really revoke the access to someone to the database, if he had access to the connection string at some point in time it is not a secret for him anymore)

For all these reasons, using a secret connection string to connect to an Azure SQL Database is not the right approach.

The new way: using Azure Active Directory Authentication

Instead of using a secret connection string to connect to a database, the idea is to use the Azure Active Directory authentication mechanism. Azure Active Directory is the location that contains all the identities of your users and your applications in your company. So you can manage directly which identity (user or application) have access to a database.

Applications or users that want to query a database will authenticate against Azure AD to retrieve an access token that will allow them to access the database using a connection string without any username nor password.

If you want to know more about the advantages of using Azure AD authentication for connecting to an Azure SQL Database you can have a look in the official documentation.

In the code we can remove the user id and password from the connection string but we have to retrieve an Azure AD access token and pass it to the SqlConnection instance:

var accessToken = await new DefaultAzureCredential().GetTokenAsync(new TokenRequestContext(new string[] { "https://database.windows.net//.default" }));
using var connection = new SqlConnection("Server=server-testingmsi6499.database.windows.net; Database=database-testingmsi6499;")
{
    AccessToken = accessToken.Token
};
await connection.OpenAsync();

The code is using the Azure Identity library which as the documentation says "provides Azure Active Directory token authentication support across the Azure SDK". It is the recommended way to get an Azure token although you may have seen code that uses another library Microsoft.Azure.Services.AppAuthentication to do the same thing. The class DefaultAzureCredential from Azure Identity library combines multiple authentication mechanisms (like Managed Identities, Visual Studio, Azure CLI ...) that will be tried in order to retrieve a token so it is practical class which can handle most of the scenarios.

Therefore, provided that you have granted access to your database to the user you are using locally (in Visual Studio, in vs code or in Azure CLI) and to the managed identity of your application in Azure (App Service or Azure Function for instance) the same code will work both locally and in Azure.

Here comes Active Directory Default authentication mode

We have seen that using Azure Active Directory Authentication was a better solution than using a connection string with secrets in it to connect to a database. However it involves manually retrieving an Azure AD token which makes the code a bit more complex to read. That is exactly why Active Directory Default new authentication mode was introduced in Microsoft.Data.SqlClient v3.0.0. Under the hood, SqlClient does the exact same thing that we were showing previously so we don't have to do it ourselves: just specifying the authentication mode to Active Directory Default in the connection string is enough to make it work.

using var connection = new SqlConnection("Server=server-testingmsi6499.database.windows.net; Authentication=ActivDirectory Default; Database=database-testingmsi6499;");
await connection.OpenAsync();

🗨 There are other Azure Active Directory authentication methods available, you can find them in the documentation here.

A complete example

Enough theory, what if you want to quickly test this by yourself ?

A bit of Azure CLI to initialize the database

I took an Azure CLI sample script from Microsoft and modify it a little to configure a database with all what is necessary to use Azure Active Directory to connect my user to it.

#!/bin/bash
location="West Europe" # to change with your preferred location
randomIdentifier=testingmsi${RANDOM:0:5}

resourceGroup="resource-$randomIdentifier"
server="server-$randomIdentifier"
database="database-$randomIdentifier"

login="globalSqlAdmin"
password="P@ssw0rdToChange!" # to change to have a more secured password

# Retrieve your public IP.
# Replace by your local machine IP if your are executing this script from cloud shell.
startIP=$(dig +short myip.opendns.com @resolver1.opendns.com)
endIP=$startIP

# Retrieve your current logged in user to be used as sql server admin. 
# Change with another user id if you want another user to be admin.
azureaduser=$(az ad signed-in-user show --query "objectId" -o tsv)

echo "Creating $resourceGroup..."
az group create --name $resourceGroup --location "$location"

echo "Creating $server in $location..."
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

echo "Configuring firewall..."
az sql server firewall-rule create --resource-group $resourceGroup --server $server -n AllowYourIp --start-ip-address $startIP --end-ip-address $endIP

echo "Creating $database on $server..."
az sql db create --resource-group $resourceGroup --server $server --name $database --sample-name AdventureWorksLT --service-objective Basic --zone-redundant false

echo "Creating AD admin in sql server..."
az sql server ad-admin create --resource-group $resourceGroup --server-name $server --display-name ADMIN --object-id $azureaduser

echo "Database connection string to use: \"Server=$server.database.windows.net; Authentication=Active Directory Default; Database=$database;\""

This script should be self explanatory if you have already played a little with Azure CLI. Basically, what it does is:

• create an azure sql server
• configure the server firewall to allow you to query it from your local IP address (if you are executing the script from cloud shell, replace startIP variable by your local machine IP)
• create an azure sql database with already tables and data in it from the sample AdventureWorksLT
• set you logged in azure ad user as the AD administrator of the database
• write in the console the connection string to use in your C# code to access the database

If you want to customize something do not hesitate to modify the scripts and especially variables like the resources location, the sql server user / password or the name of the resources. This is a bash script but if you want to execute it in PowerShell, all the Azure CLI commands should work fine, you just have to change the variables declarations as the syntax is different in PowerShell. If you don't have Azure CLI installed on your laptop you can use Azure Cloud Shell to execute this script.

🗨 If you are new to Azure CLI, you can read my article Good bye Azure Portal, Welcome Azure CLI.

Querying the database from a minima API in C

Usually I like to create a console application (with the worker service template for instance) for my samples, yet this time I decided to try the new minimal APIs from .NET 6 (currently in preview).

Minimal APIs would probably deserve an entire blog post, but let's just say a minimal API in .NET 6 allow you to build a small HTTP API with less ceremony that a classic controller-based API. As all the code can be written in a Program.cs file, so it's very convenient when you want to quickly build a web application without too much complexity (especially if you are new to ASP.NET Core) or if you are developing a small microservice.

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Dapper;
using Microsoft.AspNetCore.Builder;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Hosting;

var builder = WebApplication.CreateBuilder(args);
var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseDeveloperExceptionPage();
}

app.MapGet("/", (Func<Task<IEnumerable<Product>>>)(async () =>
{
    using var connection = new SqlConnection("Server=server-testingmsi6499.database.windows.net; Authentication=Active Directory Default; Database=database-testingmsi6499;");
    var products = await connection.QueryAsync<Product>("SELECT TOP 10 ProductID, Name from [SalesLT].[Product]");
    return products;
}));

app.Run();

public record Product(int ProductID, string Name);

As you can see this code is only 26 lines long:

• there is only one route, that returns the Product identifiers and names from the table [SalesLT].[Product] of the database created with the previous Azure CLI script
• the SQL query is done by using the micro ORM Dapper which really simplifies the boilerplate code to query an sql database while keeping performance
• the result of the SQL query is mapped to a record class Product which is declared in one line
• the code uses Microsoft.Data.SqlClient v3.0.0 with the Active Directory Default authentication mode

🗨 To keep things simple, I am connecting to the database with the my Azure AD account which is admin of the sql server. But I could also have assigned a role with lower permissions to my account, see here for more information on how to do that.

To conclude

While building an application interacting with Azure we often neglect to use mechanisms like Azure AD authentication that remove the need for secrets. But as we have seen in this article some libraries like Microsoft.Data.Sql.Client or the Azure SDKs allow us to do that quite easily. I love how connecting to an Azure SQL Database in C# is becoming more simple and more secure at the same time.