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

ByAnkush Jain| Last Updated On Sunday, Dec 13, 2020

Azure Azure SQL Database SQL Server

Disclaimer: I am a consultant at Amazon Web Services, and this is my personal blog. The opinions expressed here are solely mine and do not reflect the views of Amazon Web Services (AWS). Any statements made should not be considered official endorsements or statements by AWS.

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 cloud

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 the 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();
}

References

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

References

Ankush JainSoftware Engineer

Recent Posts