If you're running SQL Server 2005 or above you can use this (some shops disable xp_cmdshell):

Select

client_net_address,local_net_address

from sys.dm_exec_connections

where session_id=@@SPId;

Limitation: You only get IP addresses if you're connective via TCP/IP. If you're local and using Shared Memory then those attributes don't exist. If you turn off Shared Memory (or any protocols except for TCP/IP) via Server Configuration Manager you will always get IP address for any connection using this technique.