Microsoft sql server error 10054

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

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
Источник

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

Источник

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.

Источник

Понравилась статья? Поделить с друзьями:
  • Microsoft sharepoint ошибка
  • Microsoft setup bootstrapper office 2016 ошибка
  • Microsoft security essentials ошибка подключения при обновлении
  • Microsoft security essentials не обновляется ошибка подключения
  • Microsoft remote desktop ошибка 0х204 mac