Finding IP through T-SQL

Question

Finding IP through T-SQL

Jeff K

SSC-Addicted

Points: 417
More actions
February 19, 2008 at 4:10 pm

#183135

I work in a small shop and have been tasked with logging IP Address as part of an Audit table via trigger (in-house app and boxed app accessing the same database prevents our development team from handling the audit themselves. I proposed using machine name (HOSTNAME()) rather than IP since its easier to find, but was shot down because our in-house app will return the machine name of our web server, rather than that of the user. I cannot use LoginName as a tracking point because the in-house app access the database through a single login.
I've struggled with this as the "junior" guy in the shop, especially since our senior DBA is out on medical. Any ideas?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

connections are not really handled by SQL server, but usernames/passwords/spids, once a conenction has been made is auditable.

If you need to know who is connecting by IP, you should capture that information fromt eh operating system, and not from SQL.

the hostname that comes from the connection can be faked/spoofed as far as SQL is concerned., so it's not 100% reliable.

after you have a hostname, you can use an app or cmdshell to nslookup the hostname.

check out this article on the issue:

http://www.sqlservercentral.com/articles/Administering/auditingwithsqlprofiler/1461/

I think the answer is that the application needs to capture the connecting IP address, and forward that to sql server.

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!

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

The connections are being made by your web server. Hence, regardless of how you get the IP Address, you will end up with the IP Address of the web server, not the actual client PC (of the web server).

The hostname is not all that reliable because it is up to the client making the connection to SQL Server to put the name of the host in the hostname field on the connection string. If the client chooses, it can be any abitrary string. I have seen it hardcoded to the same value all the time (and had the value of the hostname of a developers workstation).

The only option you have is to get the web server to give SQL Server the IP Address - perhaps as a parameter to relevant stored procedues

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

The IP on the web server should be in one of the server variables. Keep in mind this is the connection IP, so it might not be the actual client if there are firewalls/proxys being used. As Happycat suggested, send that in as a parameter to a stored procedure.