Find ClientHost using ClientIP – SQL Server 2014

Posted on

Question :

Recently we started getting alerts as below :

  • EventID 18456 – Login failed for user ‘abc’. Reason: Could not find a login matching the name provided. [CLIENT:]
  • EventID 17828 –
    The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT:]

Below are the steps I did so far to find the Hostname using the Client IP in above log ( :

  1. nststat -a

This does not give me any information about Client Host name and says HostName not found .

enter image description here

  1. ping -a

This gives me Reply message for IP mentioned .

  1. nslookup

Domain cant find IP.

What else should I do to find out what is wrong with failed login and whose login it is and from which machine ?

Answer :

Try the following (taken from

declare @IP as varchar(15)
declare @cmd as varchar(1000) 
set @IP='xxxxx' 
SET @cmd = 'ping -a -n 1 ' + @IP 
Create Table #Output (Output varchar(150) default(''))
EXEC xp_cmdshell @cmd 
Begin try 
Select top 1 Replace(LEFT([Output],CHARINDEX('[', [Output])-2),'Pinging ','') as HostName from #Output where Output like 'Pinging%'
End Try 
Begin catch 
Select 'Host name for:' + @IP +' could not be find'
End catch 

Leave a Reply

Your email address will not be published. Required fields are marked *