Not all login failures are logged in SQL Server 2005

When login attempts with a bad password for a SQL Login are done through a .Net Application which is using System.Data.SQLClient, then out of 10 continuous attempts, SQL Server would log login failures only 1 time

Reason/ Resolution

The above mentioned behavior with System.Data.SQLClient is by design. When we try to connect to SQL server with pooling enabled [which is enabled by default], first it creates a connection in pool & connects to SQL to do further operations. When there is an exception like login failure then a flag in connection pool associate with current connection is set with this error. The flag about the error will not be cleared until the connection is closed.

When we try to connect again with same credential or connection string then it will use the same connection object from the pool & return the message right away. This will avoid the round trip to SQL server and gives better performance.

So as long as we try with the same connection string, the SQLClient doesn’t connect to SQL Server & an error is returned right away to the client. However, we see the message in SQL Error Log if you change the connection string i.e. continuously change the password.

As already mentioned that this is by design to get better performance. To overcome this behavior you may either use

System.Data.OLEDB in your application [use SQLNCLI or SQLOLEDB as provider]

Or

Use Pooling=false in connection string [disable the connection pooling]

Or

Keep on changing the password [this will give you a new connection object from pool because the connection string changes]