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.

Answer 2

It seems you need to add a retry logic to your Web API as explained here. The "Physical connection is not usable" error may be caused by transient errors as explained on this Microsoft documentation.

"Transient fault errors typically manifest as one of the following error messages from your client programs:+
•Database on server is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of •Database on server is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of . (Microsoft SQL Server, Error: 40613) •An existing connection was forcibly closed by the remote host. •System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable) •An connection attempt to a secondary database failed because the database is in the process of reconfguration and it is busy applying new pages while in the middle of an active transation on the primary database."