Sql server 2005 ошибка 10054

SQL server error 10054 often triggers during remote database connection at the client-side. It generally triggers due to issues with Service Principal Name (SPN) for the SQL Server service.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Today we’ll take a look at the cause for this error and how to fix it.

What causes the error SQL server error 10054?

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. In short, an SPN mapping allows service on a particular server to be associated with an account responsible for the management of the service, thereby permitting mutual Kerberos authentication.

SQL server error 10054 triggers normally during remote database connection at the client-side. A typical error message looks like:

The major reasons for this error message include:

• Failure to register a Service Principal Name (SPN) for the SQL Server service
• Duplicated SPNs
• Dynamic ports
• SQL Server got installed with Window Authentications only
• SSL certificates at the client-side

Let us now look at how to fix this error in detail.

How to fix the error SQL server error 10054?

SQL Server always attempts to create an SPN for the instance upon startup. Unless the service account is specifically given the Read and Write ServicePrincipalName permissions, this will fail. Thus it may lead to SQL error 10054. Let us now look at the steps to fix this error.

Check if SPN is registered by SETSPN tool.

To check the SPNs that are registered for a specific computer, you can run the following commands from a command prompt:

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

If the SPN is not registered, we need to provide the service account permissions to read/ write the SPN and register an SPN by running SETSPN with the -S option.

For instance, to register the http service on the standard port on a computer named test in the help.example.com domain using a service account named test1, use the following command:

setspn -s http/test.help.example.com helptest1

Check for duplicated SPNs

As we listed out earlier, another reason for the 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

Once the duplicated SPNs are identified, we need to delete all of the duplicated SPN and recreate it. It can be performed with the setspn commands. Format to be used for these operations are:

setspn -s service/namehostname // adding SPN
setspn -r hostname //resetting spn
setspn -d service/name hostname //removing SPN

Disable SYN flooding attack protection

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

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParametersSycAttackProtect{DWORD} = 0

Once the key is added, we need to reboot the server for the changes to take effect.

Alternate Solutions

Apart from the fixes discussed above, there are some alternate fixes that can help to resolve the issue. The SQL error 10054 can be triggered due to the use of dynamic ports. It is possible to bind SPN to an instance when using dynamic ports. Thus using specific ports can help to fix the error.

The SSL certificate installed at the client end can sometimes cause hindrance and can trigger the 10054 error. Thus it would be a good idea to remove the SSL certificate at the client end temporarily. It would help to confirm if this was the reason behind the error.

Further, changing the authentications to “SQL Server and Window authentication” might also help to fix the problem.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL server error 10054 often triggers during remote database connection at the client-side due to issues with Service Principal Name (SPN). Today, we saw how our Support Engineers fix this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

An existing connection was forcibly closed by the remote host (OS error 10054)

Applies to: В SQL Server

Before you start troubleshooting, we recommend that you check the prerequisites and go through the checklist.

This article details various causes and provides resolutions for the following errors:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 — An existing connection was forcibly closed by the remote host.)

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 — An existing connection was forcibly closed by the remote host.)

Operating system error 10054 is raised in the Windows sockets layer. For more information, see Windows Sockets Error Codes: WSAECONNRESET 10054.

When do you see the error?

Secure Channel, also known as Schannel, is a Security Support Provider (SSP). It contains a set of security protocols that provide identity authentication and secure, private communication through encryption. One function of Schannel SSP is to implement different versions of the Transport Layer Security (TLS) protocol. This protocol is an industry standard that is designed to protect the privacy of information communicated over the Internet.

The TLS Handshake Protocol is responsible for the key exchange necessary to establish or resume secure sessions between two applications communicating over TCP. During the pre-login phase of the connection process, SQL Server and client applications use the TLS protocol to establish a secure channel for transmitting credentials.

The following scenarios detail errors that occur when the handshake can’t be completed:

Scenario 1: No matching TLS protocols exist between the client and the server

SSL and versions of TLS earlier than TLS 1.2 have several known vulnerabilities. You’re encouraged to upgrade to TLS 1.2 and disable earlier versions wherever possible. Accordingly, system administrators could push out updates through group policy or other mechanisms to disable these insecure TLS versions on various computers within your environment.

Connectivity errors occur when your application uses an earlier version of Open Database Connectivity (ODBC) driver, OLE DB provider, .NET framework components, or a SQL Server version that doesn’t support TLS 1.2. The issue occurs because the server and the client can’t find a matching protocol (such as TLS 1.0 or TLS 1.1). A matching protocol is needed to complete the TLS handshake required to proceed with the connection.

Resolution

To resolve this issue, use one of the following methods:

• Upgrade your SQL Server or your client providers to a version that supports TLS 1.2. For more information, see TLS 1.2 support for Microsoft SQL Server.
• Ask your system administrators to temporarily enable TLS 1.0 or TLS 1.1 on both the client and the server computers by performing one of the following actions:
  • Use the IIS Crypto tool (Ciphers suites section) to validate and make changes to the current TLS settings.
  • Start Registry Editor, and locate the Schannel-specific registry keys: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL .
    For more information, see TLS 1.2 Upgrade Workflow and SSL Errors after Upgrading to TLS 1.2.

