Finding Host Names for Failed login attempts!

The Intro

If you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log.

Here’s some common messages around failed logins from the error log:

  • Login failed for user “<username>”. Reason: Password did not match that for the login provided. [CLIENT:]
  • Login failed for user “<username>”. Reason: An attempt to login using SQL authentication failed. [CLIENT:]
  • Login failed for user “<username>”. Reason: Failed to open the explicitly specified database ‘<databasename>’. [CLIENT:]
  • Login failed for user “<username>”. Reason: Could not find a login matching the name provided. [CLIENT:]
  • Login failed for user “<username>”. Reason: Access to server validation failed while revalidating the login on the connection. [CLIENT:]
  • Login failed for user “<username>”. Reason: The account is currently locked out. [CLIENT:]
  • Login failed for user “<username>”. Reason: Failed to open the database ‘<databasename>’ configured in the session recovery object while recovering the connection. [CLIENT:]

The script below uses xp_cmdshell and nslookup to lookup the IP address from the error message against your DNS server for the name of the client machine. This can be useful for quickly identifying where all those failed logins are coming from, without requiring a manual nslookup.

A small caution!

Since I’m using xp_cmdshell in this script, you obviously need to have that enabled, which might be considered a potential security issue. I tend to ensure no one has sysadmin rights that isn’t actually a bonafide systems administrator; so I’m not super worried about an escalation-of-privilege attack via xp_cmdshell.

Paying the rent!

So, homies, on to the script1:

Let me know if you like this script, and also, let me know if you think it stinks!

This post forms part of our series on SQL Server Security.

1 – yes, the script rendering in this post sucks. Too many double-quotes-inside-single-quotes, or something!