Home / server / Questions / 100214
Accepted
Ed Leighton-Dick
Asked: 2010-01-07 13:14:36 +0800 CST

Unknown Kerberos authentication pattern on SQL Servers

  • 772

One of our admins noticed a strange pattern of login messages in the Windows event log on one of our SQL Servers, and after some investigation, I've found that it the same pattern can be found on most (but not all) of our SQL Servers. I cannot seem to find the source, however.

The pattern:

  • A series of four messages related to Kerberos authentication occurs every 29 minutes on affected servers. (No, that's not a typo.)
  • The series is event IDs 552, 540, 576, and 538 (in that order) on Windows 2003 and earlier. On Windows 2008, the series is 4648, 4624, 4672, and 4634.
  • The first message in the series says that the SQL Server's service account is authenticating via Kerberos using my credentials.
  • The second and third messages relate to a login using my credentials via Kerberos and the permissions granted.
  • The fourth message is a logoff.
  • The series always happens in under 1 second.

I've eliminated everything I can think of:

  • No other messages in the Windows event log correlate to the messages.
  • No messages in the SQL Server logs correlate to the messages.
  • No SQL Server Agent jobs run on that scheduler.
  • Task Scheduler has no jobs on any of the affected servers.
  • Our 3rd-party job scheduler does not have any jobs that run on that schedule, nor does it have my credentials.
  • I briefly suspected a 3rd-party monitor that we use, but the messages were produced even when the monitor was completely turned off.
  • No other 3rd-party monitors are used that would have either the service account's credentials or my credentials.
  • Our linked servers use SQL Server authentication, not Windows authentication.
  • The servers are a mix of SQL Server 2000, 2005, and 2008, so it can't be related to any newer SQL Server technologies, such as Service Broker.

Does anyone have any suggestions of anything else to check to find the cause of these messages?

windows sql-server authentication kerberos

1 Answers

  1. Best Answer

    I found the answer by running a different server-side trace. A job in SQL Server Agent requests information from the OS, which causes it to authenticate the job owner with Windows. Something (either Windows or SQL) is apparently caching this authentication so it doesn't have to hit Kerberos as frequently; the 29-minute interval must be the timeout on this cache.

    Thanks, everyone, for your help!

    • 0