Scenario 2: Matching TLS protocols on the client and the server, but no matching TLS cipher suites

This scenario occurs when you or your administrator restricted certain algorithms on the client or the server for extra security.

The client and server TLS versions, cipher suites can be easily examined in the Client Hello and Server Hello packets in a network trace. The Client Hello packet advertises all the client cipher suites, while the Server Hello packet specifies one of them. If there are no matching suites, the server will close the connection instead of responding to the Server Hello packet.

Resolution

To check the issue, follow these steps:

If a network trace isn’t available, check the functions value under this registry key: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlCryptographyConfigurationLocalSSL0010002

Use this PowerShell command to find the TLS functions

Use a tool such as IIS Crypto (Ciphers suites section) to check whether there are any matching algorithms. If no matching algorithms are found, contact Microsoft support.

Scenario 3: SQL Server uses a certificate signed by a weak-hash algorithm, such as MD5, SHA224, or SHA512

SQL Server always encrypts network packets that are related to sign in. For this purpose, it uses a manually provisioned certificate or a self-signed certificate. If SQL Server finds a certificate that supports the server authentication function in the certificate store, it will use the certificate. SQL Server will use this certificate even if it hasn’t been manually provisioned. If these certificates use a weak-hash algorithm (thumbprint algorithm) such as MD5, SHA224, or SHA512, they won’t work with TLS 1.2 and cause the previously mentioned error.

Self-signed certificates are not affected by this issue.

Resolution

To resolve the issue, follow these steps:

1. In SQL Server Configuration Manager, expand SQL Server Network Configuration in the Console pane.
2. Select Protocols for .
3. Select the Certificate tab and follow the relevant step:
   • If a certificate is displayed, select View to examine the Thumbprint algorithm to confirm whether it’s using a weak-hash algorithm. Then, select Clear and go to step 4.
   • If a certificate isn’t displayed, review the SQL Server error log for an entry that resembles the following and note down the hash/thumbprint value:
     2017-05-30 14:59:30.89 spid15s The certificate [Cert Hash(sha1) «B3029394BB92AA8EDA0B8E37BAD09345B4992E3D»] was successfully loaded for encryption
4. Use the following steps to remove server authentication:
   1. Select Start >Run, and type MMC. (MMC also known as the Microsoft Management Console.)
   2. In MMC, open the certificates and select Computer Account in the Certificates snap-in screen.
   3. Expand Personal >Certificates.
   4. Locate the certificate that SQL Server is using by its name or by examining the Thumbprint value of different certificates in the certificate store and open its Properties pane.
   5. On the General tab, select Enable only the following purposes and deselect Server Authentication.
5. Restart the SQL Server service.

Scenario 4: The client and the server are using TLS_DHE cipher suite for TLS handshake, but one of the systems doesn’t have leading zero fixes for the TLS_DHE installed

If this article has not resolved your issue, you can check if the common connectivity issues articles can help.

See also

Third-party information disclaimer

The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.

Источник

Sql error 10054 tcp provider

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Cannot connect to RAJASAJIDSQLEXPRESS.
Hello
I am using SQL server management studio 2012. previously it was working fine but it is not working. When i connect server it shows following error

• Moved by Olaf Helper MVP Friday, November 28, 2014 6:10 PM Moved from «Database Engine» to a more related forum

Please check the SQL Server ErrorLog file if you can see some more details there, also if you may see a «SPN Registration» error.

The error occurs when connecting Via TCP/IP can you check in SQL Server configuration manager that tcp/ip protocol is enabled or not . Please refer

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

• Edited by Shanky_621 MVP Friday, November 7, 2014 2:39 PM

Please see attached file for sql config. i think every thing is fine there

Please suggest any solutions.

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

I checked those blogs. They are also not useful. they are just a garbage. I have already created SynAttackProtect setting in Registery but that also not helped..

see below image

Here are my latest SQL error logs

Please check and help me.

According to your SQL Server error log, the issue could be due to the failure of registering a Service Principal Name (SPN) for the SQL Server service.

SQL Server always attempts to create an SPN for the instance upon startup. Unless the service account is specifically given the Read and Write ServicePrincipalName permissions, this will fail. Besides, other causes could be duplicated SPNs, or dynamic ports.

I recommend you to follow methods below to troubleshoot the issue.

Firstly, you could check if SPN is registered by SETSPN tool. If not, please give the service account permissions to read and write the SPN and register a SPN by running SETSPN with the -S option.

Secondly, to find if there are any duplicated SPN, you could check through “setspn –X”. If so, you could delete all of the duplicated SPN and recreate it.

Moreover, it is possible to bind SPN to instance when using dynamic ports. So I recommend you to use specific ports.

Источник

SQL server error 10054 – Fix it easily

by Arya MA | Jul 19, 2020

SQL server error 10054 often triggers during remote database connection at the client-side. It generally triggers due to issues with Service Principal Name (SPN) for the SQL Server service.

