Error 19 - Physical connection error

Question

A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable) - occurs intermittently, using hybrid connector to connect to on-premise database from Azure API

I try connecting to SQL database in on-premise sever from app service resource (Web API). Configured classic hybrid connection end point to on-premise db.

Answer 1

Root Cause :

In SQL database, there is a property 'Auto Close' in Options tab, which is set to TRUE by default.

When Azure Service(API) establishes connection to SQL server. Azure tries to maintain the same connection pool and try to reuse and reconnect to it.

As we have set 'Auto Close as TRUE', the SQL database clears the existing connection after sometime. Whereas Azure tries to reconnect with the earlier connection which has already been cleared by SQL database. This results in 'Physical Connection is not usable - error 19'.

SOLUTION:

In SQL db, go to database properties. Navigate to 'OPTIONS' page and expand automatic tab and set 'AUTO CLOSE' property as 'FALSE'.

Make sure you restart Azure app and your database server to ensure no old connections are used.

PROBLEM SOLVED.

NOTE : This is one of the reason (which happened to me).

Another useful method to get rid of this error is to use RETRY LOGIC of Entity Framework 1.1.0

services.AddDbContext<DbContext>(options => options.UseSqlServer('yourconnectionstring',
                 sqlServerOptionsAction: sqlOptions =>
                 {
                     sqlOptions.EnableRetryOnFailure(
                         maxRetryCount: 5,
                         maxRetryDelay: TimeSpan.FromSeconds(30),
                         errorNumbersToAdd: new List<int>() { 19 });
                 }));

In Retry logic, error 19 is not included. So you have to pass the error code 19 to set retry logic for error code 19.