how to get IP address from sql server 2000

Question

how to get IP address from sql server 2000

molecule_kaab

SSC Eights!

Points: 826
More actions
March 23, 2008 at 7:35 am

#379344

I'm trying to write trigger for audit trail purpose.
and want to retrieve the IP address of the client that do the change in database
Is it possible to get the IP address in trigger command
or I have to write store procedure for this by myself instead make use of TRIGGER
THANKS IN AVANCE

Viewing 9 posts - 1 through 8 (of 8 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

On SQL2005 you can get this from [font="Courier New"]select * from sys.dm_exec_connections[/font] in the client_net_address column assuming that their net_transport is TCP.

On Sql2000 it is a challenge. It appears that for most remote connections, the first connection/session from that client will have the MAC address coded in the [font="Courier New"]net_address[/font] column of [font="Courier New"]sysprocesses[/font]. Than you could use [font="Courier New"]xp_CmdShell 'ARP -a'[/font] if your SQL server is also a domain controller to list the current cache of MAC to IP mappings. If it is not a domain controller, well ... we are already beyond the limits of what I know. Someone else will have to take it from here.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

mysat SSC Enthusiast Points: 154 More actions · Answer 2

Were you able to resolve this issue? I am looking for a solution to this issue as well..

Richellere SSC Eights! Points: 902 More actions · Answer 3

Hey Barry,

Tried this and it works for me.

Thanks for the Solution

Richellere

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Thanks Richellere, glad I could help someone ... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jass14 SSC Rookie Points: 39 More actions · Answer 5

To find the Ip address, i made use of this site http://www.whoisxy.com/[/url] & gain solution ..

It provides me nice solution in free cost ..

base128grep Valued Member Points: 53 More actions · Answer 6

Thanks Barry,

You pointed me in the right direction, in regards to sQL2000 I was able to use:

xp_CmdShell 'ipconfig|find "IP Address"'

I had to insert the output into a table variable and use RIGHT(Rtrim(ipaddr),15) to get just the "IP Address"

balaji2005.r SSC Enthusiast Points: 112 More actions · Answer 7

use this query for trigger or procedure it's get the ip address in sql server

declare @ip varchar(40)

exec sp_get_ip_address @ip out

print @ip

balaji2005.r SSC Enthusiast Points: 112 More actions · Answer 8

use this query for trigger or procedure it's get the ip address in sql server

declare @ip varchar(40)

exec sp_get_ip_address @ip out

print @ip