Intermittent "login failed for user 'sa'"

[venablc]

I have a number of desktop applications all built using .net framework 3 which seem to go through phases of intermittently throwing an error when connecting to our database. The error is a simple "login failed for user 'sa'" as if the user name is incorrect but as it works the majority of the time it obviously is not.

I am using the sqlClient.sqlClientConnection with native drivers with all my applications and connecting to a sql2005 database which is mirrored to a backup.

Has anyone heard of or experiencing anything like this before? this is proving a tough one to diagnose!

[brucevde]

If the option "Audit Login Failures" is on (the default) then the SQL Server log files might tell you the reason why.

[venablc]

There is no mention of the errors in the SQLserver logs or the windows logs, where can I check that "Audit login failures" is on?

[venablc]

where can I check that "Audit login failures" is on?

has anyone experienced this before?

[brucevde]

This is a Server property. In management studio right click the server name and click properties. You will find the Login Auditing options on the security tab. This is what is written to the SQL Server Log file when an invalid password is used.

2009-05-28 07:20:30.33 Logon Error: 18456, Severity: 14, State: 8.
2009-05-28 07:20:30.33 Logon Login failed for user 'softuser'. [CLIENT: <local machine>]