Synchronously opening multiple ActiveDirectoryManagedIdentity connections in parallel is very slow
- 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
Assessment
This issue has not been assessed yet.