Sql server ошибка 10054 - Решение и исправление самых разных ошибок на TopOshibok.ru

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 help\test1

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_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

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»;

Источник

Remove From My Forums

Question
Cannot connect to RAJASAJID\SQLEXPRESS.
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 Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
2014-10-31 01:03:25.43 Server      The service account is 'WORKGROUP\RAJASAJID$'. This is an informational message; no user action is required.
2014-10-31 01:03:25.43 Server      Registry startup parameters: 
	 -d c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\master.mdf
	 -e c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG
	 -l c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mastlog.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 'RAJASAJID\SQLEXPRESS'. 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 Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Binn\ftimport.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 [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2014-10-31 01:03:38.32 spid12s     Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
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:\Windows\Microsoft.NET\Framework64\v4.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 'RajaSajid\Raja 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 Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\dbAccounts_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_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\ 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_MACHINE\SYSTEM\ControlSet001\Control\Lsa\Kerberos\Parameters\MaxTokenSize

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 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

Источник

I have just installed SQL Server 2017 on a Linux machine, when I connect locally it is working. But on trying to connect from SSMS it says:

Error 10054: A connection was successfully established with the server, but then an error occurred during the login process.

Please tell me what’s wrong?

root@vps188507:~# cat /var/opt/mssql/log/errorlog
2018-06-07 14:08:50.38 Server      Microsoft SQL Server 2017 (RTM-CU7) (KB4229789) - 14.0.3026.27 (X64)
        May 10 2018 12:38:11
        Copyright (C) 2017 Microsoft Corporation
        Developer Edition (64-bit) on Linux (Debian GNU/Linux 8 (jessie))
2018-06-07 14:08:50.39 Server      UTC adjustment: -4:00
2018-06-07 14:08:50.39 Server      (c) Microsoft Corporation.
2018-06-07 14:08:50.39 Server      All rights reserved.
2018-06-07 14:08:50.39 Server      Server process ID is 4124.
2018-06-07 14:08:50.39 Server      Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2018-06-07 14:08:50.39 Server      Registry startup parameters:
         -d /var/opt/mssql/data/master.mdf
         -l /var/opt/mssql/data/mastlog.ldf
         -e /var/opt/mssql/log/errorlog
2018-06-07 14:08:50.40 Server      SQL Server detected 2 sockets with 1 cores per socket and 1 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2018-06-07 14:08:50.40 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-06-07 14:08:50.40 Server      Detected 6251 MB of RAM. This is an informational message; no user action is required.
2018-06-07 14:08:50.40 Server      Using conventional memory in the memory manager.
2018-06-07 14:08:50.60 Server      Buffer pool extension is already disabled. No action is necessary.
2018-06-07 14:08:50.78 Server      InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2018-06-07 14:08:50.78 Server      Implied authentication manager initialization failed. Implied authentication will be disabled.
2018-06-07 14:08:50.80 Server      Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2018-06-07 14:08:50.85 Server      The maximum number of dedicated administrator connections for this instance is '1'
2018-06-07 14:08:50.85 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2018-06-07 14:08:50.88 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.
2018-06-07 14:08:50.90 Server      In-Memory OLTP initialized on lowend machine.
2018-06-07 14:08:50.97 Server      Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2018-06-07 14:08:50.99 Server      Query Store settings initialized with enabled = 1,
2018-06-07 14:08:51.01 spid6s      Starting up database 'master'.
2018-06-07 14:08:51.01 Server      Software Usage Metrics is disabled.
2018-06-07 14:08:51.35 spid6s      Resource governor reconfiguration succeeded.
2018-06-07 14:08:51.35 spid6s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2018-06-07 14:08:51.35 spid6s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2018-06-07 14:08:51.46 spid6s      SQL Trace ID 1 was started by login "sa".
2018-06-07 14:08:51.50 spid6s      Server name is 'vps188507'. This is an informational message only. No user action is required.
2018-06-07 14:08:51.56 spid9s      Starting up database 'mssqlsystemresource'.
2018-06-07 14:08:51.56 spid22s     Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2018-06-07 14:08:51.56 spid22s     Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2018-06-07 14:08:51.57 spid6s      Starting up database 'msdb'.
2018-06-07 14:08:51.57 spid9s      The resource database build version is 14.00.3026. This is an informational message only. No user action is required.
2018-06-07 14:08:51.60 spid9s      Starting up database 'model'.
2018-06-07 14:08:51.89 spid9s      Polybase feature disabled.
2018-06-07 14:08:51.89 spid9s      Clearing tempdb database.
2018-06-07 14:08:51.89 spid19s     A self-generated certificate was successfully loaded for encryption.
2018-06-07 14:08:51.90 spid19s     Server is listening on [ 'any' <ipv6> 1433].
2018-06-07 14:08:51.90 spid19s     Server is listening on [ 'any' <ipv4> 1433].
2018-06-07 14:08:51.90 Server      Server is listening on [ ::1 <ipv6> 1434].
2018-06-07 14:08:51.90 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-06-07 14:08:51.91 Server      Dedicated admin connection support was established for listening locally on port 1434.
2018-06-07 14:08:51.92 spid19s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-06-07 14:08:52.39 spid9s      Starting up database 'tempdb'.
2018-06-07 14:08:52.60 spid9s      The tempdb database has 1 data file(s).
2018-06-07 14:08:52.60 spid24s     The Service Broker endpoint is in disabled or stopped state.
2018-06-07 14:08:52.61 spid24s     The Database Mirroring endpoint is in disabled or stopped state.
2018-06-07 14:08:52.62 spid24s     Service Broker manager has started.
2018-06-07 14:08:52.65 spid6s      Recovery is complete. This is an informational message only. No user action is required.

MDCCL

8,4303 gold badges30 silver badges59 bronze badges

asked Jun 7, 2018 at 19:25

Let’s try a few things:

Option 1: First on the source SQL server (trying to connect to your Linux SQL Server) check to make sure named pipes (and TCP/IP) is enabled on the source SQL Server via SQL Server Configuration Manager (if the source SQL Server (SSMS) runs on Windows). I have two client protocols to enable on Windows both 32bit and 64bit:

Option 2: Try changing SQL Server to mixed mode and try logging in with a SQL account (but I’m assuming you’ve done this already).

Option 3: Just saw these various changes some people tried. Some of these changes look quite odd but worked for some people:

https://stackoverflow.com/questions/27267658/no-process-is-on-the-other-end-of-the-pipe-sql-server-2012?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

Option 4: Open ports 1433 and 1434 on Linux (might need to do this on source AND target servers):

Allow Port 1433 Ubuntu Linux for SQL Server

Option 5: (You may have said this a different way), but connect via SSMS using the target SQL Server’s IP address.

Option 6: Use the fully qualified SQL Server name (FQDN) to connect to the target SQL Server. (i.e. the way you’d connect to SQL Server on Azure).

Option 7: Try using tcp:servername,1433 within SQL Server Management Studio to connect to the target Linux SQL Server

answered Jun 8, 2018 at 15:19

StingSting

2,74811 silver badges19 bronze badges

Источник

What causes the error SQL server error 10054?

How to fix the error SQL server error 10054?

Check if SPN is registered by SETSPN tool.

Check for duplicated SPNs

Disable SYN flooding attack protection

Alternate Solutions

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Question

All replies

Question

Answers

Solution for Resolving the Error

Alternate Solutions for related issue

SQL Server Error Code and solution summary