Obtaining machine names for failed logins

The SQL Server error log contains invaluable details about failed logins, but only if you enable login auditing. Once enabled, the error log will provide the details of logins that failed including the name of the login, the IP address of the machine where the login originated, and the cause of the failure. Even with all those great details, it can still be difficult to accurately understand which machine caused those failed logins, since the error log only shows the IP address, not the machine name.

get the name for failed logins

  So much beauty!

The code below analyzes the SQL Server error logs for messages relating to failed logins. Helpfully, it also obtains the DNS machine name for the computers where the failed logins originated.

The code requires the use of the extended stored procedure sys.xp_cmdshell, which it uses to query DNS for machine names. See the xp_cmdshell Microsoft Documentation for more details.

In order to run the above code, you need to enable xp_cmdshell, and you’ll need to enable auditing of failed logins. The following code enables both items:

The output looks similar to:

+---------------------+--------+--------------------------------+--------------+----------------------+
| Most Recent Failed  | Login  | FailureReason                  | ClientIP     | ClientName           |
| Login Attempt       | Name   |                                |              |                      |
+---------------------+--------+--------------------------------+--------------+----------------------+
|                     |        |                                |              |                      |
| 2016-06-09 11:33:58 | usr1   | The system administrator can   | 192.168.0.10 | machine1.domain.com  |
|                     |        | unlock it                      |              |                      |
|                     |        |                                |              |                      |
| 2016-06-09 10:33:15 | usr2   | Reason: Password did not match | 192.168.0.11 | machine5.domain.com  |
|                     |        |  that for the login provided   |              |                      |
|                     |        |                                |              |                      |
| 2016-05-30 07:23:53 | mon    | Reason: Password did not match | 192.168.0.13 | machine4.domain.com  |
|                     |        |  that for the login provided   |              |                      |
|                     |        |                                |              |                      |
| 2016-05-26 15:28:03 | dbo    | Reason: Failed to open the exp | 192.168.0.20 | machine17.domain.com |
|                     |        | licitly specified database     |              |                      |
|                     |        |                                |              |                      |
+---------------------+--------+--------------------------------+--------------+----------------------+

Hopefully this script helped you figure out who caused those failed login attempts! Let me know if you have any questions or comments.

Check out the rest of our posts on security!