SQL Server and SSPI handshake failed error

Recently I faced an issue where users from one domain were unable to login to SQL Server and getting this error:


Error: 17806, Severity: 20, State: 2.

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xxxxxxx]


Error: 18452, Severity: 14, State: 1.

Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: xxxxxx]

We have two domains let's say A and B. My SQL Server is running in domain B. In a normal scenario, both domain users are able to connect. But few days ago, all the users from domain A were unable to connect to SQL Server running in domain B. However domain B users were able to connect it successfully. Users approached SQL Server DBA team to look into the issue.

When I checked, even I was unable to connect to SQL Server remotely using SSMS and using credentials of domain A. However when I tried to RDP the server using domain A's credential it was working fine and there after remote access to SQL server also started working fine. So it looked to me like when I RDPed the server something got refreshed and my login remotely started working fine. I was sure that the actual issue was something related to AD and kerberos authentication but unable to prove so.

I researched this issue on web and found two solutions to fix it. One was to fix malfunctioning DC and second was to restart the server where SQL Server was running. I went the 2nd way as windows admins were not accepting that it is an DC issue. I checked everything from SQL end nothing got changed recently. Also found that this issue started happening after below event was logged on server where SQL Server was running:


Log Name: System

Source: NETLOGON

Date: xxxxxxxx

Event ID: 5719 Task

Category: None

Level: Error

Keywords: Classic

User: N/A

Computer: xxxxxxxxxx

Description: This computer was not able to set up a secure

Why does a reboot of the server sometimes fix this issue?

I think the OS has an issue talking with the DC (for whatever reason(s) you determine) and when this happens, it just gets out of sync somehow with DC, AD, etc. and a quick fix is to reboot the SQL Server OS when this occurs and there is no network communication with the DC at the time of the reboot, and all comes up fine with everything back in sync at that point. To accurately answer this, you probably need to find the reason first.

Proving that [the] Domain Controller(s) is/are or is/are not the issue

1. Troubleshoot & Consider

-

I'm not suggesting you make a bunch of changes before trying to find
and fix the specific problem beforehand but there are many factors to
consider I would think. It's just something you have to troubleshoot
one step at a time unless there's something obvious causing the issue.

1. Access Perspective

-

In my setup, I'm the domain admin as well as the DBA so I have access
to everything and all servers (two domains with two-way trusts) to
troubleshoot at all levels whereas you may be a bit more restricted
with what you can review, etc. on each server in the loop of the
issue.

1. No Simple Oversight

-

I usually start with the simple items first that don't require changes
and see if there are any obvious issues (e.g. event viewers on all
servers, DNS testing on all servers, review all server configurations,
etc.).

1. No Telling Logic

-

This could be an issue at the OS level of the SQL Server for the
Windows version it is on. This could also be an issue with the NIC of
the SQL Server or a mis-configuration of TCP/IP or DNS settings on the
SQL Server or any of your DCs.

1. Collect & Update

-

Gather information on or ensure the SQL Server is fully updated with
Windows Updates, and ensure server firmware is up-to-date if
applicable with BIOS version as well as any hardware device firmware
updates. The domain admins could also do the same on the DCs but use
caution with making a bunch of changes and do so one step at a time.

1. Best Practices

-

I'm not sure how your AD and DCs are configured topology wise, but
best practices should be followed as best as possible and a DC should
be close to the network where both domains are physically rather than
going across a slower network pipe or routers, etc. which adds another
level where there could be issues or configurations to review.


The DCs should have their TCP/IP settings configured so that their DNS
settings point to other DNS servers or follow best practices otherwise
as per Microsoft for the DCs configurations applicable in your
environment.

1. Request Access or Configuration Disclosure Assistance

-

You'll need to do basic troubleshooting to find the issues to resolve,
ask questions, see what all could be updated and patched or changed,
and get your domain admins to help you troubleshoot or disclose domain
configurations to you if you're not sure how to see if everything is
configured as best it could be for optimum performance and per best
practices.

Important: This not to say there couldn't be a very simple reason this suddenly happened but if nothing has changed anywhere (i.e. OS on DCs or SQL Server, network configurations, AD or forest functional levels, hardware upgrades, Windows Updates, etc.) then you just have to troubleshoot for the reason why one step at a time.

StackExchange Database Administrators Q#116694, answer score: 2