If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > How to get the client IP Address in T-SQL ?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
How to get the client IP Address in T-SQL ?

Hi,

My problem is -
I have a trigger for auditing the changes(insert/update/delete) in the database table.That is done and is working fine. But I need to have the client's IP address from where the changes are done. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL.

As I have checked many forums, I got that there is extended stored procedure in master database named xp_cmdshell which has xplog70.dll and when we execute this stored procedure with 'ipconfig' we can get the IP Address. But I do not need that in master database. I need that in my database say myDB.

So how to proceed further. I don't know whether to create extended SP which contains DLL or is there any other option.

Pls help
Thanks in advance

Last edited by neetu_bhagtani; 10-24-07 at 03:58.
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Have you tried calling the stored procedure* from within your "myDB"?
__________________
George
Home | Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
See this the stored procedure code and call,

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 is in the master Database.If I do this same thing in my database say myDB it gives error that it do not have extended SP

Last edited by neetu_bhagtani; 10-24-07 at 03:57.
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Try
Code:
Exec dbo.sp_get_ip_address @ip out
__________________
George
Home | Blog
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
If we exec this stored procedure in the trigger, How to store the output in the table or in some variable. Since I have tried this way - Insert #temp exec master..xp_cmdshell 'ipconfig' which gives error saying that -' insert and execute statments cannot be nested'. And if I first create the table with a column and then write - select * into #temp from exec sp_get_my_ip_address @ip out, it gives error as incorrect syntax near exec
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
Let's just confirm the DBMS that we're using...
I'm guessing SQL Server 2000, but please correct me if I'm wrong.

I'll then move the thread to the appropriate topic.
__________________
George
Home | Blog
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
I am using SQL Server 2005
Reply With Quote
  #8 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
This works in 2000 and 2005 and might be of some use to you - let me know how you get on.
Code:
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
__________________
George
Home | Blog
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
Now my problem is - if the client machine do not have host name assigned in that case how can we ping and get the IP ?
Reply With Quote
  #10 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
You can't...
Back to your method it is then...

Try this
Code:
--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 trgger 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 and assign it to a variable
  SET @ipLine = (
    SELECT ipLine
    FROM   #ip
    WHERE  ipLine LIKE '%IP Address%'
    )
  
  --If the IP 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 2000 and 2005.
__________________
George
Home | Blog

Last edited by gvee; 10-24-07 at 07:48. Reason: Tidied up code and added comments
Reply With Quote
  #11 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,428
Let me see if I understand what the original poster (neetu bhagtani) was looking for, because this line of reasoning doesn't sound correct to me.

You have at least a SQL 2005 server, and a web server in a data center. You have clients that connect to the web server using HTTP, but those clients do not log in to the SQL Server directly.

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.

-PatP
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
See as I told you before I get an error at this line -

INSERT #ip EXEC master..xp_cmdshell 'ipconfig' (as per the code given by you)

Insert #temp exec master..xp_cmdshell 'ipconfig' (and as per the code written by me)

which gives error saying that -' insert and execute statments cannot be nested'.


Also, I would like to tell that I was working on testing environment means on local server but not on live server since it this works on testing server and only we can upload and test on live server. Live Server is SQL Server 2003

Now when I uploaded your pinging version of Stored Procedure I get an error telling that master DB owner is someone else and when I give '[dbo].master' for executing the xp_cmdshell it gives another error saying that server is not the sysservers list use 'sp_addlinkedserver' Stored Procedure to add the server in the sysservers list

Pls help
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
Yes, as described below is very much true

Quote:
If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.
Reply With Quote
  #14 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,879
So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)
__________________
George
Home | Blog
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Oct 2007
Posts: 15
Some users will access the application on web server from UK, some from US, and some from India. So all will connect to same web server on which the application resides but will have different IP addresses of their machines.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On