Ошибка sql сервер 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:

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

  • Hey, I have just installed SQL 2017 Server on linux machine, when I connect localy it is working. But on trying to connect from MSSMS it always 

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

    Here is my logdata, anybody please tell me what I did 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.

Источник

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:

enter image description here

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's user avatar

MDCCL

8,3703 gold badges30 silver badges59 bronze badges

asked Jun 7, 2018 at 19:25

Ilse Höl's user avatar

3

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:

enter image description here

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

enter image description here

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

Sting's user avatar

StingSting

2,73811 silver badges19 bronze badges

0

Источник

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 domaintest

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

  1. provider: ssl provider, error: 0 – an existing connection was forcibly closed by the remote host
  2. ssis tcp provider: an existing connection was forcibly closed by the remote host
  3. microsoft sql server an existing connection was forcibly closed
  4. sqlncli11 tcp provider: an existing connection was forcibly closed by the remote host
  5. system.componentmodel.win32exception: an existing connection was forcibly closed by the remote host
  6. microsoft sql server error 10054
Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка srs freelander 1
  • Ошибка sql 1045
  • Ошибка sql при выборке на этой странице
  • Ошибка srs cfff volvo s40
  • Ошибка sql 1044