Efcore: Using EF Core with IoC and SqlConnection.AccessToken

[cbrianball]

This is likely a case of my not understanding how to use the library correctly, but after many searches, I have not found an answer.

I am planning to leverage the Managed Service Identity (MSI) in Azure app services. In order to authenticate against SQL Azure, I need to acquire an access token and set it on the SqlConnection object.

I am calling IServiceCollection.AddDbContext<>() and passing in a action to configure my DbContext using the DbContextOptionsBuilder that is passed into the action method.

There is an overload on DbContextOptionsBuilder.UseSqlServer to pass in a DbConnection object. This appeared to work fine at first, but then when I started getting exceptions about the SqlConnection being in an open state when I started to put the application under load.

Upon further inspection, it looks like the action method I pass into IServiceCollection.AddDbContext<>() is only called a single time. That leads me to believe the same options object is used to create all DbContext instances. Given that I passed in a DbConnection object, I assume that EF is reusing the same DbConnection object for ALL contexts, which certainly explains the problem I'm having.

Where is the proper "hook" to allow the DbContext class to manage the lifetime of the DbConnection object, but to allow me to set properties on the DbConnection class after it is first created? I'm not worried about my access token expiring for a given instance of the DbConnection class, but I also want to make sure that whenever a new DbConnection object class is created, that is has an access token that isn't about to expire.

Steps to reproduce

Use the DbContextOptionsBuilder to pass in a DbConnection object with the UseSqlServer extension method, then create multiple instances and notice they all have the same DbConnection object.

Further technical details

EF Core version: 1.1.5
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: VS 2017

[ajcvickers]

@cbrianball This should work correctly in 2.0 and later because the DbContextOptions is registered as scoped by default. I suspect that, if you can't update to 2.0, then manually doing the D.I. registration in the app to register the options as scoped should work.

[cbrianball]

Ok. I started your second suggestion earlier, but noticed in the source code that a number of services are registered in the helper methods. I'd rather not copy the code into my library, but if that's the easiest option, I'll do that. Ultimately I'll upgrade to 2.0, but I'm not ready for that at this time. Thanks.

[cbrianball]

For anyone else who runs across this: I was using an interface type on my constructors (and not the concrete DbContext class), so I registered using AddDbContext with the concrete type (and pass in the connection string), then registered my interface with an implementation factory, and in that factory, I get an instance of the concrete DbContext class, then I use the GetDbConnection extension method on the Database property of the concrete class to get the DbConnection, from there I cast it and set the AccessToken property.

This way, EF manages the lifecycle of the DbConnection class, and I can be sure that any newly created DbConnection object will have an access token with a reasonable amount of time left before it expires.

[ajcvickers]

@cbrianball That sounds like a good approach. Thanks for sharing!

[jamesecwalker]

@cbrianball How did you register the AccessToken on the SqlConnection in EF Core? I didn't think this was possible yet? Could you share here if poss? Thanks

[cbrianball]

@jamesecwalker I'm not sure I understand your question, are you asking how to set the access token on the SqlConnection object, or how to acquire an access token?

