Categories: SQL Server

SQL Server Error : 10054, Severity: 16. The data value for one or more columns

Table of Contents

Toggle

SQL Server Error : 10054 Details

SQL Server Error: 10054
Severity: 16
Event Logged or not: No
Description:
The data value for one or more columns overflowed the type used by the provider.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

A service instance’s service principal name (SPN) is a unique identifier. Kerberos authentication uses SPNs to link a service instance to a service logon account. In summary, an SPN mapping associates a service on a given server with an account responsible for its maintenance, allowing mutual Kerberos authentication.

“an existing connection was forcibly closed by the remote host sql server 2019” or
“sql server, error: 10054 client unable to establish connection”

During a client-side remote database connection, SQL server error 10054 occurs normally. The following is an example of a typical error message:

Solution for Resolving the Error

When SQL Server starts up, it tries to construct an SPN for the instance.

This will fail unless the service account is explicitly granted Read and Write ServicePrincipalName rights.

As a result, SQL error 10054 may occur.

The failure to register a Service Principal Name (SPN) for the SQL Server service, according to your SQL Server error log in this case, could be the cause of the problem.

When SQL Server starts up, it tries to construct an SPN for the instance. This will fail unless the service account is explicitly granted Read and Write ServicePrincipalName rights. Other possible causes include duplicated SPNs or dynamic ports.

To troubleshoot the problem, I propose using the procedures listed below.

To begin, use the SETSPN utility to see if SPN is registered. If not, run SETSPN with the -S option to provide the service account permission to read and write the SPN and register an SPN.

Check with the SETSPN utility to see if SPN is registered.

From a command prompt, type the following commands to see which SPNs are registered for a certain computer:

setspn -L [hostname] - Substitute the actual hostname for the computer.
 setspn -L localhost      - This command will check registrations for the account localhost.

To register the http service for say the example.sqlserver.com domain using a service account named test, use the following command:

setspn -s http/test.example.sqlserver.com domain\test

To Check for duplicated SPNs

The second reason for 10054 SQL error is duplicated SPNs. We can use the setspn command with a -X option to list out all the duplicated SPNs.

setspn –X

The third way to fix 10054 error would be to disable the SYN flooding attack protection. This can be done by adding the following registry key.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\SycAttackProtect{DWORD} = 0
Once the key is added, we need to reboot the server for the changes to take effect.

Alternate Solutions for related issue

The SSL certificate installed at the client end can sometimes cause this issue and can trigger the 10054 error. Check the SSL Certificate

SQL Server Error Code and solution summary

SQL Server Error: 10054
Severity: 16
Event Logged or not: No
Description:
The data value for one or more columns overflowed the type used by the provider.

Set the SPN’s needed to resolve the issue which resolves also below issues

provider: ssl provider, error: 0 – an existing connection was forcibly closed by the remote host
ssis tcp provider: an existing connection was forcibly closed by the remote host
microsoft sql server an existing connection was forcibly closed
sqlncli11 tcp provider: an existing connection was forcibly closed by the remote host
system.componentmodel.win32exception: an existing connection was forcibly closed by the remote host
microsoft sql server error 10054