EF Core Connection to Azure SQL with Managed Identity

azure-active-directory azure-sql-database ef-core-2.2 entity-framework-core

Question

I am using EF Core to connect to a Azure SQL Database deployed to Azure App Services. I am using an access token (obtained via the Managed Identities) to connect to Azure SQL database.

Here is how I am doing that:

Startup.cs:

public void ConfigureServices(IServiceCollection services)
{
    //code ignored for simplicity
    services.AddDbContext<MyCustomDBContext>();

    services.AddTransient<IDBAuthTokenService, AzureSqlAuthTokenService>();
}

MyCustomDBContext.cs

public partial class MyCustomDBContext : DbContext
{
    public IConfiguration Configuration { get; }
    public IDBAuthTokenService authTokenService { get; set; }

    public CortexContext(IConfiguration configuration, IDBAuthTokenService tokenService, DbContextOptions<MyCustomDBContext> options)
        : base(options)
    {
        Configuration = configuration;
        authTokenService = tokenService;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = Configuration.GetConnectionString("defaultConnection");
        connection.AccessToken = authTokenService.GetToken().Result;

        optionsBuilder.UseSqlServer(connection);
    }
}

AzureSqlAuthTokenService.cs

public class AzureSqlAuthTokenService : IDBAuthTokenService
{
    public async Task<string> GetToken()
    {
        AzureServiceTokenProvider provider = new AzureServiceTokenProvider();
        var token = await provider.GetAccessTokenAsync("https://database.windows.net/");

        return token;
    }
}

This works fine and I can get data from the database. But I am not sure if this is the right way to do it.

My questions:

  1. Is this a right way to do it or will it have issues with performance?
  2. Do I need to worry about token expiration? I am not caching the token as of now.
  3. Does EF Core has any better way to handle this?
1
20
1/14/2019 6:32:22 PM

Accepted Answer

Is this a right way to do it or will it have issues with performance?

That is the right way. OnConfiguring is called for each new DbContext, so assuming you don't have any long-lived DbContext instances, this is the right pattern.

Do I need to worry about token expiration? I am not caching the token as of now.

AzureServiceTokenProvider takes care of caching.

Does EF Core has any better way to handle this?

Setting the SqlConnection.AccessToken is currently the only way of using AAD Auth in SqlClient for .NET Core.

11
1/15/2019 2:54:16 AM

Popular Answer

For developers using .NET Framework for Managed Identity, the below code might be helpful for getting the entity connection:

app.config:

<add key="ResourceId" value="https://database.windows.net/" />
<add key="Con" value="data source=tcp:sampledbserver.database.windows.net,1433;initial catalog=sampledb;MultipleActiveResultSets=True;Connect Timeout=30;" />

c# file

using System;
using System.Configuration;
using System.Data.Entity.Core.EntityClient;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Azure.Services.AppAuthentication;

public static EntityConnection GetEntityConnectionString()
{
    MetadataWorkspace workspace = new MetadataWorkspace(
       new string[] { "res://*/" },
       new Assembly[] { Assembly.GetExecutingAssembly() });

    SqlConnection sqlConnection = new SqlConnection(Con);

    var result = (new AzureServiceTokenProvider()).GetAccessTokenAsync(ResourceId).Result;

    sqlConnection.AccessToken = result ?? throw new InvalidOperationException("Failed to obtain the access token");

    EntityConnection entityConnection = new EntityConnection(
        workspace,
        sqlConnection);

    return entityConnection;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow