dotnet / dotnet/SqlClient

Synchronously opening multiple ActiveDirectoryManagedIdentity connections in parallel is very slow

Open
#2,470 3 comments 0 reactions 0 assignees View on GitHub
Repro Available :heavy_check_mark:
Dominant language
C#
Stars
989
Forks
340
Avg merge
4d 18h
Merged PRs (30d)
69

Description

### Describe the bug
When multiple connections are opened concurrently using `Authentication=ActiveDirectoryManagedIdentity` via `.Open()`, the time taken increases significantly depending on the overall count.

When using the default `Connect Timeout` value (15s), this will prevent any connections opening with as few as ~20 concurrent attempts.

### To reproduce
This can be reproduced using code similar to the following, running on a freshly-rebooted Azure App Service:

```c#
const string connectionString = @"Data Source=managedidentityrepro-sql.database.windows.net;Initial Catalog=managedidentityrepro-sqldb;Connect Timeout=60;Encrypt=True;Trust Server Certificate=True;Authentication=ActiveDirectoryManagedIdentity;Application Name=EntityFramework";
Parallel.For(0, 16, new ParallelOptions { MaxDegreeOfParallelism = 16}, _ =>
{
using var connection = new SqlConnection(connectionString);
connection.Open();
});
```

Because I have not been able to test ActiveDirectoryManagedIdentity auth locally, I've put together a repro that can be deployed to an Azure App Service + Azure SQL Database:
https://github.com/TomGathercole/ManagedIdentityRepro

The issue only affects new connections, so the app service must be rebooted before testing with a particular number of threads. This is a bit of a pain, and you may not want to deploy this in the first place, so I have incldued the results I collected below:

| async | threads | totalMilliseconds |
|-------|---------|-------------------|
| False | 1 | 2,584.46 |
| False | 2 | 2,787.73 |
| False | 4 | 4,301.57 |
| False | 8 | 10,845.30 |
| False | 16 | 13,047.61 |
| False | 32 | 26,729.01 |
| True | 1 | 2,020.73 |
| True | 2 | 2,098.92 |
| True | 4 | 2,208.14 |
| True | 8 | 2,176.08 |
| True | 16 | 2,230.75 |
| True | 32 | 2,384.87 |

(async = false uses the synchronous `.Open()` method. async = true uses `.OpenAsync()`, and is included to illustrate the difference in behaviour)

I realize this is fairly complicated - if there's a way I can easily test ActiveDirectoryManagedIdentity auth in a console app, then please let me know and I'll try and simplify the repro.

### Expected behavior
`.Open()` should perform similarly to `.OpenAsync()` when multiple connections are opened in parallel.

### Further technical details
Microsoft.Data.SqlClient version: (5.2.0)
.NET target: (.net8.0)
SQL Server version: (Azure SQL)
Operating system: (Windows on Azure App Service)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.