Connecting ASP.NET Core Web API to Azure SQL Server via Managed Identity

Question

I am able to log in via azure data studios. I have went through multiple tutorials and cannot get my ASP.NET Core code to work to log into the db from the app service or locally. I get "Login failed for user ''."

The steps I have taken. My app service for my web api is set to managed identity. I have added this identity to grp-sqladmin. I have created an app service for a sql database then set managed identity to yes. I have set the admin for the sql db to grp-sqladmin in active directory of the sql database server. I have then added the managed identity to the sql database as a user using the following:

CREATE USER [WebAPI] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [WebAPI];
ALTER ROLE db_datawriter ADD MEMBER [WebAPI];
ALTER ROLE db_ddladmin ADD MEMBER [WebAPI];
GO

I have then made my connection string in my web api :

  },
  "ConnectionStrings": {
    "AzureSqlConnection": "Server=tcp:blahblah.database.windows.net;Database=My DB; Authentication = Active Directory Default;"
  },

I draw an error in my dbContext getting the access token:

    public class DbContext
{
    private readonly IConfiguration _configuration;
    private readonly string? _connectionString;


    public DbContext(IConfiguration configuration)
    {
        _configuration = configuration;
        _connectionString = _configuration.GetConnectionString("AzureSqlConnection");

    }
    public IDbConnection CreateConnection()
    {
        var conn = new SqlConnection(_connectionString);
        var credential = new Azure.Identity.DefaultAzureCredential();
        var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
        conn.AccessToken = token.Token;

        return conn;
    }
}

When i set the access token for the connection it states: "Cannot set the AccessToken property if 'Authentication' has been specified in the connection string."

Then if I go to remove that authentication=Active Directory Default the token is generated and assigned to the connection but when I run my query I get the error: "Login failed for user ''."

I have wasted hours trying to figure out why this is not working. Any ideas? The tutorial I went by was this: https://learn.microsoft.com/en-us/azure/app-service/tutorial-connect-msi-sql-database?tabs=windowsclient%2Cef%2Cdotnet

    public class CalendarRepository : ICalendarRepository
{
    private readonly DbContext _context;
    public CalendarRepository(DbContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<CalendarEvent>?> GetCalendarEvents()
    {
        try
        {
            var query = "SELECT * FROM CALENDAR";
            using (var connection = _context.CreateConnection())
            {
                var events = await connection.QueryAsync<CalendarEvent>(query);
                return events.ToList();
            }

        }
        catch(Exception ex)
        {

            return null;
        }
    }

}

However, I have watched a few youtube videos and some users do add the authentication item at the end of their connection string so this is confusing. Not sure if this is the primary issue also.

I use managed identies for MS Graph API and Azure key vaults and love it so I would like to integrate this SQL DB stuff into identities as well. I am using system assigned identity as well if that matters.

I do az login in cli and i login using my account. Which I have also included in the grp-sqlamin group so it should all be connected i would think.

Can you adapt your SqlConnection accordingly to this documentation? It seems you are trying to configure things yourself that should be done automatically. — Silvan
– Silvan, Commented Jun 28, 2023 at 20:30
@silvan are you referring to me getting the token in CreateConnection? Do I not need to get an access token manually in dev environment ? — Qiuzman
– Qiuzman, Commented Jun 29, 2023 at 0:58
Turns out my issue was the user for the db needs to be the name of my app registration not the web app (service app). I’m confused which is actually the identity? — Qiuzman
– Qiuzman, Commented Jun 29, 2023 at 1:00
Wow you are right i do not have to get the token manually. It does it automaticaly with envionment variables. Just curious, since the sql client handles everything. Do I even need a DbContext model? This seems like a waste whenn I can just use the using(var connection = new sqlConneciton(_connectionstring)) in my repository directly rather than reference dbcontext. — Qiuzman
– Qiuzman, Commented Jun 29, 2023 at 1:20

Bhavani · Accepted Answer · 2023-06-29 06:45:09Z

0

To connect Azure sql database from web API through system assigned managed identity authentication mention the connection string in below format in Appsetting.json:

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

Use below code for connection.

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;

enter image description here

set admin for sql server as you want.

enter image description here

choose administrator account for azure service authentication to retrieve the token credentials.

Image for reference:

enter image description here

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

enter image description here

Login to sql server with administrator add user to the database and assign role to the user

create user [<appName>] from external provider;
alter role db_datareader add member [<appName>];
alter role db_datawriter add member [<appName>];

enter image description here

The database successfully connected to the app.

Image for reference:

enter image description here

answered Jun 29, 2023 at 6:45

Bhavani

5,4762 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Qiuzman Over a year ago

Thank you for this answer: Based on my experimentation you don’t need to manually get the token like you did. Ms sql client does it for you with the latest version. It gets it from local environment variables. It does this in development and production.

Qiuzman Over a year ago

Also I see you didn’t check azure Active Directory authentication only in your second pic. Isn’t this less secure? Also for access configurstion is it set to open to public connections?

Collectives™ on Stack Overflow

Connecting ASP.NET Core Web API to Azure SQL Server via Managed Identity

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related