-1

Is there a way to log login attempts in SQL Server, including machine name together with the machine IP address?

I read something about login triggers, but I don't know if it's useful for my needs.

  • What version of SQL server are you using? – KASQLDBA Nov 10 '15 at 8:57
  • both 2008 r2 and 2012 – basteez Nov 10 '15 at 9:01
1

You can enable Login auditing -- In SQL Server Management Studio, open SQL Server Properties > Security > Login Auditing.

There are four options available:

  1. None - Neither successful nor failed logins will be audited.
  2. Failed logins only - Failed logins will be audited, but successful logins will be ignored.
  3. Successful logins only - Successful logins will be audited, but failed logins will be ignored.
  4. Both failed and successful logins - Login will be audited regardless of success and failure

Once you click OK, settings will save and take effect after the restart of the service.

Once you've turned on auditing, the next step is to be able to find the results of auditing required. You can find this recorded in the SQL Server log.

On how to read the sql server log you can refer here..

In SQL server 2012 you can use SQL Server Extended Events to capture failed logins -

To set up Auditing using extended events and SQL server Audit you can read Using Extended Events to review SQL Server failed logins - -

  • yes, this works fine, but I need also machine name because I want to know if the login has been made from a domain pc or from an external one, while this system logs only the ip address. Is this possible? – basteez Nov 10 '15 at 9:39
  • Yes its possible, If you have log management software, another good place to look is the Application event log for the operating system. You can view this by using the Computer Management tool. If you expand System Tools and then Event Viewer, you'll see the Application event log wher you can see the details like success or failure Audit. open its properties and you can see source and user for further details – KASQLDBA Nov 10 '15 at 9:44
  • unfortunately the OS registers the server name even for remote logons, I'll try to figure out if this behavior can be changed – basteez Nov 10 '15 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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