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

User · Answer

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]

User · Answer

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

User · Answer

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

[sql-server-2008] Where are SQL Server connection attempts logged?

Examples related to sql-server-2008

Examples related to logging

Examples related to database-connection