How to get the client IP address from SQL Server 2008 itself?

I have a trigger for insert/update/delete. That is working fine. Also, I need the client's IP address from where the changes are made. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL while my trigger will get fired.

Also I go-ogled, and got that there is stored procedure in master database named xp_cmdshell which when executed with ipconfig we can get the IP Address. I think this will work only when you have administrative access to database. Mine hosting is a shared hosting , so I don't have such privilege. Is there any other way out?

Please help

Thanks in advance

Please note: I don't have administrative privileges on my SQL Server 2008 database. I need a solution as an authenticated user.

Another update:

I have got the solution, the query that will work for my scenario is

SELECT hostname, net_library, net_address
FROM sys.sysprocesses 
WHERE spid = @@SPID

It executes as needed but there is only one issue, that net_address is not in IP format. below is mine result:

hostname    net_library     net_address
IPC03       TCP/IP          AE8F925461DE  

I am eager to know:

  1. What is net_address here? Is is MAC Address or Some IP address etc?

  2. Is there any way to convert net_address to ip?

Humble request:

Before answering/commenting/downvoting, I would request you to go through the question, first, thoroughly. I found some guys commented/downvoted without properly going through the question. No issues, everybody makes mistakes. But not every time make mistake. :)


I found something which might work for you

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;

From How to get Client IP Address in SQL Server

Also have a look at this article about Get client IP address

You can try out this solution. It even works on shared hosting:

select CONNECTIONPROPERTY('client_net_address') AS client_net_address 

Ultimately join the two system tables:

SELECT  hostname,
FROM    sys.sysprocesses AS S
INNER JOIN    sys.dm_exec_connections AS decc ON S.spid = decc.session_id
WHERE   spid = @@SPID


hostname | net_library | net_address | client_net_address    
PIERRE   | TCP/IP      | 0090F5E5DEFF|

it needs just single line of code

 SELECT CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))

I couldn't get the exact numeric IP address, instead I got a NULL value because of the limitation of the above statements. The limit is that you only get IP addresses if you're connected 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.

You are best stuck with


... which can act in place of numeric IP address.

try this

DECLARE @IP_Address varchar(255);
SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

select @IP_Address;

Need Your Help

Should I unsubscribe from events?

c# events subscribe unsubscribe

I have 3 questions concerning events:

value_to_boolean deprecated; what's a good replacement?

ruby-on-rails activerecord ruby-on-rails-3.2 deprecated

Is there a "cool-kid-approved" replacement for ActiveRecord::ConnectionAdapters::Column.value_to_boolean in rails 3.2?