How to get the IP address in T-SQL

Question

Post reply

How to get the IP address in T-SQL

Tim Talbert-364824

SSC Journeyman

Points: 86
More actions
August 21, 2007 at 10:10 am

#373291

We have a stored procedure that stores the IP address from the calling program for auditing purposes. I have't found a function that supplies the IP address yet. Anybody have a solution for me?

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

do you mean using DNS/nslookup to convert an ipaddress like 66.94.234.13 to http://www.yahoo.com?

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!

Shaik Asiff Old Hand Points: 361 More actions · Answer 2

Shaik Asiff

Old Hand

Points: 361

August 21, 2007 at 12:29 pm

#728404

To do this you need permission to run xp_cmdshell from master database

Please follow the link below

http://www.sqlmag.com/Article/ArticleID/48303/sql_server_48303.html

Cheers

Asiff

Tim Talbert-364824 SSC Journeyman Points: 86 More actions · Answer 3

We are just starting out in SQL Server and would like to avoid using xp-cmdshell if we can because we aren't real sure of ourselves with securing it. Current circumstances won’t allow us to use Windows AD security, so that adds another wrench into it. What we are doing is capturing an audit trail. We are leaning to using the machine name instead of the IP address both for simplifying the logic and being a bit more definite of where the access was from.

Lowell SSC Guru Points: 323482 More actions · Answer 4

yeah having to roll thru the tables later after the IP's been captured woudl be a pain in the audit.

Since you are going to use HOST_NAME instead, you obviously are already aware of some of the system functions to get data like that...for others whoa re reading this, try something like this:

select CURRENT_USER,host_name(),HOST_ID(),SYSTEM_USER

you'll get results like this:

dbo

DAISY

1788

sa

that info can be captured and used in a field in your audit tables if needed.

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!