How to connect ASP.NET MVC project in Visual Studio to Azure SQL database with password-less connection string

Question

I want to find a solution to connect to the Azure SQL database without passcode by utilizing the Azure Managed Identity.

I have done the setup on Azure based on the following document. However there are two issues.

https://learn.microsoft.com/en-us/samples/azure-samples/azure-sql-db-who-am-i/azure-sql-db-passwordless-connections/

1. I am using ASP.NET MVC on .NET 4.8, so I can't copy the code from the solution.
2. I also want to find the solution that once the code and setup is done, I do want test out the connection locally.

With some material/code here if you could also give some explanation that would be great.

Answer 1

I have created an ASP.NET MVC webapp in Visual Studio. I have given connection string in appsetting.json file as:

"ConnectionStrings": {
    "QuotesDatabase": "Server=tcp:<servename>.database.windows.net,1433; Database=<databasename>;" }

Screenshot for reference:

I added below packages to the project.

I added this code to connect to the Azure SQL database:

var connectionString = Configuration.GetConnectionString("<connectionstringname>");
            services.AddTransient(a =>{
                var sqlConnection = new SqlConnection(connectionString);
                var credential = new DefaultAzureCredential();
                var token = credential
                        .GetToken(new Azure.Core.TokenRequestContext(
                            new[] { "https://database.windows.net/.default" }));
                sqlConnection.AccessToken = token.Token;
                return sqlConnection;

Screenshot for reference:

I write this query to retrieve data from the Azure SQL database:

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace SqlMSI.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class QuotesController : ControllerBase
    {
        private readonly string connectionString;

        public QuotesController(SqlConnection dbconnection)
        {
            DbConnection = dbconnection;
        }

        public SqlConnection DbConnection { get; }
      
        public async Task<IActionResult> Get()
        {
            DbConnection.Open();
            var sqlCommand = new SqlCommand("select * from quote", DbConnection);
            var reader = sqlCommand.ExecuteReader();
            var quotes = new List<Quote>();

            while (reader.Read())
            {
                var quote = new Quote()
                {
                    Id = Guid.Parse(reader["Id"].ToString()),
                    QuoteNumber = int.Parse(reader["QuoteNumber"].ToString())
                };
                quotes.Add(quote);
            }

            return Ok(quotes);
        }
    }

    public class Quote
    {
        public Guid Id { get; set; }
        public int QuoteNumber { get; set; }
    }
}

Screenshot for reference:

I set Azure Service authentication to retrieve the token credentials.

Screenshot for reference:

I set myself as admin to the SQL Server.

Screenshot for reference:

I added client IP address to the sql server Image for reference:

It run successfully and connected to azure sql database and retrieve the data from database.

Image for reference:

I Published the project into Azure app services

Image for reference:

Add Ip address of webapp in azure to the sql server.

Set system assigned manage identity in on state of Azure app service.

I open SSMS and login the server using active directory password option.

I created user and added roles to the user using below code

create user [quotes-app] from external provider;
alter role db_datareader add member [quotes-app];
alter role db_datawriter add member [quotes-app];

image for reference:

successfully connected to the app without using userid password.

Image for reference: