Auditting IP addresses in logins

Question

Auditting IP addresses in logins

Alergy

Mr or Mrs. 500

Points: 508
More actions
October 11, 2007 at 6:37 am

#178200

Firstly, I'm sorry if this a simple question or if I've missed something already on this site, but I'd like to try to log the IP address of login attempts to our SQL Server (2000, SP4). If I look in the errorlog the only info I get is whether or not the connection was trusted or not. Can this info be resolved to an actual IP address at all?
Thanks
Ali

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

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Only if the client sends it in. the hostname is usually in sysprocesses (sp_who2) when the person connects, but I'm not aware of a way to audit this from SQL.

What are you trying to do? If this is a long term thing, set up a server trace. Otherwise I'd get network people involved to check things and log IPs.

Alergy Mr or Mrs. 500 Points: 508 More actions · Answer 2

Steve Jones - Editor (10/11/2007)
Only if the client sends it in. the hostname is usually in sysprocesses (sp_who2) when the person connects, but I'm not aware of a way to audit this from SQL.
What are you trying to do? If this is a long term thing, set up a server trace. Otherwise I'd get network people involved to check things and log IPs.

Thanks Steve - that's what I thought. We had some unusualness on the server (table on a DB was dropped) which made me think we might have some security issue. I'm in the process of further locking down the server and setting up a trace, but I was wondering if there was a way of retrospectively looking at what has connected from where.

Thanks for the super-prompt reply!

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 3

On SQL 2000 you only have one thing available: the MAC address of the client machine once it has connected. Look at SYSPROCESSES in the netaddress field. It contains the MAC address of the client machine (even on routed networks it shows the client workstation's MAC address). To convert MAC to IP you may need to ask a network admin to trace a router.

There are two issues with the native SQL Server approach: neither the MAC nor the IP can be traced from SQL Server Trace; and the MAC address is only available ONCE the login is successful (it cannot be used to track failed logins).

Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing

Alergy Mr or Mrs. 500 Points: 508 More actions · Answer 4

Thanks Herve for the extra pointers. This is all really useful and helpful to know - it's just good to get a better understanding of what is available for security audits.