may i find Client machine ip in asp.net using sql procedure, Is it possible?

Question

SELECT CLIENT_NET_ADDRESS AS 'ClientIPAddress'     
 FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

I am using the above value in a insert procedure but Client system ip not saved. Please help me

ALTER procedure [dbo].[proInsertLogDetails]
@action nvarchar(50),
@table_id nvarchar(500),
@created_by nvarchar(500)
as
begin
declare @ip varchar(100)
SELECT @ip=CLIENT_NET_ADDRESS   FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID
insert into user_log
(
[action],
created_by,
ip_address,
table_id
)
values  
(
@action,
@created_by,

@ip
,
@table_id
)

I am using above store procedure.

Post your stored procedure code. We can't help you solve a problem with code we can't see. — JamesT, Jan 23 '15 at 10:28

score 0 · Answer 1 · answered Jan 23 '15 at 10:51

0

Your question is tagged as ASP.net but you are using the connection to SQL Server to determine your client's IP address. I think you mean the Web Browser as the client here ? In which case, since the Browser never makes a connection to SQL Server the best you can hope for it the address of the Web Serve that connects to the SQL Server.

answered Jan 23 '15 at 10:51

PhillipH

6,182
1
15
25

there is no alternate to get client system IP using sql server. – Sumit Kumar Jan 23 '15 at 10:54
2

Sorry, can you be a bit more clear in your responses. In normal ASP.net applications it is the web server that makes a connection to SQL Server therefore SQL Server will report the address of hte web server as the "client IP address". You cannot use SQL Server to get the IP address of the clients browser - in ASP.net you would use the HttpRequest to get the client IP address, but this is not foolproof - see http://stackoverflow.com/questions/735350/how-to-get-a-users-client-ip-address-in-asp-net – PhillipH Jan 23 '15 at 10:57

score 0 · Answer 2 · answered Jan 23 '15 at 10:55

You can get the client machine ip address/hostname in asp.net using

String sName = System.Net.Dns.GetHostEntry
                (HttpContext.Current.Request.UserHostName).HostName

send this value as parameter to the stored procedure proInsertLogDetails

ALTER procedure [dbo].[proInsertLogDetails]
@action nvarchar(50),
@table_id nvarchar(500),
@created_by nvarchar(500),
@ClientName varchar(100)
as

score 0 · Answer 3 · answered Jan 23 '15 at 11:04

You can create a function

CREATE FUNCTION [dbo].[GetIp] ()
    RETURNS VARCHAR(255)
    AS
    BEGIN
        DECLARE @ip VARCHAR(255);

        SELECT @ip = client_net_address
        FROM sys.dm_exec_connections
        WHERE Session_id = @@SPID;

        RETURN @ip;
END

may i find Client machine ip in asp.net using sql procedure, Is it possible?

3 Answers3