A few months ago, we put in more layers of monitors on our production database servers. One such improvement is based on logins. Both OS logins and Sql Server logins. On any given regular day, not many people log into production servers (OS – login); May be 1 or 2 in a day. So, it will be good monitor if anyone logs in and causes repeated login failures. Similarly for database servers.

For database servers, we monitor all login failures —  as there should not be any DB login failures. If repeated failures occur that it is an indication of a problem. Either unauthorized principal attempt or DDoS attack or authorized principal attempt. Either way it needs to be monitored.

With all this extra monitoring, we’ve been receiving a lot of login alerts. But most of them are false positives — like Password Expiration, Password reset, User account locked, etc. After getting too many of these, we started looking into them. A typical alert message would look like this:

Monitor:                 SQLApplicationLogMonitors: DB-SVR1 -- Security Login Failures with ID 4776
Group:                   SQL Application Log Monitors
Status:                  Microsoft-Windows-Security-Auditing:4776 on DB-SVR1, 
Category:                14336
Message:                 The computer attempted to validate the credentials for an  account.
Authentication Package:  MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Logon Account:           JohnDoe
Source Workstation:      DB-SVR1
Error Code:              0xc0000071
Matched on:              Event: Microsoft-Windows-Security-Auditing:4776
Type:                    Error
Timestamp:               18:29:19 01/10/14
Sample #:                35013

Upon looking into this further, we understood that these false positives errors are not errors, but log entries indicated the nature of action under the category called “security” — we were monitoring everything under this category.

Following table shows, what each of these Error Codes mean:

Error Code:

Equipped with this better understanding we were able to configure the monitors to only look for real errors and not informational.

Hat tip to my colleague Bill for teaching me this.

Hope this helps,
_SqlTimes