Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    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 04:58.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    Have you tried calling the stored procedure* from within your "myDB"?
    George
    Home | Blog

  3. #3
    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 04:57.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    Try
    Code:
    Exec dbo.sp_get_ip_address @ip out
    George
    Home | Blog

  5. #5
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    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

  7. #7
    Join Date
    Oct 2007
    Posts
    15
    I am using SQL Server 2005

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    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

  9. #9
    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 ?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    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.
    Last edited by gvee; 10-24-07 at 08:48. Reason: Tidied up code and added comments
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,943
    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

  12. #12
    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

  13. #13
    Join Date
    Oct 2007
    Posts
    15
    Yes, as described below is very much true

    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.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,115
    So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)
    George
    Home | Blog

  15. #15
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •