8. Use native AAD authentication support with Microsoft.Data.SqlClient v3

While this part wasn't discussed during the stream because Microsoft.Data.SqlClient v3 wasn't release yet, it's too good an update to not mention it here.

The official .NET SQL driver, Microsoft.Data.SqlClient, now supports AAD authentication natively, meaning the responsibility of acquiring a token, attaching it to a connection, and dealing with caching renewing isn't ours anymore 🎉!
This means that we can delete a bunch of code (see the diff linked below), and focus on the application's business logic.

To opt in to use AAD authentication, we now have to use the Authentication keyword in our connection string.
The two values that we're most interested in are:

"Authentication" keyword value	Azure.Identity credential used behind the scenes	Typical scenario
Active Directory Managed Identity	ManagedIdentityCredential	When the app is running on Azure
Active Directory Default	DefaultAzureCredential	When running the app locally

Interesting links on the matter:

• Supported values for the "Authentication" keyword: https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15#setting-azure-active-directory-authentication.
• A post on my blog describing this solution: https://mderriey.com/2021/07/23/new-easy-way-to-use-aad-auth-with-azure-sql/.

Diff from previous tag: 7.leverage-interceptors-to-use-aad-auth-with-tokens...8-use-native-aad-auth-support-in-microsoft-data-sqlclient

7. Use EF Core interceptors to hook AAD authentication with tokens

The method we found to hook into the SqlConnection lifetime with EF Core is to use interceptors: https://docs.microsoft.com/ef/core/logging-events-diagnostics/interceptors.

In this case, we use a connection interceptor and inspect it just before EF Core opens it.
We need to override both the synchronous and asynchronous methods of the interceptor because EF Core will call one or the other depending on how we interact with your EF Core context:

• If we use .ToList(), .ToArray(), .Any(), the synchronous OnConnectionOpening method will be called on the interceptor.
• If we use .ToListAsync(), .CountAsync(), .ToDictionaryAsync(), the asynchronous OnConnectionOpeningAsync one will be invoked.

This means we have to add support for synchronous operations to our token acquisition process.
Luckily for us, Azure Identity and the in-memory cache support it, so it's a matter of adding another method.

Diff from previous tag: 6.ef-core-basic-implementation...7.leverage-interceptors-to-use-aad-auth-with-tokens

6. Using EF Core with a connection string

We now want to hit our SQL Server instance with EF Core.
The reason is that because EF Core manages the lifetime of the SqlConnection instances, by default we don't have a way to attach the token as we were doing with Dapper.

The first step is to get a basic implementation using EF Core to source the data displayed on the home page of our app.

Diff from previous tag: 5.introduce-token-caching...6.ef-core-basic-implementation

5. Introducing token caching

Something we need to be aware of when we consume Azure Identity ourselves (i.e. outside of the Azure SDK), is that many concerns are not taken care of for us, like token caching.

In our previous implementation, we were requesting a token (implying a network request) every time we load the page, which is far from ideal.

To remediate this, we take the following steps:

1. We create a new abstraction called IAzureSqlTokenProvider representing the process of acquiring a token for Azure SQL.
2. We create a base implementation that leverages Azure Identity, which is identical to what we had before in SqlConnectionFactory.
3. We introduce caching via the decorator pattern; to avoid network requests, we cache them in memory.
4. Finally, we use the excellent Scrutor NuGet package that adds support for decorators to the built-in dependency injection container.

Diff from previous tag: 4.using-sql-with-aad-auth...5.introduce-token-caching

4. Introducing AAD auth while using SQL Server

At this step, we call the Azure Identity library ourselves to get a token so we can attach it to the SQL connection.

Like we did for Azure Blob Storage, we use different providers to support both local development and running in Azure.

We also implement some logic to determine whether the app is currently using AAD authentication.
The one we landed on uses 2 tests:

1. Does the instance name contains database.windows.net?
2. Does the connection string not contain a username?

If both of these conditions are true, it means we're using AAD auth, hence acquiring and attaching the token.

The scope we request, https://database.windows.net//.default, comes from 2 places:

1. https://database.windows.net/ is the resource id of the AAD application representing Azure SQL as seen here: https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/services-support-managed-identities#azure-sql.
2. /.default is a special AAD scope when using the Microsoft identity platform endpoint, see https://docs.microsoft.com/en-us/azure/active-directory/develop/v2-permissions-and-consent#the-default-scope.

Diff from previous tag: 3.sql-with-connection-string...4.using-sql-with-aad-auth

3. Using SQL Server with a connection string

Now that we've seen how Azure Identity integrates with the Azure SDK, we see how we can do it with SQL Server.

As a first step, we create a basic implementation that uses Dapper to get the list of people displayed in the app.
We use Dapper as a first step because we're in control of the lifetime of the SqlConnection instances, which will make it easier to introduce AAD authentication later.

To get started, I recommend using the updated AzureIdentityLivestream.PeopleGenerator project:

• The create-person-table.sql file allows you to create the table with the expected schema quickly.
• Running the generator project will use the SqlConnectionString value in appsettings.json to create rows in the aforementioned table.

Diff from previous tag: 2.blob-storage-with-aad-auth...3.sql-with-connection-string

2. Using AAD authentication with Azure Blob Storage

We now leverage Azure Identity to use Azure AD authentication to connect to Blob Storage.
The 2 providers we register are:

1. The managed identity one, for when the app runs on Azure.
2. The VS Code one, which uses the account you're signed into with the Azure Account extension.

We also add some logic to support both local development and running in Azure, as we most likely don't want to use AAD auth when developing locally against Azurite:

• If the connection string is a valid absolute URI like https://<account-name>.blob.core.windows.net, we use AAD auth.
• Otherwise, we assume it's a regular connection string.

Diff from previous tag: 1-blob-storage-with-connection-string...2.blob-storage-with-aad-auth

1. Using Azure Blob Storage with a connection string

This is the starting point, where the app sources data from Azure Blob Storage using a connection string.

To get running, I recommend:

1. Setting up either Azurite or the Azure Storage Emulator.
2. Running the AzureIdentityLivestream.PeopleGenerator project to generate data in the blob container.