As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.

Today we’ll take a look at the cause for this error and how to fix it.

What causes the error SQL server error 10054?

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. In short, an SPN mapping allows service on a particular server to be associated with an account responsible for the management of the service, thereby permitting mutual Kerberos authentication.

SQL server error 10054 triggers normally during remote database connection at the client-side. A typical error message looks like:

The major reasons for this error message include:

• Failure to register a Service Principal Name (SPN) for the SQL Server service
• Duplicated SPNs
• Dynamic ports
• SQL Server got installed with Window Authentications only
• SSL certificates at the client-side

Let us now look at how to fix this error in detail.

How to fix the error SQL server error 10054?

SQL Server always attempts to create an SPN for the instance upon startup. Unless the service account is specifically given the Read and Write ServicePrincipalName permissions, this will fail. Thus it may lead to SQL error 10054. Let us now look at the steps to fix this error.

Check if SPN is registered by SETSPN tool.

To check the SPNs that are registered for a specific computer, you can run the following commands from a command prompt:

If the SPN is not registered, we need to provide the service account permissions to read/ write the SPN and register an SPN by running SETSPN with the -S option.

For instance, to register the http service on the standard port on a computer named test in the help.example.com domain using a service account named test1, use the following command:

Check for duplicated SPNs

As we listed out earlier, another reason for the 10054 SQL error is duplicated SPNs. We can use the setspn command with a -X option to list out all the duplicated SPNs.

Once the duplicated SPNs are identified, we need to delete all of the duplicated SPN and recreate it. It can be performed with the setspn commands. Format to be used for these operations are:

Disable SYN flooding attack protection

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

Once the key is added, we need to reboot the server for the changes to take effect.

Alternate Solutions

Apart from the fixes discussed above, there are some alternate fixes that can help to resolve the issue. The SQL error 10054 can be triggered due to the use of dynamic ports. It is possible to bind SPN to an instance when using dynamic ports. Thus using specific ports can help to fix the error.

The SSL certificate installed at the client end can sometimes cause hindrance and can trigger the 10054 error. Thus it would be a good idea to remove the SSL certificate at the client end temporarily. It would help to confirm if this was the reason behind the error.

Further, changing the authentications to “SQL Server and Window authentication” might also help to fix the problem.

[Need any further assistance in fixing SQL errors? – We’re available 24*7]

Conclusion

In short, SQL server error 10054 often triggers during remote database connection at the client-side due to issues with Service Principal Name (SPN). Today, we saw how our Support Engineers fix this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Источник

Hi,

Please help with connection problems between machines in different environments.

I am new to using Service Broker in SQL 2005.  I have set up 2 SQL 2005 express instances on two different servers within my network. The Initiator server machine is within my development domain.  The Target server machine is is in my network, but is not part of the domain.  It is in a separate machine in a separate WorkGroup.  I am using Certificates fo security for all messaging.

All of this is working OK.

Next I needed to move this to fit my ultimate environment.  This involves having he SB communications between my WorkGroup or Domain SQL instance and a SQL Service Broker Instance in my client’s domain.  I assumed that all that I had to do was change the IP addresses for the Routes and adding new Service names and queues to identify my client’s location rather then the computers in my physical network. 

When I begin dialog conversations with my Client computer, they do not get delivered.  The transmission_queue status entry reads:
   An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)’.

On my client’s side, the Windows Event log has the following entry:

                Error: 17836, Severity: 20, State: 1

                Length specified in network packet payload did not match number of bytes read; the connection has been closed.

                                Please contact the vendor of the client library: [CLIENT: 68.164.193.20]

I don’t now if this is relavent, but the server in my client’s environment is NATted.  I have assumed it is not a transport issue because it is receiving the incoming login request.  I have also watched the traffic with Wireshark and packets are being sent/received between our two sites.

I have run Profiler concurrently on but my SQL instance and my client’s instance to watch the traffic at both ends.  When my side profiler reports the error «An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)», there is nothing reported in the profiler session on my client’s side.

If it would help with replying to this question, I can provide the SQL scripts used setup and configure both sides to this environment.

Thanks,
Norm Derrick

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 helpservice1

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_MACHINESYSTEMCurrentControlSetServicesTcpipParametersSycAttackProtect{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.

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

The SQL Server Management Studio is working just fine when we RDP into the server and try to perform any sa privileges.However, when we use it from our local machines it is throwing the errors

Situation:

SSMS 2016 client

Version: Microsoft SQL Server 2014- Service Pack 1 -Standard Edition

TCP/IP is also enabled.

I can connect successfully to the database server using management studio but when I try to open database diagrams, I get the below error. I don’t even see SQL server agent even if we login with sa privileges.

SQL Server agent service is running.

  TITLE: Microsoft SQL Server Management Studio
  ------------------------------
   An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo)
   ------------------------------
   ADDITIONAL INFORMATION:
   A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)
   For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
  ------------------------------
  An existing connection was forcibly closed by the remote host
  ------------------------------
  BUTTONS:
  OK