Azure SQL Database Connection Pooling with User Assigned Managed Identity

Question

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

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
– Dan Guzman, Commented May 4, 2021 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
– randy, Commented May 4, 2021 at 13:12
I misunderstood the credential you were using. I thing @mpeterson's answer should address the issue with pooling. — Dan Guzman
– Dan Guzman, Commented May 4, 2021 at 16:27

mpeterson · Accepted Answer · 2021-05-04 14:06:13Z

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.

answered May 4, 2021 at 14:06

mpeterson

1,69212 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

randy Over a year ago

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.

Collectives™ on Stack Overflow

Azure SQL Database Connection Pooling with User Assigned Managed Identity

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related