locked
SQL Server Getting Frequent Timed-out RRS feed

  • Question

  • Hello Everyone,

    We are getting frequent timeout issues on our SQL QA Environment. Upon checking the sql logs we are getting the below error messages

    SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed

    Please provide us the solution for this issue.

    Thanks,

    Rokesh


    Rokesh

    Monday, March 9, 2020 4:59 AM

All replies

  • Hello.

    It happens when the SPNs for the service account have not been created in the Archive Directory. The error message will show up periodically, and may have different error codes associated with it. It will becomes normal you need not worry about it.

    Monday, March 9, 2020 5:09 AM
  • No actually our application is getting timed-out frequently some times its not loading.

    Intermittently we are receiving this issue.

    THanks,

    Rokesh


    Rokesh

    Monday, March 9, 2020 5:30 AM
  • How the application connect to SQL Server? Windows Authentication?

    What is server authentication mode?

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?redirectedfrom=MSDN&view=sql-server-ver15

    But also consider  this may happen when the SPNs for the service account have not been created in Active Directory.  Have a look at this link for more information:

    http://technet.microsoft.com/en-us/library/bb735885.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 9, 2020 5:35 AM
  • Sometimes intermittent failures are because you need to adjust the connection string to include 

    MultiSubnetFailover=True

    it's required if you use an AG listener: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-ver15

    • Edited by stueeeeee Monday, March 9, 2020 7:58 AM
    Monday, March 9, 2020 7:58 AM
  • Hi Rokesh

    You could try to add the SQL Server Service account to “Access this computer from the network” in local security policy (secpol.msc) -> User Rights Assignment. For more details, please refer to Common ‘SSPI handshake failed’ errors and troubleshooting.

    Hope this could help you.

    Best Regards,

    Amelia



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 6:38 AM
  • Hi Rokesh,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If not, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 16, 2020 1:20 AM
  • The Application connecting to SQL using mixed authentication.

    But this issue occurs frequently 5 minutes the application is showing blank page and again it is started working.


    Rokesh

    Wednesday, March 18, 2020 6:43 AM
  • we didn't done any changes in the connection string. 

    Rokesh

    Wednesday, March 18, 2020 6:44 AM
  • Hello Amelia,

    Could you please tell me how to get the SQL Server account so that i can check whether the account available in Local Security Policy.


    Rokesh

    Wednesday, March 18, 2020 6:45 AM
  • Hi Amelia,

    Unfortunately the issue still exists.

    Issue remains same and i am getting the new error in event viewer.

    "Unknown SQL Exception 0 occurred. Additional error information from SQL Server is included below.

    The target principal name is incorrect.  Cannot generate SSPI context."

    Please help me to provide the fix for this issue.


    Rokesh

    Wednesday, March 18, 2020 6:47 AM
  • Also i am receiving the below error message in security logs.

    An account failed to log on.

    Subject:
    Security ID: NULL SID
    Account Name: -
    Account Domain: -
    Logon ID: 0x0

    Logon Type: 3

    Account For Which Logon Failed:
    Security ID: NULL SID
    Account Name: NML-SID******
    Account Domain: **********

    Failure Information:
    Failure Reason: Unknown user name or bad password.
    Status: 0xC000006D
    Sub Status: 0xC000006A

    Process Information:
    Caller Process ID: 0x0
    Caller Process Name: -

    Network Information:
    Workstation Name: JP-NML-*********
    Source Network Address: **********
    Source Port: 51981

    Detailed Authentication Information:
    Logon Process: NtLmSsp 
    Authentication Package: NTLM
    Transited Services: -
    Package Name (NTLM only): -
    Key Length: 0

    This event is generated when a logon request fails. It is generated on the computer where access was attempted.

    The Subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

    The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).

    The Process Information fields indicate which account and process on the system requested the logon.

    The Network Information fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

    The authentication information fields provide detailed information about this specific logon request.
    - Transited services indicate which intermediate services have participated in this logon request.
    - Package name indicates which sub-protocol was used among the NTLM protocols.
    - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.


    Rokesh

    Wednesday, March 18, 2020 7:05 AM
  • Hi Rokesh,

    You could check the SQL Server Service account in SQL Server Configuration Manager-> right-click SQL Server Service-> Properties ->Log on.

    According to the error message, you need to add the SPNs for the SQL Server service account to Active Directory. Please refer to How to troubleshoot the "Cannot generate SSPI context" error message for more details.

    In addition, here is a tool may help you to troubleshoot this issue.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 19, 2020 8:28 AM
  • Hello Amelia,

    SPN's already set for SQL Account and it is not related with SPN.

    when ever the application is down for 1 minute i can the below error frequently

    "Unknown SQL Exception 18452 occurred. Additional error information from SQL Server is included below.  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."


    Rokesh

    Wednesday, March 25, 2020 7:22 AM
  • Hi Rokesh,

    >>Unknown SQL Exception 18452 occurred. Additional error information from SQL Server is included below.  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    Please make sure the SQL Service account is in the AD server.

    Please refer to the following threads which might help:

    SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

    Login error - The login is from an untrusted domain and cannot be used with Windows authentication

    SQL Server 2008 Windows Auth Login Error: The login is from an untrusted domain

    SQL Server Troubleshooting: Login is from an untrusted domain and cannot be used with Windows authentication

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 26, 2020 7:52 AM
  • I am still facing the same issue.

    Workarounds applied so far:

    1) Added the disableloopbackcheck in registry editor on Sharepoint WFE Server and DB Server but the issue remains same. -> Also restarted the server to reflect the changes but no use issue remains same.

    2) Perfomed the config wizzard it was successfull but still the same issue.

    3) checked the ipconfig of both server are pingable and there was no timed-out.

    4) checked the protocols of Sql TCP/IP it was enabled and working fine.

    Issue is:

    1) Login from untrsted domain from windows authentication IP pointing out to WFE Server only.

    2) SSPI Handshake failed.

    3) Target Principal server is incorrect events coming from WFE Server.

    Please let me know what should i do to overcome this issue.

    Also, We noticed that out Sharepoint application is going blank during this issue comes and started working in 1 minute again this is the issue.

    Also, during that time when we tried to login using service account it is showing as account is locked.

    This are my findings, Could anyone help me to find the solution for this issue.


    Rokesh


    Wednesday, April 15, 2020 6:14 AM