How to add Azure MSI AccessToken into EF6 DbContext to connect Azure SQL PaaS

Question

With MVC & EF6 Code first approach I am able to integrate Azure MSI token & performing CRUD operation but how do I perform migration for which I have to inject token into DBContext:

Connection String:

For peforming CRUD query I use legacy ADO.NET style query as below and it works:

Getting MSI : In order to run migration, how do I pass Azure MSI AccessToken into DbContext contructor.

For dbcontext i have to define separate connection string with provider name.

Don't take screenshots of your code. Copy past it to your question. See here way: https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question — H. Pauwelyn, Apr 29 '20 at 15:57

score 2 · Answer 1 · answered Apr 29 '20 at 15:49

You need to setup in your DI:

Startup.cs

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

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

DbContext

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

    public AzureProvider(IConfiguration configuration, IDBAuthTokenService tokenService, DbContextOptions<AzureProvider> 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);
    }
}


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

        return token;
    }
}

EF Core Connection to Azure SQL with Managed Identity

Hi Thiago, Thank you for the solution, what I am using if EF6 not EF COre and EF6 do not have OnConfiguring() method to override — Asif Iqbal, Apr 29 '20 at 16:58
sorry about that. Please take a look if the following link helps you: https://stackoverflow.com/q/49531754/1384539 — Thiago Custodio, Apr 29 '20 at 17:42

score 1 · Answer 2 · answered Jul 21 '21 at 20:39

This is how I did AAD token authentication for Azure SQL using Entity Framework 6 and .NET Framework for anyone that comes across this question.

public class MyContext : DbContext
{
    public MyContext() : base(CustomAzureSQLAuthProvider.GetTokenConnection("MyConnectionStringName"), true)
    { 
    }
   
    //...
}

public class CustomAzureSQLAuthProvider
{
    private static readonly string[] azureSqlScopes = new[]
    {
        "https://database.windows.net//.default"
    };

    private static readonly TokenCredential credential = new DefaultAzureCredential();

    public static DbConnection GetTokenConnection(string connectionStringName)
    {
        var connectionStringSettings = ConfigurationManager.ConnectionStrings[connectionStringName];

        var dbConnection = DbProviderFactories
            .GetFactory(connectionStringSettings.ProviderName)
            .CreateConnection();
        dbConnection.ConnectionString = connectionStringSettings.ConnectionString;

        var tokenRequestContext = new TokenRequestContext(azureSqlScopes);
        var tokenResult = credential.GetToken(tokenRequestContext, default);
        SqlConnection sqlConnection = dbConnection as SqlConnection;
        sqlConnection.AccessToken = tokenResult.Token;
        return sqlConnection;
    }
}

Note: I had to use the non-async GetToken function. It did not work with GetTokenAsync.

How to add Azure MSI AccessToken into EF6 DbContext to connect Azure SQL PaaS

2 Answers2