Where are SQL Server connection attempts logged?

Question

Does SQL Server has an external log file or internal table for attempted connections, or is that kind of info put somewhere in the Windows Event Log?

Answer 1

Another way to check on connection attempts is to look at the server's event log. On my Windows 2008 R2 Enterprise machine I opened the server manager (right-click on Computer and select Manage. Then choose Diagnostics -> Event Viewer -> Windows Logs -> Applcation. You can filter the log to isolate the MSSQLSERVER events. I found a number that looked like this

Login failed for user 'bogus'. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.12.3.126]

Answer 2

You can enable connection logging. For SQL Server 2008, you can enable Login Auditing. In SQL Server Management Studio, open SQL Server Properties > Security > Login Auditing select "Both failed and successful logins".

Make sure to restart the SQL Server service.

Once you've done that, connection attempts should be logged into SQL's error log. The physical logs location can be determined here.

Answer 3

If you'd like to track only failed logins, you can use the SQL Server Audit feature (available in SQL Server 2008 and above). You will need to add the SQL server instance you want to audit, and check the failed login operation to audit.

Note: tracking failed logins via SQL Server Audit has its disadvantages. For example - it doesn't provide the names of client applications used.

If you want to audit a client application name along with each failed login, you can use an Extended Events session.

To get you started, I recommend reading this article: http://www.sqlshack.com/using-extended-events-review-sql-server-failed-logins/