FRIENS, Is possible to get the local IP since a store procedure??
-
FRIENDS , Is posiible to get the Local IP of a client from a store procedure ?
Example exec sp_YourIP
Your ip is 228.123.22.34
Thanks a lot of a someone that can help me
-
I remember this one from before
See this thread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=250528
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
mmm but the examples that i found only they show the ip from server but i need the ip from my local user
is possible?
-
Since the procedure lives on the server, the client must tell the server which IP it has when it connects.
SQL Server cannot 'reach out' and get the IP from the remote client.
/Kenneth
-
Did you read the whole thread?
The upshot was that it's not possible...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Oh it is a pity, and not exists some trick?
-
Not without some help. Your end, the server (SQL Server) needs to either:
1) Be told by the connection which IP it comes from
or...
2) Find some place where it can read logged IP addresses (eg webserverlogs or similar) - if you have a place that logs connecting IP addresses.. SQL Server doesn't do that, so it doesn't know which IP each connection comes from.
The problem however, if you do have some other place to read connecting IP's from, is how to connect those to a specific spid in SQL Server.
I'm guessing it would depend on what 'other IP source' you may have, if it would be at all possible.
What the server does know, is which MAC address that the connecting client has. Depending on what you want to use the IP address for, the MAC may serve the purpose of identifying difference between clients, if nothing else.
/Kenneth
-
There ought to be a way to get the local MAC address, because the client is apparently passing it to the server (it's visible in the current activity pane).
But the local IP may not be visible to the server, if there is a NAT layer (address-translation) between client and server.
-
Well if you really want to try here you go:
if object_id('tempdb..#ipaddress') is not null
drop table #ipaddress
create table #ipaddress ( address varchar(80) null )
declare @cmd varchar(300)
select @cmd = 'ping -n 1 ' + Host_Name()
insert into #ipaddress( address ) exec master..xp_cmdshell @cmd
select rtrim(ltrim(replace(replace(address,'Ping statistics for',''),':',''))) IP
from #ipaddress where address Like 'Ping statistics for%'
Cheers,
* Noel -
OH Wonderful it works thank you GrandMaster
-
Good work noeld! Where were you for the other thread?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Nice one Noel
Though the fact that it apparently works raises some questions about security..
The connection needs to be able to use xp_cmdshell. Haven't looked what the proxy account would need as minimum perms to be able to execute ping.exe, but it could be an issue. If the proxy account isn't used, it means that the connection is sysadmin..
(if it works that is)Be prepared that it may not always work - that is, host_name() may be empty, or the host may not answer unsolicited pings, due to firewall rules, router filterings and what have you. Though if it works for our poster, then it's great.
..just be wary of the exec xp_cmdshell grants, and be prepared that no IP may be returned for reasons out of one's own control.
/Kenneth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply