Sql server ошибка 10054

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

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:

SQL server error 10054

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.

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.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

RRS feed

  • Remove From My Forums

  • 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 

    Sajid Manzoor

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

All replies

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

  • hello

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

    Please suggest any solutions.

    Sajid Manzoor

  • Ok fine please read the blogs shared you will find solution

    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

    My Technet Wiki Article

    MVP

  • Hello

    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

    Sajid Manzoor

  • Hello

    Here are my latest SQL error logs

    2014-10-31 01:03:25.34 Server      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

2014-10-31 01:03:25.40 Server      (c) Microsoft Corporation.
2014-10-31 01:03:25.40 Server      All rights reserved.
2014-10-31 01:03:25.40 Server      Server process ID is 2052.
2014-10-31 01:03:25.43 Server      System Manufacturer: 'Dell Inc.', System Model: 'Inspiron N5110'.
2014-10-31 01:03:25.43 Server      Authentication mode is WINDOWS-ONLY.
2014-10-31 01:03:25.43 Server      Logging SQL Server messages in file 'c:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLLogERRORLOG'.
2014-10-31 01:03:25.43 Server      The service account is 'WORKGROUPRAJASAJID$'. This is an informational message; no user action is required.
2014-10-31 01:03:25.43 Server      Registry startup parameters: 
	 -d c:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATAmaster.mdf
	 -e c:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLLogERRORLOG
	 -l c:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATAmastlog.ldf
2014-10-31 01:03:25.43 Server      Command Line Startup Parameters:
	 -s "SQLEXPRESS"
