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?
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.
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:
What is net_address here? Is is MAC Address or Some IP address etc?
Is there any way to convert net_address to ip?
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) AS BEGIN DECLARE @IP_Address varchar(255); SELECT @IP_Address = client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID; Return @IP_Address; END
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, net_library, net_address, client_net_address 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| 10.38.168.5
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.
DECLARE @IP_Address varchar(255); SELECT @IP_Address = client_net_address FROM sys.dm_exec_connections WHERE Session_id = @@SPID; select @IP_Address;