To set the token, the SqlConnection object has a property called AccessToken. The DbConnection you get from the GetDbConnection() extension method can be cast to SqlConnection (assuming you've configured your context to connect to SQL Server). The AccessToken property was added in version 4.6 of the .NET Framework.

If you are asking how to acquire the token, I am using the AzureServiceTokenProvider class in the Microsoft.Azure.Services.AppAuthentication NuGet package to get the token.

[jamesecwalker]

@cbrianball Thanks for getting back to me. Ah yeah I know how to do this in "classic" EF - just thought you'd found a work around for EF Core? The AccessToken property doesn't exist on the SqlConnection object in EF Core - unless I've missed something?

[cbrianball]

@jamesecwalker Sounds like you are using .NET Core -- that doesn't currently support this feature. I am using EF Core (and ASP.NET Core), but I am running them on the .NET Framework (not .NET Core) -- so I am able to get AD Authentication against SQL Azure to work.

The AccessToken property does not exist on the SqlConnection object in .NET Standard, but it does exists in .NET Framework v4.6+. That's the beauty of .NET Standard, if you write your library code against that, then applications that use .NET Framework AND .NET Core can consume your library and take advantage of the applicable framework.

Begin soapbox:

At this time, unless I need to run my application cross-platform, I still default to .NET Framework, because I always run into an issue where a feature isn't available in .NET Core or a 3rd party library I need to use targets the .NET Framework (and not .NET Standard), but I love the fact that major frameworks such as EF Core and ASP.NET Core target .NET Standard so that I am able to use them while still running against the .NET Framework.

[jamesecwalker]

@cbrianball Thanks! Hmmm interesting, I'm targetting .NET Standard 2.0 at the moment as I thought it would be the best approach to 'future' proof myself on this project whilst being able to use it in some legacy bits of code too. I also forgot that SqlConnection is part of ADO and not EF itself so was thinking in my head that this was an EF Core issue rather than the scope of .NET Standard which presumably doesn't include this AccessToken property.

Makes a lot more sense now though, but need to think whether I want to pull back to .NET Framework on the project. Thanks for the help!

[cbrianball]

@jamesecwalker This is my personal opinion as there are always multiple ways to solve a problem:

If you are writing a reusable library, I would try to continue to target .NET Standard and leave some way for the consumers of your library to add the functionality as needed (a simple solution would be to take an Action<> as a parameter to one of your methods).

My rule of thumb for library code is to target .NET Standard whenever possible, but my executables (e.g. console applications, web applications, etc.) will use either .NET Framework or .NET Core depending on the needs of the application. There are exceptions, as is always the case when it comes to real-world scenarios, but I try to stick to this rule whenever reasonable.

[jamesecwalker]

@cbrianball Why didn't I think of that! Have passed in the SqlConnection object into my Standard library from my Framework code now and the AccessToken works a dream.
Takeaway for me is to think of Standard as just a common interface between Framework and Core.
Thanks for your help!

[lesley86]

@jamesecwalker Can you explain what you meant. I have 3 projects .net standard library (2.0), .netCore (2.1) with EF.Core and .netFramework library (4.7.2). How do i pass in the sql connection from the .netFramework project to be used by the .netCore project.

My .netStandard library is referenced by both the .netCore and .netFramework projects. If i try add a reference from to the .netFramework project to the .netStandard project I get an incompatible error.

I essentially need to add the access token and sql Column Encryption provider to be used by EF.Core migrations..

Your comment made it seem like you figured out how to add the access token to a netCore project? by using the connection passed in from a .netframework project? Is that correct?

If so how did you reference the associated projects without getting an incompatible error? or did you change your .netCore project to target .netFramework?

thanks in advance

@cbrianball Why didn't I think of that! Have passed in the SqlConnection object into my Standard library from my Framework code now and the AccessToken works a dream.
Takeaway for me is to think of Standard as just a common interface between Framework and Core.
Thanks for your help!

[cbrianball]

@lesley86 Does the project with EF.Core need to be .netCore? Assuming it's a class library, you are generally better off making it a .net standard library as you will open up the number of platform/frameworks it can operate on significantly.

Writing a library against .net Standard is basically saying "I don't care which framework I run on, as long as that framework supports the version of .net standard I require".

If your library is referenced by a .net core process, then it will use the SqlConnection object defined by .net core which does NOT support AccessToken (currently).

If your library is referenced by a .net framework process, then it will use the SqlConnection object defined by .net framework, which does (assuming you are using a recent enough version) AccessToken on SqlConnection.

Since .net standard doesn't know if AccessToken exists or not, you need to provide a way to pass the SqlConnection object such that it can be configured by a library/executable that is tied to a framework that does have AccessToken. The easiest way to do this is to provide a way to pass in an Action type into your .net standard library and have the .net standard library invoke that action method whenever you need to set an AccessToken.

Example (I'm not suggesting this is the best possible way to handle this, but it's a short example):

.net standard library with EF.Core
````
public class MyDbContext : DbContext
{
public static Action ConfigureConnection;

}
````

.net framework library/executable

public static int Main() { MyDbContext.ConfigureConnection = (connection) => { <set connection.AccessToken here> } }

[mgolois]

For anyone else who runs across this: I was using an interface type on my constructors (and not the concrete DbContext class), so I registered using AddDbContext with the concrete type (and pass in the connection string), then registered my interface with an implementation factory, and in that factory, I get an instance of the concrete DbContext class, then I use the GetDbConnection extension method on the Database property of the concrete class to get the DbConnection, from there I cast it and set the AccessToken property.

This way, EF manages the lifecycle of the DbConnection class, and I can be sure that any newly created DbConnection object will have an access token with a reasonable amount of time left before it expires.

@cbrianball, can your implementation be shared on github?

[cbrianball]

@mgolois
I don't have an easy code sample to point you to, so I'll reproduce it here the best that I can. In the below example services is an instance of IServiceCollection and this code would be called from ConfigureServices in your Startup class.

MyDbContext is your database context. I have it implement IMyDbContext, which exposes all the property/methods you want exposed to the rest of your application (this also gives you an opportunity to 'shield' parts of your database from the rest of your application, if you'd like.

Whenever I have a class that needs to access the database, I add IMyDbContext to the constructor of that class.

using Microsoft.EntityFrameworkCore;

services.AddDbContext<MyDbContext>(builder => builder.UseSqlServer(connectionString));

services.AddScoped<IMyDbContext>(serviceProvider => {
  var dbContext = serviceProvider.GetRequiredService<MyDbContext>();  
  var connection = dbContext.Database.GetDbConnection() as System.Data.SqlClient.SqlConnection;
  if(connection == null) {/*either return dbContext or throw exception, depending on your requirements*/}
  connection.AccessToken = //code used to acquire an access token;
  return dbContext;
});

Note: Ensure that both MyDbContext and IMyDbContext have the same Lifetime Scope (e.g., transient, scoped, etc.).

[mgolois]

@cbrianball sounds good. Thanks!!!

[mgolois]

@cbrianball @divega after further thinking, I implemented it this way, do you see you anything wrong with it?

services.AddDbContext<MyDBContext>(options => {
                SqlConnection conn = new SqlConnection(Configuration["ConnectionString"]);
                conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;
                options.UseSqlServer(conn);
            });

[ajcvickers]

@mgolois The problem with that approach is that your SqlConnection will never be disposed. If you create it, then you must dispose it.

[mgolois]

@ajcvickers ah! I see! Thank you!!!

[mgolois]

@ajcvickers updated implementation:

public class MyDbContext : DbContext
    {
        public MyDbContext(DbContextOptions options) : base(options)
        {
            var conn =  (SqlConnection) this.Database.GetDbConnection();
            conn.AccessToken = (newAzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;
        }
        public DbSet<Item> Items { get; set; }
    }

//Startup.cs
services.AddDbContext<ToDoDbContext>(options =>
{
    var connStr = Configuration["ConnectionString"];
    options.UseSqlServer(connStr);
});

[cbrianball]

Update: The NuGet package System.Data.SqlClient v4.6.0 contains the System.Data.SqlClient.SqlConnection class which has the AccessToken property. This property is not supported by all runtimes, but it is supported in .NETStandard 2.0!

[jonarmstrong]

Just to chime in on this. I went with the approach put forth by @mgolois. It was effective, but it required quite a bit of effort to get it baked into the standard db scaffolding process with the tools. I ended up writing a powershell script to handle it. The basic idea is the dotnet ef dbcontext scaffold command generates the constructor which is where the modifications to the connection string need to happen. This has a few niceties to it, it will only get the access token if the connection string isn't attempting to use another authentication method. It'll also comment out the automatically generated OnConfiguring method which could further modify the connection string & prevents users from providing their own implementation of that virtual method in a partial class. Finally, the indentation of the @ string looks a little funny so it'll match up with the braces emitted by the tooling. I hope this helps someone.

# .\GenerateModels.ps1

&{
    $ErrorActionPreference = "Stop"
    $root = Join-Path -Path '.' -ChildPath '' -Resolve

    if($PSScriptRoot) {
        $root = $PSScriptRoot
    }

    # if your running this script and your database instance is different, change this
    $server = "."
    $startupProject = 'Project.Domain.csproj'
    # if you want to skip generation for any of the individual databases, just comment out the entire line with a # in front of it, similar to this comment
    $databasesToScaffold = @(
        @{ DatabaseName = "SampleDatabase" ; Context = "SampleDatabaseContext"; Output = ".\SampleDatabase\Model";}
    )

    foreach($database in $databasesToScaffold) {

        Write-Host -ForegroundColor Magenta "Generating $($database.DatabaseName) => $($database.Output)\$($database.Context)"
        $connectionString = "Server=$server;Database=$($database.DatabaseName);Integrated Security=true;"

        $output = Join-Path -Path $root -ChildPath $database.Output 
        $project = Join-Path -Path $root -ChildPath $startupProject 

        & dotnet ef dbcontext scaffold $connectionString Microsoft.EntityFrameworkCore.SqlServer --context $database.Context --output-dir $output --startup-project $project --force --data-annotations --no-build

        $contextOutput = Join-Path -Path $root -ChildPath $database.Output -AdditionalChildPath "$($database.Context).cs" -Resolve
        $content = Get-Content $contextOutput -Raw

        # we need to do a regex replacement in the method to add some boiler plate code to support Access Tokens in the database, as this isn't otherwise configurable
        # we include the full class names here so we dont have to try and add using statements
        $accessTokenCode = 
@'

            var conn = (System.Data.SqlClient.SqlConnection)Database.GetDbConnection();

            var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(conn.ConnectionString);

            // if the connection string is missing the user name or password and the connection string is not flagged for integrated security, try to get an access token
            if ((string.IsNullOrEmpty(builder.Password) || string.IsNullOrEmpty(builder.UserID)) && !builder.IntegratedSecurity)
            {
                conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").GetAwaiter().GetResult();
            }

'@

        $updatedContent = $content -replace "(?<signature>public $($database.Context)\(DbContextOptions\<$($database.Context)\> options\))(?<body>(?:\{[^}]*\}|//.*\r?\n|`"[^`"]*`"|[\S\s])*?\{(?:\{[^}]*\}|//.*\r?\n|`"[^`"]*`"|[\S\s])*?)(?<closure>\})", "`${signature}`${body}$accessTokenCode`${closure}"

        # regex out the OnConfiguring method because otherwise we have to yank the connection string out of it all the time.
        $updatedContent = $updatedContent -replace 'protected override void OnConfiguring\(DbContextOptionsBuilder optionsBuilder\)?(?:\{[^}]*\}|//.*\r?\n|"[^"]*"|[\S\s])*?\{(?:\{[^}]*\}|//.*\r?\n|"[^"]*"|[\S\s])*?\}', '/*$0*/'

        $updatedContent | Set-Content -Path $contextOutput
    }

    Write-Host -ForegroundColor Green "Complete"
}

[MuraliM]

conn.AccessToken = (newAzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

@mgolois can we move this from constructor to OnConfiguring, just to avoid network calls in constructor and testable? Will this have any impact?

[PiotrPerak]

@MuraliM
I just tried it (before finding this issue) and it won't work. You get exception:

System.InvalidOperationException: 'An attempt was made to use the context while it is being configured. A DbContext instance cannot be used inside OnConfiguring since it is still being configured at this point. This can happen if a second operation is started on this context before a previous operation completed. Any instance members are not guaranteed to be thread safe.'

[markmblc]

I was having issues setting the AccessToken inside OnConfiguringin EF Core 3.0. What worked for me was a DbConnectionInterceptor. The AccessToken is attached every time EF Core starts connecting. AzureServiceTokenProvider will always return a cached token if possible so performance doesn't seem to be affected.

    public class AzureAuthInterceptor : DbConnectionInterceptor
    {
        public override async Task<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            if (!(connection is SqlConnection conn))
            {
                return result;
            }

            conn.AccessToken = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net");
            return result;
        }
    }

    services.AddDbContext<MyDbContext>(options =>
    {
        options.UseSqlServer(this.Configuration.GetConnectionString("Default"))
            .AddInterceptors(new AzureAuthInterceptor());
     });

[cbrianball]

I found another alternative that doesn't require Entity Framework Core at all! My above solution had been working fine for me for over a year, but then I needed to use another library: Microsoft.Extensions.Caching.SqlServer. The configuration of the services in that library only take in a connection string, so I had no way of setting a token.

I am now using EF Core 3, which uses Microsoft.Data.SqlClient (MDS). The current version has a dependency on version 1.x, but in order for my solution to work, I needed to use 2.0. Fortunately I was able to add a package references and everything seems to still work just fine with EF Core.

With the latest version of MDS, you can set your own SqlAuthenticationProvider type. Unfortunately you can't define your own type, but you can override an existing type.

Here's what you need to do:

1. Write your own class that implements SqlAuthenticationProvider
   
   
   
   • In the IsSupported override, your body should look like this return authenticationMethod == SqlAuthenticationMethod.ActiveDirectoryIntegrated;
   
   
   • In the AcquireTokenAsync, implement the logic to retrieve the authentication token (my implementation is below)
   
   
2. In your application startup code (before any connections are opened to the database), set your provider as the authentication provider
   
   
   
   • SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryIntegrated, new SqlAppAuthenticationProvider());
   
   
3. Update your connection string to include Authentication=Active Directory Integrated;

That's it. Now, whenever any database connection is opened from your application that uses the updated connection string, the MDS library will invoke your provider, which will obtain the token and return it, then MDS will add your token to the SqlConnection instance for you.

Note: I use the Microsoft.Azure.Services.AppAuthentication library to retrieve the token. It does its own caching, so I don't have to worry about how often MDS attempts to retrieve a token. Also, certain versions of that library already have an implementation of this class (which served as the basis for my implementation), so please check to see if your version already has this, if so, it's probably best to use that.

My implementation of the AcquireTokenAsync method (and helper method):

````
public override async Task AcquireTokenAsync(SqlAuthenticationParameters parameters)
{
var tenantByAuthority = GetTenantByAuthority(parameters.Authority);
if (string.IsNullOrEmpty(parameters.Resource)) throw new ArgumentException("A resource must be specified in SqlAuthenticationParameters");

var tokenProvider = new AzureServiceTokenProvider();
var appAuthenticationResult = await tokenProvider.GetAuthenticationResultAsync(parameters.Resource, tenantByAuthority).ConfigureAwait(continueOnCapturedContext: false);

return new SqlAuthenticationToken(appAuthenticationResult.AccessToken, appAuthenticationResult.ExpiresOn);

}

private static string GetTenantByAuthority(string authority)
{
if (!string.IsNullOrWhiteSpace(authority) && Uri.TryCreate(authority, UriKind.Absolute, out Uri result) && (object)result != null && result.Segments.Length >= 2)
{
return result.Segments[1].TrimEnd('/');
}
return null;
}
````

[cocowalla]

@markmblc strangely this doesn't seem to be working for me when running in Azure - I can see the interceptor ctor is hit, but it never seems to call ConnectionOpeningAsync. Any ideas why?

Update
It was the same problem as this - I guess somewhere a connection is being opened without async :flushed:

[chris5287]

I was having issues setting the AccessToken inside OnConfiguringin EF Core 3.0. What worked for me was a DbConnectionInterceptor. The AccessToken is attached every time EF Core starts connecting. AzureServiceTokenProvider will always return a cached token if possible so performance doesn't seem to be affected.

    public class AzureAuthInterceptor : DbConnectionInterceptor
    {
        public override async Task<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
        {
            if (!(connection is SqlConnection conn))
            {
                return result;
            }

            conn.AccessToken = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net");
            return result;
        }
    }

    services.AddDbContext<MyDbContext>(options =>
    {
        options.UseSqlServer(this.Configuration.GetConnectionString("Default"))
            .AddInterceptors(new AzureAuthInterceptor());
     });

As @cocowalla hinted at with his update, make sure to implement the ConnectionOpening override as well, for those pesky non-async calls!

public class AzureAuthInterceptor : DbConnectionInterceptor
{
    /// <inheritdoc/>
    public override InterceptionResult ConnectionOpening(DbConnection connection, ConnectionEventData eventData, InterceptionResult result)
    {
        if (connection is SqlConnection conn && conn.DataSource.Contains(".database.windows.net", StringComparison.InvariantCultureIgnoreCase))
        {
            Task<string> task = new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/");
            conn.AccessToken = task.Result;
        }

        return result;
    }

    /// <inheritdoc/>
    public override async Task<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
    {
        if (connection is SqlConnection conn && conn.DataSource.Contains(".database.windows.net", StringComparison.InvariantCultureIgnoreCase))
        {
            conn.AccessToken = await new AzureServiceTokenProvider().GetAccessTokenAsync("https://database.windows.net/");
        }

        return result;
    }
}

[chris5287]

FYI: I see native support for this is not too far away now - https://github.com/dotnet/SqlClient/pull/730