View IP of connection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

View IP of connection

Author

Topic

geossl
Yak Posting Veteran

85 Posts

Posted - 2004-05-10 : 03:36:46

Dear All,
Using app_who can view a list of connected PC but without the IP address.

Is there any way to view the IP address of the connection?

Thanks.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-10 : 11:25:50

quote:
Originally posted by geossl

Dear All,
Using app_who can view a list of connected PC but without the IP address.

Is there any way to view the IP address of the connection?

Thanks.

Use the ARP dos command. You need admin rights to run this but here's the code for a stored proc to add to the master db:

CREATE PROCEDURE SP_SPIDtoIP @SPID int
AS

-- SPID to MAC
DECLARE @MAC as varchar(12)

SELECT @MAC = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID

-- MAC to IP
DECLARE @MACDisplay as varchar(18)
DECLARE @IP as varchar(15)

CREATE TABLE #temp (OUTPUT varchar(255))
SET NOCOUNT ON
INSERT INTO #temp EXEC master..xp_cmdshell 'arp -a'

SELECT @MACDisplay = LEFT(@MAC, 2) + '-' + SUBSTRING(@MAC, 3, 2) + '-' + SUBSTRING(@MAC, 5, 2) + '-' + SUBSTRING(@MAC, 7, 2) + '-' + SUBSTRING(@MAC, 9, 2) + '-' + SUBSTRING(@MAC, 11, 2)
SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE output LIKE '%' + @MACDisplay + '%'

select @ip

-- Resolve the IP
DECLARE @CMD as varchar(100)

select @CMD = 'master..xp_cmdshell "ping -a ' + @IP + '"'
exec (@CMD)

DROP TABLE #temp
SET NOCOUNT OFF

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources