Table of Contents
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
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.
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