SQL Server 2017 connection problems with Asp.Net core runtime 6.0.x

Question

We have an application running on Asp.Net Core 3.1.x runtime.
Recently the application is moved to Asp.Net Core 6.0.x runtime.

After the upgrade, we are facing issue while connecting to SQL Server 2017 database when application is running from docker container on Redhat Linux OS.

Exception is -
Failed to connect to SQL Server\n ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.\n at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\n at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)\n at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)\n at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)\n at Microsoft.Data.SqlClient.SqlConnection.Open()...........................

This problem does not occur when database is connected from Windows ( not running as container, but as console application )

Following are connection parameters - "DB_CONNECTION_TIMEOUT": "15",
"DB_CONNECTION_POOLING": "true",
"DB_MIN_POOL_SIZE": "5",
"DB_MAX_POOL_SIZE": "100"

SQL connect string as formed as - Data Source=<IP_ADDR>,1433;Initial Catalog=mydb;User ID=myuser;Password=<password>;Pooling=True;Min Pool Size=5;Max Pool Size=100;Connect Timeout=15

Any knows issues with SQL Server 2017 + Asp.Net Core 6.0.x + Docker container combination?

Answer 1

Looks like you're using Microsoft.Data.SqlClient. That means you have probably upgraded to v4+ of the library. They made a low key change that breaks connections to SQL Databases. You can read about it on Github but basically SQL communication is now encrypted by default which breaks any system that resides on a remote server and isn't using SSL (probably most systems). The workaround is to either disable encryption in your connection string Encrypt=false or use the built in SQL SSL certificate. But that doesn't work across machines unless it is a trusted certificate so you have to disable trusted certificates.

Just taking a guess here but I'm wondering if you've got an infinite loop going on as it tries (and fails) to connect until you eventually eat up the connection pool. I'm assuming you are properly handling connections already using using. Try adjusting your connection string to either disable encryption or allow trusted certificates.

   Server=myserver;Database=mydatabase;Encrypt=true;TrustServerCertificate=true  

Or

   Server=myserver;Database=mydatabase;Encrypt=false;