How to know a computer´s ip connected to a SQL server

heze54
heze54 used Ask the Experts™
on
Hi,

Using activity monitor under SQL management Studio, I can see a computer called xxx. I do not know who has this machine name  and where is connected from . I can not resolve the machine name.

How can I know it´s ip address?

regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
in the command prompt.. type
netstat -aon
and you will get to know all the ip's that are making connections to the machine.
Top Expert 2011

Commented:
From the command line:

nslookup xxx
Top Expert 2011
Commented:
From SQL Server, you can also check the client_net_address column in the sys.dm_exec_connections table.  Something like this:
select s.session_id, s.host_name, c.client_net_address from sys.dm_exec_sessions s
join sys.dm_exec_connections c on c.session_id = s.session_id
where s.host_name = 'xxx'

Open in new window

Author

Commented:
Hi,

I used  tjhe following to get the host´s ip address:

select s.session_id, s.host_name, c.client_net_address from sys.dm_exec_sessions s
join sys.dm_exec_connections c on c.session_id = s.session_id
where s.host_name = 'nerja'


Where nerja is the name shown at activity monitor., As I can see, the  result is an ip. But that ip is used by a server not call nerja. How can be this possible?


Regards
Top Expert 2011

Commented:
The host_name is provided by the client application, so it can be inaccurate.  Refer to the WSID provider string keyword on the following link:

Using Connection String Keywords with SQL Server Native Client
http://msdn.microsoft.com/en-us/library/ms130822%28v=sql.105%29.aspx
WSID
The workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the HOST_NAME function.

More here:
The client application provides the workstation name and can provide inaccurate data. Do not rely upon HOST_NAME as a security feature.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial