SQL Connections left in CLOSE_WAIT state on Linux Azure App Service connected to SQL Server on Windows via Hybrid Connection
- Dominant language
- C#
- Stars
- 989
- Forks
- 340
- Avg merge
- 4d 19h
- Merged PRs (30d)
- 72
Description
### Describe the bug
We have a Linux App Service connected to SQL Server running on a Windows VM. The connection is made through Azure's Hybrid connection. After a SQL Connection should normally close, `sp_who` on the database continues to list the connection and `netstat -p` in the SSH console of the app service shows `:1433` connections in the `CLOSE_WAIT` state:
```
tcp 0 0 localhost:60622 127.0.0.14:1433 FIN_WAIT2 -
tcp 0 0 127.0.0.14:1433 localhost:58500 CLOSE_WAIT -
tcp 0 0 127.0.0.14:1433 localhost:34662 CLOSE_WAIT -
tcp 0 0 127.0.0.14:1433 localhost:60622 CLOSE_WAIT -
tcp 0 0 127.0.0.14:1433 localhost:55730 CLOSE_WAIT -
...
```
(I have only included :1433 connections; there are several other open connections shown that I believe are unrelated)
Output of `sp_who '******'`:
|spid|ecid|status|loginname|hostname|blk|dbname|cmd|requestid|
|----|----|----|----|----|----|----|----|----|
|55|0|sleeping|******|08a9893de9e3|0|******|AWAITING COMMAND|0|
|58|0|sleeping|******|08a9893de9e3|0|******|AWAITING COMMAND|0|
|61|0|sleeping|******|08a9893de9e3|0|******|AWAITING COMMAND|0|
|63|0|sleeping|******|08a9893de9e3|0|******|AWAITING COMMAND|0|
The number of connections for the most part corresponds to the number of connections listed by `sp_who` on the database.
I investigated possible connection pool fragmentation, but I am only using a single database and a single connection string. I built a [reproducible demo](https://github.com/DrOverbuild/AzureSqlConnectionLeaks/blob/sqlclientonly/MinimumRepro/Pages/Index.cshtml.cs), that creates a SqlConnection, reads a single record, and disposes the connection (implicitly with `using`) in the index page of the app. The connection state in netstat is `ESTABLISHED` until a [period of inactivity](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#remove-connections), after which the connection state switches to `CLOSE_WAIT` indefinitely.
To further accelerate the issue, I created a [version of the demo](https://github.com/DrOverbuild/AzureSqlConnectionLeaks/blob/clearconnectionpools/MinimumRepro/Pages/Index.cshtml.cs) that calls `SqlConnection.ClearAllPools()` after disposing the connection. After I deployed that to the same app service, I would see a new `CLOSE_WAIT` connection from `netstat` after each request.
I am seeing no logged exceptions. I am unable to reproduce the issue connecting to the same SQL Server but with the app running on my development machine (macOS, Apple Silicon), nor am I able to reproduce the issue with the app running on a Windows Azure App Service.
The symptoms of this issue are very similar to #155, just with more strict operating conditions.
### To reproduce
Using this [reproducible demo](https://github.com/DrOverbuild/AzureSqlConnectionLeaks/tree/clearconnectionpools):
- Set up an SQL Server database on a Windows machine
- Create a new Linux App Service
- Install and set up the Hybrid Connection Manager on the Windows machine
- Add the hybrid connection to the app service
- Set the connection string in the application
- Deploy the application
- Make requests to the application's index page
Use `sp_who` to monitor connections on the database. Use `netstat` in the SSH console of the app service to monitor connections on the app service.
### Expected behavior
The connection does not remain listed in `netstat` or with `sp_who` after `SqlConnection.ClearAllPools()` is called.
### Further technical details
Microsoft.Data.SqlClient version: 6.0.1 (I have tried various versions down to 5.0.0 with no success. I have not tried any earlier version.)
.NET target: .NET 9.0 (I also tried .NET 8.0 and experienced the same issue)
SQL Server version: SQL Server 2022 on Windows Server 2022, SQL Server 2017 on Windows Server 2017
Operating system: Azure App Service on Linux
**App Service Details**
Debian GNU/Linux 12 (bookworm)
DOCKER_SERVER_VERSION = 24.0.9
REGION_NAME = southcentralus
WEBSITE_SKU = Premium0V3
**Additional context**
Hybrid Connection Manager: 0.7.7
Contributor guide
Assessment
This issue has not been assessed yet.