MHilsher (5/24/2011)
Brian,Thanks for asking. To Clarify the Event Id(s) given above where from the OS Security log. The Error Message numbers that we get in the SQL log at the time of the failures were:
1)
Error: 17806, Severity: 20, State: 14.
-and-
2)
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: xxx.xxx.xxx.xxx].
(The value after "CLIENT:" was a valid IP Address from the webserver machine which I redacted according to my company's policy.)
These are exactly the error I would expect if a user from the C zone attempted to connect to any resource in the B zone as that credential is from an untrusted Domain. What we don't get on the SQL side is any indication of what the database messages are when it WORKS. Only that when it's doing a "explict login", the 4638 events in the webserver's OS security log, that the application layer works and we don't see anything at all in the SQL log.
The error code 0x8009030c means there isn't a trusted connection. So basically, at some point IIS is failing to connect and authenticate properly with the OS where SQL Server resides. Because the login can't be authenticated, SQL Server rejects. As to why it fails after about 3 hours, I'm not familiar enough with Credential Manager to tell you. You might try on an IIS specific forum because this is outside of SQL Server and you would likely get a better answer with folks using Credential Manager more regularly (I don't... we don't allow domains in the DMZ). Failing that, it might be a good idea to open a case with Microsoft Support in case you guys are bumping up against a bug.
K. Brian Kelley
@kbriankelley