Connecting with Azure SQL Database using Azure Active Directory and Managed Identity in .NET Core

Steps to connect Azure SQL with Azure Active Directory

Create an Azure SQL Database
Configure it to use Azure AD by setting up an admin
Connect with SSMS to your database using Azure Active Directory - Universal with MFA authentication

For Local Development

Go to Visual Studio > Tools > Options > Azure Service Authentication (Login with your AD Account)

Run the below script to add your email id which is an Azure AD Identity as a user in SQL Server

CREATE USER [your.name@domain.com] FROM EXTERNAL PROVIDER; 
ALTER ROLE db_datareader ADD MEMBER [your.name@domain.com]; 
ALTER ROLE db_datawriter ADD MEMBER [your.name@domain.com]; 
ALTER ROLE db_ddladmin ADD MEMBER [your.name@domain.com]; 
GO

When App runs in Azure Context

Create an App Service and enable System Assigned Managed Identity

Run the below script to add your email id which is an Azure AD Identity as a user in SQL Server

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO

To get the Access Token for Azure AD Identity, install NuGet package - Microsoft.Azure.Services.AppAuthentication

Create a class like below:

public class AzureSqlAuthTokenService
{
    public string GetToken(string connectionString)
    {
        AzureServiceTokenProvider provider = new AzureServiceTokenProvider();
        var token = provider.GetAccessTokenAsync("https://database.windows.net/").Result;
        return token;
    }
}

Finally write the ADO.NET Code and set the AccessToken property.

using (SqlConnection con = new SqlConnection(_configuration.GetConnectionString("SQLConnectionString")))
{
    string connectionStringForToken_Local = "RunAs=Developer; DeveloperTool=VisualStudio";
    string connectionStringForToken_Azure = "RunAs=App";

    con.AccessToken = new AzureSqlAuthTokenService().GetToken(connectionStringForToken_Local);

    SqlCommand cmd = new SqlCommand("SELECT * FROM Customer", con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        customer.Add(rdr["CustomerName"].ToString());
    }
    con.Close();
}

Connecting with Azure SQL Database using Azure Active Directory and Managed Identity in .NET Core

References