Using Audit.NET SqlDataProvider with Azure SQL and Managed Identity

Question

We are using the Audit.NET SqlServer Data Provider to store Audit logs in our Microsoft SQL Server. We are currently in the progress of migrating to the use of Azure SQL with Managed Identity to access the database. We haven't been able to get Audit.NET working with Azure SQL and the use of Managed Identity to connect to said database. The documentation doesn't provide any information on whether this functionality is supported or not.

We have managed to do this for our own database connections using Entity Framework Core by adding an Access Token to the SQL connection used by the Context like so:

SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.AccessToken = new AzureServiceTokenProvider()
          .GetAccessTokenAsync("https://database.windows.net/")
          .Result;

This works perfectly fine. The issue we are running into is that we want to achieve the same with the Audit.NET Sql Data Provider. Due to the AuditContext being used by the SqlDataProvider being internal we are unable to pass an Access Token to the SqlConnection used.

The only solution we've come up with is writing our own Data Provider that is virtually the same as the SqlDataProvider, the only difference being that the Context used will set an Access Token on the SqlConnection. Is this the only viable solution here or does Audit.NET offer some other way to get it working with Azure SQL and Managed Identity?

Answer 1

I think the best way could be exposing an optional setting to provide the DbContextOptions where you can set an Interceptor like the one from here to set the AccessToken for the connection.

So you could initialize your configuration like this:

Audit.Core.Configuration.Setup()
    .UseSqlServer(sql => sql
        .ConnectionString("connection string")
        .DbContextOptions(new DbContextOptionsBuilder()
            .AddInterceptors(new AzureAuthenticationInterceptor(new AzureServiceTokenProvider()))
            .Options));

or

Audit.Core.Configuration.Setup()
    .UseSqlServer(sql => sql
        .DbContextOptions(new DbContextOptionsBuilder()
            .UseSqlServer("connection string")
            .AddInterceptors(new AzureAuthenticationInterceptor(new AzureServiceTokenProvider()))
            .Options));

UPDATE

The new DbContextOptions settings was added on version 16.2.1