70

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?

Improve this question
1
  • 1
    Failed connections show up in the SQL Server log.
    – Martin Smith
    Commented Jul 20, 2011 at 22:10

3 Answers 3

Reset to default
87

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.

Improve this answer
33

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]

Improve this answer
6
  • 2
    This allowed me to find error details without having to restart MSSQL server. Thanks
    – mOrloff
    Commented Dec 22, 2014 at 15:45
  • Glad I could help. It's often the case the the O/S remembers useful stuff in the system logs; Windows, Linux, UNIX, etc.
    – DDay
    Commented Jan 13, 2015 at 3:36
  • If I could give you more than an upvote for an answer, I'd totally do it for this one. Checking the event viewer, I got details as to why the login was failing.
    – g3rv4
    Commented Aug 24, 2015 at 17:40
  • g3rv4 there you go. This didn't help me, but I gave the extra upvote you wanted :)
    – SteveCav
    Commented Mar 2, 2018 at 3:21
  • does it give details of successful connections?
    – The Godfather
    Commented Jun 8, 2020 at 6:43
4

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/

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.