How to fix Error 9786 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 9786 – “Cannot Retrieve User Name from Security Context”

Why This Error Happens

This error occurs when SQL Server 2025 is unable to resolve or validate the Windows security token associated with an incoming authentication request, typically due to a broken trust relationship between the server and Active Directory/Domain Controller. It is commonly triggered by SSPI (Security Support Provider Interface) handshake failures, often resulting from expired Kerberos tickets, misconfigured Service Principal Names (SPNs), or a corrupted local security context cache. In some cases, this also stems from the SQL Server service account lacking the necessary “Act as part of the operating system” or “Impersonate a client after authentication” privileges on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Domain Connectivity and Time Sync

  • Confirm the SQL Server host can communicate with the Domain Controller using nltest /sc_query:<DomainName>.
  • Check that the system clock is within the 5-minute Kerberos tolerance window by comparing w32tm /query /status output against the DC.
  • Resync time if necessary using w32tm /resync /force.

Step 2: Re-register the Service Principal Name (SPN)

  • Open an elevated Command Prompt and check existing SPNs with setspn -L <SQLServiceAccount>.
  • If missing or duplicated, remove the stale SPN using setspn -D MSSQLSvc/<ServerFQDN>:1433 <AccountName>.
  • Re-register the correct SPN with setspn -A MSSQLSvc/<ServerFQDN>:1433 <AccountName>.
  • Restart the SQL Server (MSSQLSERVER) service via services.msc to apply the SSPI context refresh.

Step 3: Validate Local Security Policy Permissions

  • Navigate to secpol.msc > Local Policies > User Rights Assignment.
  • Ensure the SQL Server service account is listed under “Impersonate a client after authentication” and “Act as part of the operating system.”
  • If the policy was recently changed via Group Policy, run gpupdate /force on the server and reboot to apply inherited domain policies.

Step 4: Clear the Kerberos Ticket Cache

  • Open a command prompt as the SQL Service account (or via PsExec if testing) and run klist purge to clear stale tickets.
  • Restart the Netlogon service and the SQL Server service to force the generation of fresh security tokens.

Alternative or Verification Steps

  • Verify the Fix: Attempt to log in via SQL Server Management Studio (SSMS) using Windows Authentication; check the SQL Server Error Log for the absence of Error 9786 upon the next connection attempt.
  • Alternative Fix: If the issue persists in a Windows 11 client environment connecting to the server, disable “Extended Protection” in the SQL Server Network Configuration (via SQL Server Configuration Manager) to rule out channel binding token mismatches.