Asp.Net read/write to Azure data lake and Azure SQL Server

Question

I like to create web app to upload file, save to azure data lake, read/write to azure SQL Server.

I use my Azure AD clientId/secret to access data lake,

my Azure SQL Server connection string like: Server=tcp:{MyAzureSQLServer}.database.windows.net,1433;Initial Catalog={MyAzureDatabase};Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated"

Asp.Net Mvc Core, data lake works fine but Azure SQL report: Keyword not supported: 'authentication'.

Asp.Net Mvc (Framework), Azure SQL works, but data lake report error: The type initializer for 'Microsoft.IdentityModel.Clients.ActiveDirectory.TokenCache' threw an exception.

What I'm doing wrong?

Thanks, Wes

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-01-17T19:36:41

Use an access token (obtained via the Managed Identities) to use Azure Active Directory authentication in SQL client for .NET Core. Below all you need including connection string.

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;
    }
}

More about AAD authentication using access token here.

Asp.Net read/write to Azure data lake and Azure SQL Server

1 Answers