SQL Connections to on prem SQL Server through Hybrid Connection are not released

Question

We have a Linux App Service connected via Hybrid Connection to our on prem SQL Server. We've had this issue on both SQL Server 2022 and SQL Server 2017. The same project deployed to a Windows App Service has no issue. Here's a basic project that reproduces the issue: https://github.com/DrOverbuild/AzureSqlConnectionLeaks

The application is not properly destroying SQL connections, which I can monitor with sp_who on the database. The index page of the application simply queries a single record from the database via EF Core, which is retrieved from ASP.NET's DI container. After deploying the app and a load test is run with 100 virtual users, we had about 90 sleeping connections to the database and they remained connected for over an hour until we restarted the application. The issue also occurs after setting the application to "Always On", restarting, and let the application go idle. The 5 minute interval at which Always On pings the application is long enough for the application to forget about the existing connection and start a new connection, so over time, several connections will be listed on the server. In this scenario, setting Min Pool Size to 1 or pinging the db from a background service every 30 seconds keeps the existing connection alive, so no new connections are created, but new connections created under load will still not be released.

There's nothing I can find in the code that would indicate improper resource management. I cannot reproduce the issue in local development (Apple Silicon, connected to same on prem SQL Server). I cannot reproduce the issue with a Linux App Service connecting to an Azure SQL Server. To rule out an issue with EF Core, I replaced it with pure ADO.NET and experienced the same issue. I am convinced the issue lies somewhere in Linux App Service or the Linux implementation of the .NET SQL Server client or driver, but I'm happy to explore potential issues in the application code. I'm just not sure what else to do there.

Answer 1

Hi @Jasper Reddin
Thanks for the question and using MS Q&A platform.
it seems the SQL connections are not being properly released when using a Linux App Service with Hybrid Connection to an on-prem SQL Server.
1.Ensure Max Pool Size is set to a reasonable limit and use Connection Lifetime to recycle idle connections.

var connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;Max Pool Size=100;Connection Lifetime=300;";

2.Use using statements to ensure connections are properly closed.

 using (var connection = new SqlConnection(connectionString)) { connection.Open(); } 

For Entity Framework Core, ensure DbContext is scoped correctly to avoid holding connections too long.
3.Ensure HCM is up to date and review logs for errors. Monitor active connections in Azure to identify unexpected connection retention.
4.App Service Configuration If enabled, ensure it doesn’t cause connection persistence issues. Consider a heartbeat request instead.Check default timeout settings that may interfere with connections.
5.Use the latest .NET runtime and Microsoft.Data.SqlClient version to avoid known bugs.

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.0" /> 

6.Enable Application Insights to track SQL connections. Use sp_who2 or sys.dm_exec_connections in SQL Server to identify idle connections.

7.Ensure firewall settings do not block idle or long-running SQL connections.Check TCP Keep-Alive settings on Linux (sysctl -a | grep keepalive).
references:
SQL Server connection pooling (ADO.NET)
Connection Pooling
Azure App Service Hybrid Connections
https://learn.microsoft.com/en-us/azure/azure-monitor/app/codeless-app-service?tabs=aspnetcore
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access?view=sql-server-ver16