How to get a client IP address from an SQL Server database

Question

Suppose I have three machines. One is server and the other two are clients. An SQL database has been installed on the server and an SQL Server client is installed on the other two machines. So people can connect to the SQL Server server database from their client machine.

Now I want, whenever anybody do the DML operation on table, then a trigger will fire and from that trigger the user's client machine IP address will be stored in another table. So I search good and found a script which is:

create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
    Declare @ipLine varchar(200)
    Declare @pos int
    set nocount on
    set @ip = NULL
    Create table #temp (ipLine varchar(200))
    Insert #temp exec master..xp_cmdshell 'ipconfig'
    select @ipLine = ipLine
    from #temp
    where upper (ipLine) like '%IP ADDRESS%'
    if (isnull (@ipLine,'***') != '***')
    begin
        set @pos = CharIndex (':',@ipLine,1);
        set @ip = rtrim(ltrim(substring (@ipLine ,
        @pos + 1 ,
        len (@ipLine) - @pos)))
    end
    drop table #temp
    set nocount off
end
go

declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip

But this above script gives a server IP address where the database has been installed. I am not looking for this script. I got another script which works fine. The script is:

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

SELECT dbo.GetCurrentIP()

More scripts

---

DECLARE @host varchar(255)
SET @host = host_name()

CREATE TABLE #Results (
Results varchar(255)
)

DECLARE @cmd varchar(260)
SET @cmd = 'ping ' + @host

INSERT INTO #Results
EXEC master..xp_cmdshell @cmd

SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
 , host_name() As [host_name()]
FROM   #Results
WHERE  Results LIKE 'Pinging%'

DROP TABLE #Results

--*********************************************

-- DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
    DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
    DROP TABLE myTable
END

-- Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1           char(1)
, changed_by_ip    char(15)
, changed_by_host  char(15)
, datetime_changed datetime
)
GO

-- Create trigger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON  myTable
FOR insert, update
AS
DECLARE @ipLine varchar(255)
DECLARE @pos    int
DECLARE @ip     char(15)

-- Temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)

-- Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'

-- Find the line which contains the IP address and assign it to a variable
SET @ipLine = (
SELECT ipLine
FROM   #ip
WHERE  ipLine LIKE '%IP Address%'
)

-- If the IP address is known
IF Coalesce(@ipLine, '***') <> '***' BEGIN
    --Find the index of the colon from the END of the string
    SET @pos = CharIndex(':', Reverse(@ipLine), 1) - 1
    --Trim the IP off the end of the string
    SET @ip =  Right(@ipLine, @pos)
    --Remove any trailing or leading white space
    SET @ip  = RTrim(LTrim(@ip))
END

-- Drop the temp table
DROP TABLE #ip

-- Update the audit fields based on the value being updated
UPDATE myTable
SET    changed_by_ip  = @ip
   , datetime_changed = GetDate()
   , changed_by_host  = host_name()
WHERE  id IN (SELECT id FROM inserted)
GO

-- Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')

-- Display initial values
SELECT * FROM myTable

-- Update one of the fields
UPDATE myTable
SET    field1 = 'b'
WHERE  id = 2

-- Display changed values.
SELECT * FROM myTable

-- Notice the change in datetime_changed where id = 2
GO

-- And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable

This works on my install of SQL Server 2000 and SQL Server 2005.

1. Can I call this GetCurrentIP() function from trigger?

2. Will the GetCurrentIP() function be compatible for all SQL Server versions? For example, SQL Server 2000

3. If any user connects to the database and does a DML operation through third-party applications like a custom application, can this script get the client IP address?

Answer 1

Big Question :) But the answers are really short.

1. Can I call this GetCurrentIP() function from trigger?

Yes. Instead of this function you can directly use the code inside it in your trigger.

 

2. Will the GetCurrentIP() function be compatible for all SQL Server versions? For example, SQL Server 2000?

No. Only for SQL Server 2005 and later. Because sys.dm_exec_sessions was not available in SQL Server 2000. For SQL Server you will have to adopt some different technique

 

3. If any user connects to the database and does a DML operation through third-party applications like a custom application, can this script get the client IP address?

Yes. A client application is a client application whether it's a third-party one or home made :) SQL Server won't differentiate.