2014-10-31 01:03:27.36 Server      SQL Server detected 1 sockets with 2 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2014-10-31 01:03:27.36 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2014-10-31 01:03:27.36 Server      Detected 8098 MB of RAM. This is an informational message; no user action is required.
2014-10-31 01:03:27.41 Server      Using conventional memory in the memory manager.
2014-10-31 01:03:32.56 Server      This instance of SQL Server last reported using a process ID of 1968 at 10/31/2014 1:01:10 AM (local) 10/30/2014 8:01:10 PM (UTC). This is an informational message only; no user action is required.
2014-10-31 01:03:32.59 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2014-10-31 01:03:32.59 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2014-10-31 01:03:32.62 Server      Software Usage Metrics is disabled.
2014-10-31 01:03:32.82 spid4s      Starting up database 'master'.
2014-10-31 01:03:33.74 Server      CLR version v4.0.30319 loaded.
2014-10-31 01:03:34.73 spid4s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2014-10-31 01:03:34.75 spid4s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2014-10-31 01:03:36.78 spid4s      SQL Trace ID 1 was started by login "sa".
2014-10-31 01:03:37.27 spid4s      Server name is 'RAJASAJIDSQLEXPRESS'. This is an informational message only. No user action is required.
2014-10-31 01:03:38.09 spid12s     A self-generated certificate was successfully loaded for encryption.
2014-10-31 01:03:38.13 spid4s      Failed to verify Authenticode signature on DLL 'c:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLBinnftimport.dll'.
2014-10-31 01:03:38.23 spid4s      Starting up database 'msdb'.
2014-10-31 01:03:38.23 spid9s      Starting up database 'mssqlsystemresource'.
2014-10-31 01:03:38.32 spid12s     Server is listening on [ 'any' <ipv6> 49159].
2014-10-31 01:03:38.32 spid12s     Server is listening on [ 'any' <ipv4> 49159].
2014-10-31 01:03:38.32 spid12s     Server local connection provider is ready to accept connection on [ \.pipeSQLLocalSQLEXPRESS ].
2014-10-31 01:03:38.32 spid12s     Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$SQLEXPRESSsqlquery ].
2014-10-31 01:03:38.33 spid12s     Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2014-10-31 01:03:38.38 spid9s      The resource database build version is 11.00.2100. This is an informational message only. No user action is required.
2014-10-31 01:03:38.46 spid12s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2014-10-31 01:03:38.46 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2014-10-31 01:03:38.46 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/RajaSajid:SQLEXPRESS ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2014-10-31 01:03:38.46 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/RajaSajid:49159 ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2014-10-31 01:03:40.28 spid9s      Starting up database 'model'.
2014-10-31 01:03:40.89 spid9s      Clearing tempdb database.
2014-10-31 01:03:42.13 spid9s      Starting up database 'tempdb'.
2014-10-31 01:03:42.57 spid15s     The Service Broker endpoint is in disabled or stopped state.
2014-10-31 01:03:42.59 spid15s     The Database Mirroring endpoint is in disabled or stopped state.
2014-10-31 01:03:43.11 spid15s     Service Broker manager has started.
2014-10-31 01:03:43.11 spid4s      Recovery is complete. This is an informational message only. No user action is required.
2014-10-31 01:03:43.53 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:WindowsMicrosoft.NETFramework64v4.0.30319.
2014-10-31 01:04:36.98 spid51      Starting up database 'VehicleRecrodTracking'.
2014-10-31 01:04:41.35 spid51      Starting up database 'ResponsiveDemo'.
2014-10-31 01:04:43.17 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:04:54.68 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:00.89 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:04.77 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:10.90 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:15.48 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:21.75 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:26.21 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:30.70 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:36.17 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:41.18 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:43.40 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:47.82 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:53.76 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:05:56.58 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:00.61 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:04.03 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:08.18 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:12.45 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:18.11 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:25.16 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:32.78 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:36.87 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:41.86 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:43.46 spid51      Recovery completed for database WareHouseInventory (database ID 7) in 1 second(s) (analysis 518 ms, redo 0 ms, undo 303 ms.) This is an informational message only. No user action is required.
2014-10-31 01:06:45.38 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:50.38 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:06:53.21 spid51      Recovery completed for database WareHouseInventory (database ID 7) in 2 second(s) (analysis 381 ms, redo 0 ms, undo 1215 ms.) This is an informational message only. No user action is required.
2014-10-31 01:06:58.09 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:07.76 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:15.81 spid51      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2014-10-31 01:07:16.28 spid51      Using 'xpstar.dll' version '2011.110.2100' to execute extended stored procedure 'xp_enum_oledb_providers'. This is an informational message only; no user action is required.
2014-10-31 01:07:19.29 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:25.33 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:30.53 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:34.16 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:38.06 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:41.20 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:43.22 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:45.77 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:47.51 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:49.47 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:50.76 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:52.62 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:55.10 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:07:58.24 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:02.98 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:05.09 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:08.55 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:11.32 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:15.39 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:18.38 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:20.59 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:23.07 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:24.99 spid51      Starting up database 'WareHouseInventory'.
2014-10-31 01:08:27.15 spid51      Starting up database 'WareHouseInventory'.
2014-11-02 13:43:01.35 spid5s      Server resumed execution after being idle 216433 seconds. Reason: timer event.
2014-11-04 15:16:13.45 spid51      Starting up database 'WareHouseInventory'.
2014-11-04 15:16:18.07 spid51      Starting up database 'WareHouseInventory'.
2014-11-04 15:16:20.01 spid51      Starting up database 'WareHouseInventory'.
2014-11-04 15:16:38.86 spid52      Starting up database 'WareHouseInventory'.
2014-11-04 23:12:35.53 spid51      Starting up database 'WareHouseInventory'.
2014-11-04 23:23:46.13 spid54      Starting up database 'WareHouseInventory'.
2014-11-04 23:23:47.83 spid54      Starting up database 'WareHouseInventory'.
2014-11-04 23:24:10.20 spid53      Starting up database 'WareHouseInventory'.
2014-11-04 23:46:36.89 Logon       Error: 18456, Severity: 14, State: 38.
2014-11-04 23:46:36.89 Logon       Login failed for user 'RajaSajidRaja Sajid'. Reason: Failed to open the explicitly specified database 'dbAccounts'. [CLIENT: <local machine>]
2014-11-04 23:49:36.39 spid51      Starting up database 'dbAccounts'.
2014-11-04 23:49:36.75 spid51      The tail of the log for database dbAccounts is being rewritten to match the new sector size of 4096 bytes.  3072 bytes at offset 459776 in file C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESSMSSQLDATAdbAccounts_log.ldf will be written.
2014-11-04 23:50:16.20 spid51      Starting up database 'dbAccounts'.
2014-11-04 23:55:49.52 spid51      Starting up database 'dbAccounts'.
2014-11-04 23:55:57.66 spid51      Starting up database 'dbAccounts'.
2014-11-05 00:40:02.20 Server      Server resumed execution after being idle 613 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2014-11-06 02:21:57.32 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2014-11-06 02:22:07.62 spid4s      Server resumed execution after being idle 51795 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2014-11-06 02:22:08.54 spid4s      Error: 17054, Severity: 16, State: 1.
2014-11-06 02:22:08.54 spid4s      The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
2014-11-06 02:22:38.33 spid15s     Service Broker manager has shut down.
2014-11-06 02:22:48.94 spid4s      .NET Framework runtime has been stopped.

    Please check and help me.

    thanks

    Sajid Manzoor

  • Hi SajiD,

    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.

    For more information about the process, please refer to the article:
    http://www.seangallardy.com/2014/05/using-kerberos-with-sql-server-part-1-double-hop/
    Regards,
    Michelle Li

    • Marked as answer by
      Lydia ZhangMicrosoft contingent staff
      Wednesday, November 19, 2014 1:30 AM
    • Unmarked as answer by
      SajiD designer
      Friday, November 28, 2014 7:42 PM

    • Marked as answer by
      Olaf HelperMVP
      Friday, November 28, 2014 6:10 PM
    • Unmarked as answer by
      SajiD designer
      Friday, November 28, 2014 7:42 PM
