IP Address instead of Host Name?

Question

Post reply

IP Address instead of Host Name?

Sachin Dedhia

SSCrazy

Points: 2104
More actions
December 3, 2003 at 4:58 am

#160991

Is there any way to get the IP address of the machine conneted to SQL Server? I am not intrested in the machine name.

Regards,
Sachin Dedhia

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

AFAIK, from within SQL Server you're pretty much out of luck.

Server IP you should get with



declare @ip varchar(255)

create table #temptb (grabfield varchar(255))

insert into #temptb exec master.dbo.xp_cmdshell "ipconfig"

select @ip = ltrim(right(grabfield,len(grabfield)-charindex(':',grabfield))) 

from #temptb where charindex('IP Address', grabfield) > 0

print @ip

drop table #temptb

It might work calling command line tools like http://www.sysinternals.com

Have you searched this site?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank kalis on 12/03/2003 05:11:37 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Sachin Dedhia SSCrazy Points: 2104 More actions · Answer 2

Thanks Frank, I did feel I was pretty much out of luck while posting this 🙁

Haven't come across this site. Seems to be a good one 🙂 Running out of time...Will do it sometime later.

Hope better luck next time

Sachin

😉

Regards,
Sachin Dedhia

stanislavpetkov SSC-Addicted Points: 474 More actions · Answer 3

You can execute netstat -na in a command window on the server. The IP addresses next to the ServerIP:1433 will show you who (IP) is trying to connect to the SQL server.

Stanislav Petkov

David Burrows SSC Guru Points: 65141 More actions · Answer 4

Following on from Fank's suggestion, use ping instead

declare @ip varchar(255),@cmd varchar(100) 

set @cmd = 'ping ' + HOST_NAME() 

create table #temptb (grabfield varchar(255)) 

insert into #temptb exec master.dbo.xp_cmdshell @cmd 

select @ip = substring(grabfield, 

charindex('[',grabfield)+1, 

charindex(']',grabfield)-charindex('[',grabfield)-1) 

from #temptb  

where left(grabfield,7) = 'Pinging' 

print @ip 

drop table #temptb

Far away is close at hand in the images of elsewhere.
Anon.