How to Know ip address users who are accessing the application on sql server?

Question

How to Know ip address users who are accessing the application on sql server?

nagkarjun1

Ten Centuries

Points: 1020
More actions
August 6, 2012 at 11:51 am

#259710

How to know the ip address of the client application using the sql server?
Example: if an application database is located on a sql server, can we know the ip address of the application.
and also the ip address users who are accessing the server/application

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

nagkarjun1 (8/6/2012)
How to know the ip address of the client application using the sql server?
Example: if an application database is located on a sql server, can we know the ip address of the application.
and also the ip address users who are accessing the server/application

there is a connection property you can use to get the client_net_address:

--Requires SQL 2008 +

SELECT

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

the data can also be found in one of the dmv's:

SELECT

client_net_address AS ipaddress,

auth_scheme AS AuthenticationType

FROM sys.dm_exec_connections where session_id = @@spid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

also the ip address users who are accessing the server/application

Lowell didn't touch on this so I will. There is no way that SQL can know the IP address of a client machine. I assume that you have an application server and all the sql connections are made from that server. The database connection is made from the app server to the sql server. The only way sql would know what the IP of a client machine is if you pass that value from the application to the sql box either as a parameter of through the connection string using ApplicationName.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/