Источник
  • Remove From My Forums

  • Question

  • All,

    situation:

    SSMS 2012 client — SS 2008R2 server

    I can connect successfully to the database server using management studio but when I try to, for example, ask properties of a user database, I get the error below.

    Some facts:
    * connecting with SSMS on the server itself is no problem
    * connecting to another SS2008 server is no problem
    * no connection limits is set on the server

    Any ideas?

    Error message:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    A connection was successfully established with the server, but then an error occurred during the login process. (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

Answers

  • Hi PVKERC,

    First please try to make sure the username and password is correct. if you use the SQL Server authentication, then please make sure that it is enabled.

    After that, because you can connect to the SQL Server, but failed with the logon, As the error:10054 says: An existing connection was forcibly closed by the remote host. Then I think we should try to fix the connection.

    Please try to use the following method to fix the error:

    Use the regedit.exe utility to add a new DWORD value named SynAttackProtect to the registry key:                  

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters with value data of 00000000.

    Please pay attention to that setting this registry key may expose the server to a SYN flood, denial-of-service attack. Add this registry value only if necessary and with an understanding of the security risks. Remove this registry value when testing is complete.

    Please refer to :
    how to fix the error 10054:
    http://msdn.microsoft.com/en-us/library/ms187005(SQL.105).aspx .

    Regards,
    Amy Peng

    Amy Peng

    TechNet Community Support

    • Edited by

      Tuesday, September 25, 2012 1:01 AM

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

  • Hello ,

    Check if you see 17832 error in SQL Server errorlog.

    #. Open Registry Editor (In the Sql Database Server) and add the following Key:

    HKEY_LOCAL_MACHINESYSTEMControlSet001ControlLsaKerberosParametersMaxTokenSize

    Type: REG_DWORD
    Value (Decimal): 100000

    When connecting to the SQL servers using a domain account that is a member of a lot of groups (everyone on my team is) the server is running out of buffers to process the amount of security the token is passing is causing it to be «structurally invalid»
    as we discovered in the SQL logs.

    Refer http://support.microsoft.com/kb/262177

    Thank you,

    Karthick P.K |My Facebook Page |My
    Site    | Blog space|
    Twitter

    www.mssqlwiki.com

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

Источник
  • Remove From My Forums

  • Question

  • All,

    situation:

    SSMS 2012 client — SS 2008R2 server

    I can connect successfully to the database server using management studio but when I try to, for example, ask properties of a user database, I get the error below.

    Some facts:
    * connecting with SSMS on the server itself is no problem
    * connecting to another SS2008 server is no problem
    * no connection limits is set on the server

    Any ideas?

    Error message:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    A connection was successfully established with the server, but then an error occurred during the login process. (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

Answers

  • Hi PVKERC,

    First please try to make sure the username and password is correct. if you use the SQL Server authentication, then please make sure that it is enabled.

    After that, because you can connect to the SQL Server, but failed with the logon, As the error:10054 says: An existing connection was forcibly closed by the remote host. Then I think we should try to fix the connection.

    Please try to use the following method to fix the error:

    Use the regedit.exe utility to add a new DWORD value named SynAttackProtect to the registry key:                  

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters with value data of 00000000.

    Please pay attention to that setting this registry key may expose the server to a SYN flood, denial-of-service attack. Add this registry value only if necessary and with an understanding of the security risks. Remove this registry value when testing is complete.

    Please refer to :
    how to fix the error 10054:
    http://msdn.microsoft.com/en-us/library/ms187005(SQL.105).aspx .

    Regards,
    Amy Peng

    Amy Peng

    TechNet Community Support

    • Edited by

      Tuesday, September 25, 2012 1:01 AM

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

  • Hello ,

    Check if you see 17832 error in SQL Server errorlog.

    #. Open Registry Editor (In the Sql Database Server) and add the following Key:

    HKEY_LOCAL_MACHINESYSTEMControlSet001ControlLsaKerberosParametersMaxTokenSize

    Type: REG_DWORD
    Value (Decimal): 100000

    When connecting to the SQL servers using a domain account that is a member of a lot of groups (everyone on my team is) the server is running out of buffers to process the amount of security the token is passing is causing it to be «structurally invalid»
    as we discovered in the SQL logs.

    Refer http://support.microsoft.com/kb/262177

    Thank you,

    Karthick P.K |My Facebook Page |My
    Site    | Blog space|
    Twitter

    www.mssqlwiki.com

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

Источник

Содержание

  1. An existing connection was forcibly closed by the remote host (OS error 10054)
  2. When do you see the error?
  3. Scenario 1: No matching TLS protocols exist between the client and the server
  4. Resolution
  5. Scenario 2: Matching TLS protocols on the client and the server, but no matching TLS cipher suites
  6. Resolution
  7. Scenario 3: SQL Server uses a certificate signed by a weak-hash algorithm, such as MD5, SHA224, or SHA512
  8. Resolution
  9. 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
  10. See also
  11. Существующее подключение было принудительно закрыто удаленным узлом (ошибка ОС 10054)
  12. Когда вы видите ошибку?
  13. Сценарий 1. Между клиентом и сервером не существует соответствующих протоколов TLS
  14. Решение
  15. Сценарий 2. Сопоставление протоколов TLS на клиенте и сервере, но без соответствующих наборов шифров TLS
  16. Решение
  17. Сценарий 3. SQL Server использует сертификат, подписанный алгоритмом слабого хэша, например MD5, SHA224 или SHA512.
  18. Решение
  19. Сценарий 4. Клиент и сервер используют TLS_DHE набор шифров для подтверждения TLS, но в одной из систем нет основных исправлений для TLS_DHE, установленных
  20. См. также

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.

Источник

Существующее подключение было принудительно закрыто удаленным узлом (ошибка ОС 10054)

Применяется к: SQL Server

Прежде чем приступить к устранению неполадок, рекомендуется проверить предварительные требования и ознакомиться с контрольным списком.

В этой статье описаны различные причины и приведены способы устранения следующих ошибок:

Подключение к серверу успешно установлено, но затем произошла ошибка при входе. (Поставщик: поставщик SSL, ошибка: 0 — существующее подключение было принудительно закрыто удаленным узлом.)

Соединение было успешно установлено с сервером, но при подтверждении перед входом возникла ошибка. (поставщик: поставщик TCP, ошибка: 0 — существующее подключение было принудительно закрыто удаленным узлом.)

Ошибка операционной системы 10054 возникает на уровне сокетов Windows. Дополнительные сведения см. в статье Коды ошибок сокетов Windows: WSAECONNRESET 10054.

Когда вы видите ошибку?

Secure Channel, также известный как Schannel, является поставщиком поддержки безопасности (SSP). Он содержит набор протоколов безопасности, которые обеспечивают проверку подлинности удостоверений и безопасное частное взаимодействие с помощью шифрования. Одной из функций Schannel SSP является реализация различных версий протокола TLS. Этот протокол является отраслевым стандартом, который предназначен для защиты конфиденциальности информации, сообщаемой через Интернет.

Протокол ПОДТВЕРЖДЕНИЯ TLS отвечает за обмен ключами, необходимый для создания или возобновления безопасных сеансов между двумя приложениями, обменивающимися данными по ПРОТОКОЛу TCP. На этапе перед входом в процесс подключения SQL Server и клиентские приложения используют протокол TLS для создания безопасного канала для передачи учетных данных.

В следующих сценариях подробно описываются ошибки, возникающие, когда подтверждение не удается завершить.

Сценарий 1. Между клиентом и сервером не существует соответствующих протоколов TLS

SSL и версии TLS, предшествующие TLS 1.2, имеют несколько известных уязвимостей. Рекомендуется выполнить обновление до TLS 1.2 и по возможности отключить более ранние версии. Соответственно, системные администраторы могут отправлять обновления с помощью групповой политики или других механизмов, чтобы отключить эти небезопасные версии TLS на различных компьютерах в вашей среде.

Ошибки подключения возникают, если приложение использует более раннюю версию драйвера ODBC, поставщика OLE DB, компонентов платформы .NET или версию SQL Server, которая не поддерживает TLS 1.2. Проблема возникает из-за того, что сервер и клиент не могут найти соответствующий протокол (например, TLS 1.0 или TLS 1.1). Для завершения подтверждения TLS, необходимого для продолжения подключения, необходим соответствующий протокол.

Решение

Для решения этой проблемы воспользуйтесь одним из указанных ниже способов.

  • Обновите SQL Server или поставщиков клиентов до версии, поддерживающей TLS 1.2. Дополнительные сведения см. в статье Поддержка TLS 1.2 для Microsoft SQL Server.
  • Попросите системных администраторов временно включить TLS 1.0 или TLS 1.1 как на клиентском, так и на серверном компьютерах, выполнив одно из следующих действий:
    • Используйте средство шифрования IIS (раздел Наборы шифров) для проверки и внесения изменений в текущие параметры TLS.
    • Запустите редактор реестра и найдите разделы реестра, относящиеся к Schannel: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNEL .
      Дополнительные сведения см. в разделах Рабочий процесс обновления TLS 1.2 и Ошибки SSL после обновления до TLS 1.2.

Сценарий 2. Сопоставление протоколов TLS на клиенте и сервере, но без соответствующих наборов шифров TLS

Этот сценарий возникает, когда вы или ваш администратор ограничили определенные алгоритмы на клиенте или сервере для дополнительной безопасности.

Клиентские и серверные версии TLS, наборы шифров можно легко изучить в пакетах Client Hello и Server Hello в трассировке сети. Пакет Client Hello объявляет все наборы шифров клиента, а пакет Server Hello указывает один из них. Если соответствующие наборы отсутствуют, сервер закроет подключение вместо ответа на пакет Server Hello .

Решение

Чтобы проверить проблему, выполните следующие действия.

Если трассировка сети недоступна, проверьте значение функций в этом разделе реестра: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlCryptographyConfigurationLocalSSL0010002

Используйте эту команду PowerShell для поиска функций TLS.

Используйте такое средство, как ШИФРОВАНИЕ IIS (раздел Наборы шифров), чтобы проверить наличие соответствующих алгоритмов. Если соответствующие алгоритмы не найдены, обратитесь в службу поддержки Майкрософт.

Сценарий 3. SQL Server использует сертификат, подписанный алгоритмом слабого хэша, например MD5, SHA224 или SHA512.

SQL Server всегда шифрует сетевые пакеты, связанные с входом. Для этого используется вручную подготовленный сертификат или самозаверяющий сертификат. Если SQL Server находит сертификат, поддерживающий функцию проверки подлинности сервера в хранилище сертификатов, он будет использовать сертификат. SQL Server будет использовать этот сертификат, даже если он не был подготовлен вручную. Если эти сертификаты используют слабый хэш-алгоритм (алгоритм отпечатка), например MD5, SHA224 или SHA512, они не будут работать с TLS 1.2 и вызывают ранее указанную ошибку.

Эта проблема не затрагивает самозаверяемые сертификаты.

Решение

Для устранения этой проблемы выполните следующие действия:

  1. В диспетчер конфигурации SQL Server разверните узел SQL Server Конфигурация сети в области Консоли.
  2. Выберите Протоколы в поле экземпляра.
  3. Перейдите на вкладку Сертификат и выполните соответствующий шаг:
    • Если отображается сертификат, выберите Вид , чтобы проверить алгоритм отпечатка и убедиться, что в нем используется алгоритм слабого хэша. Затем нажмите кнопку Очистить и перейдите к шагу 4.
    • Если сертификат не отображается, просмотрите в журнале ошибок SQL Server запись, похожую на следующую, и запишите значение хэша или отпечатка:
      2017-05-30 14:59:30.89 spid15s The certificate [Cert Hash(sha1) «B3029394BB92AA8EDA0B8E37BAD09345B4992E3D»] was successfully loaded for encryption
  4. Чтобы удалить проверку подлинности сервера, выполните следующие действия.
    1. Выберите Запустить>запуск и введите MMC. (MMC также называется консолью управления Майкрософт.)
    2. В MMC откройте сертификаты и выберите Учетная запись компьютера на экране оснастки Сертификаты .
    3. Разверните узел Личные>сертификаты.
    4. Найдите сертификат, который SQL Server использует, по его имени или проверив значение отпечатка различных сертификатов в хранилище сертификатов, и откройте его область Свойства.
    5. На вкладке Общие выберите Включить только следующие цели и снимите флажок Проверка подлинности сервера.
  5. Перезапустите службу SQL Server.

Сценарий 4. Клиент и сервер используют TLS_DHE набор шифров для подтверждения TLS, но в одной из систем нет основных исправлений для TLS_DHE, установленных

Если эта статья не устранена, вы можете проверить, могут ли статьи о распространенных проблемах с подключением помочь.

См. также

Заявление об отказе от ответственности за сведения о продуктах сторонних производителей

В этой статье упомянуты программные продукты независимых производителей. Корпорация Майкрософт не дает никаких гарантий, подразумеваемых и прочих, относительно производительности и надежности этих продуктов.

Источник

Источник

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.

Источник
  • Remove From My Forums

  • Question

  • All,

    situation:

    SSMS 2012 client — SS 2008R2 server

    I can connect successfully to the database server using management studio but when I try to, for example, ask properties of a user database, I get the error below.

    Some facts:
    * connecting with SSMS on the server itself is no problem
    * connecting to another SS2008 server is no problem
    * no connection limits is set on the server

    Any ideas?

    Error message:

    TITLE: Microsoft SQL Server Management Studio
    ——————————

    A connection was successfully established with the server, but then an error occurred during the login process. (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

Answers

  • Hi PVKERC,

    First please try to make sure the username and password is correct. if you use the SQL Server authentication, then please make sure that it is enabled.

    After that, because you can connect to the SQL Server, but failed with the logon, As the error:10054 says: An existing connection was forcibly closed by the remote host. Then I think we should try to fix the connection.

    Please try to use the following method to fix the error:

    Use the regedit.exe utility to add a new DWORD value named SynAttackProtect to the registry key:                  

    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters with value data of 00000000.

    Please pay attention to that setting this registry key may expose the server to a SYN flood, denial-of-service attack. Add this registry value only if necessary and with an understanding of the security risks. Remove this registry value when testing is complete.

    Please refer to :
    how to fix the error 10054:
    http://msdn.microsoft.com/en-us/library/ms187005(SQL.105).aspx .

    Regards,
    Amy Peng

    Amy Peng

    TechNet Community Support

    • Edited by

      Tuesday, September 25, 2012 1:01 AM

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

  • Hello ,

    Check if you see 17832 error in SQL Server errorlog.

    #. Open Registry Editor (In the Sql Database Server) and add the following Key:

    HKEY_LOCAL_MACHINESYSTEMControlSet001ControlLsaKerberosParametersMaxTokenSize

    Type: REG_DWORD
    Value (Decimal): 100000

    When connecting to the SQL servers using a domain account that is a member of a lot of groups (everyone on my team is) the server is running out of buffers to process the amount of security the token is passing is causing it to be «structurally invalid»
    as we discovered in the SQL logs.

    Refer http://support.microsoft.com/kb/262177

    Thank you,

    Karthick P.K |My Facebook Page |My
    Site    | Blog space|
    Twitter

    www.mssqlwiki.com

    • Marked as answer by
      Maggie Luo
      Tuesday, September 25, 2012 2:22 AM

Источник

Понравилась статья? Поделить с друзьями:
  • Sql server setup has encountered the following error
  • Sql server raise error
  • Sql server management error 26
  • Sql server login failed for user microsoft sql server error 18456
  • Sql server linked server error 18456