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
Enabling Kerberos Event Logging on a Specific Computer
Start Registry Editor.
Add the following registry value:
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
Server Time: 22:26:27.0000 11/19/2010 Z
Error Code: 0x25 KRB_AP_ERR_SKEW
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.
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
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.