5

It seems like the EF Core connection pooling is not working correctly with User Assigned Managed identities. My web page makes 3 Ajax calls from my Angular front end to a Web API controller. The controller uses a repository class that contains the DbContext that implements IDisposable.

We are running .NET Core 3.1 with EF Core using the default dependency container and the default DbContext settings, so it's ServiceLifeTime.Scoped.

My DbContext ctor has code like this for the managed identity.

var connection = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();

var options = new DefaultAzureCredentialOptions { ManagedIdentityClientId = surveyToolOptions.Value.ManagedIdentityClientId };
var credential = new DefaultAzureCredential(options);
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));

connection.AccessToken = token.Token;

When I look a the session count, it goes up by the 3 connections every time I hit the page. It's never reusing the connections. They also don't go away for about 4-5 minutes.

SELECT host_name, Program_name, COUNT(*) 
FROM sys.dm_exec_sessions  s
JOIN sys.databases AS d ON s.database_id = d.database_id
GROUP BY host_name, Program_name

This is a problem since the Azure SQL database is very limited on the number of sessions. https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases#standard-service-tier

When I switch it over to Standard SQL Server authentication with a User Id and password, it's working as expected.

Do I need to turn connection pooling off when using Managed Identities? That does seem to work, but I'm sure it's taking longer to establish a connection with pooling off.

-Randy

randy
  • 253
  • 4
  • 17
  • Each different security context is a separate connect pool. You'll get better scalability if you authenticate to the database using a service account rather than than the end client identity. – Dan Guzman May 04 '21 at 12:49
  • @DanGuzman, i thought a User Assigned Managed identity is a service account, or are you saying i should use a System Assigned Managed identity? It shouldn't be using anything to do with the end user in either case, at least that's what i thought. – randy May 04 '21 at 13:12
  • I misunderstood the credential you were using. I thing @mpeterson's answer should address the issue with pooling. – Dan Guzman May 04 '21 at 16:27

1 Answers1

2

If you are creating a new DBContext on every request, and it calls credential.GetToken(...) in the constructor, you are likely ending up with a different token every time, so the connection cannot be pooled.

Retrieving and storing the token outside of the constructor should fix this.

mpeterson
  • 1,672
  • 12
  • 15
  • Thanks mpeterson! That was essentially the key. I had to use a singleton service that gets the token and is injected into the dbContext ctor. – randy May 05 '21 at 20:44