AddDbContextPool does not work with Azure Ad managed identity authentication for Azure SQL

[felinepc]

Describe the bug

I followed MS documentation here to configure Azure AD managed identity for Azure SQL authentication. It works with AddDbContext in ASP.NET Core 3.1, but not AddDbContextPool which we want to use for higher performance

To Reproduce

1. Adjust connection string per MS documentation
2. Add to DbContext's constructor:

var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

3. Configure DI in Startup.cs with services.AddDbContextPool()

Exceptions (if any)

SqlException: Login failed for user ''. Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)

Further technical details

• ASP.NET Core version: 3.1
• Include the output of dotnet --info

.NET Core SDK (reflecting any global.json):
 Version:   3.1.402
 Commit:    9b5de826fd

Runtime Environment:
 OS Name:     Windows
 OS Version:  10.0.19041
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\3.1.402\

Host (useful for support):
  Version: 3.1.8
  Commit:  9c1330dedd

.NET Core SDKs installed:
  3.1.402 [C:\Program Files\dotnet\sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.22 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.22 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.1.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.22 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.1.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.1.8 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

• IDE: Visual Studio 2019 Enterprise on Windows 10

[blowdart]

@ajcvickers Who on the SQL team could talk to this?

[ajcvickers]

@blowdart The pattern shown in the documentation won't work with context pooling because the constructor is only called once the first time the context instance is created. It is also Means that the context is fully initialized and the connection is created as soon as the context instance is created, which defeats the normal lazy initialization that happens in the context to avoid work if it isn't actually used. Finally, it forces sync I/O.

People have had more success with a pattern that uses database interceptors. See dotnet/efcore#21578. We'll also tracking the experience with dotnet/efcore#13261.

[blowdart]

@felinepc as this falls under EF (because they also have the SQL classes), I'm going to close this and you can follow either of the issues Arthur suggests above.