We have helped to fix numerous SQL related issues for our customers as part of our Server Support Services.

In this context, we shall look into the main reason why sql 10054 bug occurs and how to solve it.

What triggers SQL server error 10054?

The unique identifier of a service instance in an SQL server is known as a Service Principal Name (SPN).

In a Kerberos authentication process, SPNs helps to relate a service instance with a service logon user account. The association of a service on a server with an account which controls the service is made possible by an SPN mapping. This process allows interactive Kerberos authentication.

The main factors which triggers this error are;

* When a Service Principal Name (SPN) fails to register with an SQL Server Service.

* In the process where the SPNs is replicated.

* When the ports are Dynamic in nature.

* Due to SSL certificates issues at client side.

* When Windows Authentications option alone is selected when an SQL Server is Installed.

How to solve SQL server error 10054.

Whenever an SQL Server is started, an SPN for that instance is always created. This process could fail when the Read/Write ServicePrincipalName permission is not enabled in the service account leading to SQL error 10054.

Below, you will see how to fix this bug.

Checking the registration status of the SPN via SETSPN system tool

You can use the following command to know if the SPNs is registered or not;

setspn -L hostname - This Substitute the actual hostname for the computer.
setspn -L localhost- This checks the registration status of the account localhost.

In the case where the SPN is not registered, it needs to be and the Read/Write permission for the service account must be set. In the command below, lets say the domain  is service.domain.com and the service account is service1 for computer named computer1, then run;

setspn -s http/computer1.service.domain.com help\service1

Checking if the SPNs is replicated

Another cause of SQL error 10054 is due to a situation when the SPNs is replicated. We can use the following command to check for duplicates and list them if any;

setspn –X

As soon as you see duplicated SPNs, they need to be deleted and created again. The following command can be used in this case;

setspn -s service/namehostname // To add SPN
setspn -r hostname // To reset spn
setspn -d service/name hostname // To remove SPN

Other Solutions to fix SQL server error 10054

In some cases, this error can be solved when you disable SYN flooding attack protection. To do this, add the following registry key;

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\SycAttackProtect{DWORD} = 0

After adding successfully, you must reboot the server to enable changes to be effected successfully.

Additionally, SSL certificate being present could lead to such issue triggering. In this case, a temporal removal of SSL could enable a quick fix to 10054 sql error.

Need support in solving SQL errors? We are available.