August 28, 2003 at 1:25 am
I have experienced several times that some users using a hostname e.g. "myhost" that never exists in the network but connected to the database. All I can find in the sysprocesses table is the net_address (MAC address). How can I find out the actual IP or hostname for these kind of clients? Thanks a lot
August 28, 2003 at 5:33 am
That's a very good question. Applications send in the host name, e.g. DTS packages appear to always come from the system that authoried them, drives me nuts.
I was fascinated to find the mac address was indeed the client address. The client connection must send that end, at least with some protocols, since I do not think it is carried in the IP packets after they go through a WAN (and mine did). THough it's been too many years since i looked. The MAC address gets turned into the last router it passes through.
I'm not aware of how to take a MAC address on a network that includes routers and turn them into IP addresses. Maybe someone else knows. It's fairly easy on a LAN, you can look in the ARP cache on the SQL Server, just
ARP -a
You can also do:
netstat -a
and see all the current TCP connections to that server, but you won't get a clue necessarily as to which goes with which PID.
Is the sid in sysprocesses useful? I'm not quite sure what that is, whether it's the NT user SID or the computer name SID? Probably the former, and so not useful.
Darn good question, I'm curious if someone has a good answer.
August 28, 2003 at 5:36 am
By the way, I should note that W2K (probably XP) includes a network sniffer that's decent (limited to what comes in/out of that server, not a general sniffer). So you can do some gross things like sniff all packets and then search out strings or check specific connections to determine which did what. But that's the kind of thing likely useful only in a repetitive attack or in testing a specific case.
August 29, 2003 at 6:07 am
For a description on how to obtain a list of MAC to IP addresses see the following Windows 2000 FAQ:
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14462
August 29, 2003 at 6:13 am
That's only on a LAN. On a LAN it's pretty straightforward, and you don't have to go through all that. If it's currently communicating with the SQL Server, log in there and look in the arp cache, since it's already had to resolve the IP/MAC combination.
But if the remote user is over a routed link (in a different subnet), it's not so easy. At least I don't know how.
There really must be somewhere that SQL Server can tell you information about the connection itself, since it has it open and knows the details, but I haven't found it yet.
August 29, 2003 at 6:53 am
Like you said before, IP traffic does not carry MAC addresses over WAN links. So you cannot use that.
August 29, 2003 at 7:25 am
quote:
Is the sid in sysprocesses useful? I'm not quite sure what that is, whether it's the NT user SID or the computer name SID? Probably the former, and so not useful.
It could be NT user SID or SQL Server login ID. Some applications use one generic SQL Server login to communicate with database. There is no way to know by SID who and which workstations are connect to database unless the application design in the database connection string with computer name.
See Andy's article at http://www.sqlservercentral.com/columnists/awarren/worstpracticebadconnectionstringsandbadinfoinsyspr.asp
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply