Microsoft – SQL Server – Authentication Error – Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

Another day, Another (computer) problem.

One does not need to wonder much to determine why IT people have issues with “General Grumpiness”. But, let us leave that for another day.

User emailed asking if the database server is down… I checked a bit and the server appeared up.

User emailed me the exact error message “Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)”.

I tried the following:

  • Changed the “MS SQL Server” service from AD Domain Account to “Local System”, back and forth a few time
  • Used “setspn” to review spn listings
         setspn -L <server-name>
         setspn -L <AD Domain Account> | find “<server-name>”
  • Thought about using klist or/and kerbtray
  • But, could not shake the need to try “w32tm /resync”.  First time I tried it, it failed with a bunch of errors.  The second time, accessed the service palette to stop and restart “Windows Time” (w32time).  And, then issued “w32tm /resync”.
  • Viola — Now user can authenticate using Windows Authentication

Finding the solution through the means detailed above works, but a bit more scientific path would be to enable Kerberos “Event Logging”.

How to enable Kerberos event logging

http://support.microsoft.com/kb/262177/
Enabling Kerberos Event Logging on a Specific Computer

Start Registry Editor.

Add the following registry value:

      \HKEY_LOCAL_MACHINE
      \SYSTEM
      \CurrentControlSet
      \Control
      \Lsa
      \Kerberos
      \Parameters

            Registry Value: LogLevel
            Value Type: REG_DWORD
            Value Data: 0x1

If the Parameters subkey does not exist, create it.

Please remove this registry value when it is no longer needed so that performance is not degraded on the computer. Also, you can remove this  registry value to disable Kerberos event logging on a specific  computer.

The setting will become effective  immediately on Windows Server 2008, on Windows Vista, on Windows Server 2003, and on Windows XP.

For Windows 2000, you must restart the computer.

Review Event logging

Once the registry change is effected, issue the client command\request again and start reviewing the Windows Event Viewer:

For example, on a computer that has this error.

Error:- 0x25 KRB_AP_ERR_SKEW



A Kerberos Error Message was received: on
logon session
Client Time:
Server Time: 22:26:27.0000 11/19/2010 Z
Error Code: 0x25 KRB_AP_ERR_SKEW
Extended Error:
Client Realm:
Client Name:
Server Realm: LAB.COM
Server Name: LDAP/LABDC04.LAB.com/lab.com
Target Name: LDAP/LABDC04.LAB.com/lab.com@LAB.COM
Error Text:  File: 9 Line: d86 Error Data is in record data.

Interpretation:

AD Server (LABDC04) was contacted and it returned error 0x25 KRB_AP_ERR_SKEW.

Using Google came upon MS’s own explanation:

Authentication Errors are Caused by Unsynchronized Clocks

http://technet.microsoft.com/en-us/library/cc780011(WS.10).aspx

Kerberos authentication relies on the date and time that are set on the KDC and the client.  If there is too great a time difference between the KDC and a client requesting tickets, the KDC cannot determine whether the request is legitimate or a replay. Therefore, it is vital that the time on all of the computers on a network be synchronized in order for Kerberos authentication to function properly.

Clock skew can be easily diagnosed by reviewing the information in the System log.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s