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 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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

From How to get Client IP Address in SQL Server

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

Method 2

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

select CONNECTIONPROPERTY('client_net_address') AS client_net_address

Method 3

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

Output:

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

Method 4

it needs just single line of code

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

Method 5

Below query returns IP address and the workstation name of the client machine (not the server).

SELECT CONNECTIONPROPERTY('client_net_address') AS IpAddress,
       HOST_NAME() AS ClientMachineName

Reference:
https://docs.microsoft.com/en-us/sql/t-sql/functions/host-name-transact-sql?view=sql-server-ver15

Example

The following example creates a table that uses HOST_NAME() in a DEFAULT definition to record the workstation name of computers that insert rows into orders table.

CREATE TABLE Orders_demo  
(
  OrderID     INT        PRIMARY KEY,  
  OrderDate   DATETIME   NOT NULL,
  Workstation NCHAR(30)  NOT NULL     DEFAULT HOST_NAME()
);

Method 6

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

SELECT SERVERPROPERTY(N'MachineName');

… which can act in place of numeric IP address.

Method 7

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0