Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking Illicit Users


Tracking Illicit Users

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36226 Visits: 18751
I'd be interested to hear about the MAC address. I thought everything sent in the connection was determined by the client sender, not the network or any automatic/authentic service. Since it operates at a higher network level, I'm not sure that SQL knows anything about the client the client doesn't want it to know.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
Joseph Hicks (1/15/2008)
Andy Warren (1/15/2008)
... The other point is that hostname can be spoofed on purpose or accidentally ...


We had a group of developers (from before my employment) use this to identify the class of a process. Unfortunately for me, this was then used in other processes to automate other processes, so now I'm stuck in a sea of "I can't tell which computers the connections are coming from", so I've turned to using MAC addresses (I know SQL will return them, but I'm not currently looking at my code - I'll update this post later). I'd think this would be a much more reliable method of determining which computers are connecting, but I know almost nothing of MAC addresess and haven't figured out how to tie them to an IP address without explicitly checking the MACs of each of our computers.


You should be aware that the MAC address can be changed dynamically, so a restriction based on MAC address is not foolproof.

The old DECnet protocol depended on the ability of a system to assign the MAC address to a specific address.
Joseph Hicks-446980
Joseph Hicks-446980
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 21
The sysprocesses.net_addres column stores the MAC address of the connection. It should be NULL for system processes (possibly for local connections, but I haven't tested that). I don't know of any way to spoof a MAC address, but at the same time, I don't really know how to search for a computer on my network if I only have a MAC address, so it does little good for me in either case.

I'll go back and update my original post to add this information in.
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7643 Visits: 18084
Michael Valentine Jones (1/15/2008)
[quote]Joseph Hicks (1/15/2008)


You should be aware that the MAC address can be changed dynamically, so a restriction based on MAC address is not foolproof.

The old DECnet protocol depended on the ability of a system to assign the MAC address to a specific address.


And every NIC that I have had in recent years has the ability - it's called the Locally Administered Address (from the old Token ring days...)

for what it's worth - the MAC or LAA also gets picked up in NewSequentialID() which inidentally might be the easiest way to pick up what MAC address "did the deed". The last 12 digits are the Hex representation of whatever MAC (real or spoofed) the machine is advertising at the time.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Joseph Hicks-446980
Joseph Hicks-446980
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 21
Michael Valentine Jones (1/15/2008)
Joseph Hicks (1/15/2008)
Andy Warren (1/15/2008)
... The other point is that hostname can be spoofed on purpose or accidentally ...


We had a group of developers (from before my employment) use this to identify the class of a process. Unfortunately for me, this was then used in other processes to automate other processes, so now I'm stuck in a sea of "I can't tell which computers the connections are coming from", so I've turned to using MAC addresses (I know SQL will return them, but I'm not currently looking at my code - I'll update this post later). I'd think this would be a much more reliable method of determining which computers are connecting, but I know almost nothing of MAC addresess and haven't figured out how to tie them to an IP address without explicitly checking the MACs of each of our computers.


You should be aware that the MAC address can be changed dynamically, so a restriction based on MAC address is not foolproof.

The old DECnet protocol depended on the ability of a system to assign the MAC address to a specific address.


At this point, I'm not as much interested in creating automated restriction rules (assuming that's what you're referring to), but more of identifying what PC a given SQL authenticated process is running from. We have an application user that is intended to only be used by applications (we can't use full windows authentication at this time), but I've seen that user pop up on other machines with spoofed machine names, so I've used MAC addresses (with limited success thus far) to track where the connections are coming from and find out who is doing it.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
Joseph Hicks (1/15/2008)
Michael Valentine Jones (1/15/2008)
Joseph Hicks (1/15/2008)
Andy Warren (1/15/2008)
... The other point is that hostname can be spoofed on purpose or accidentally ...


We had a group of developers (from before my employment) use this to identify the class of a process. Unfortunately for me, this was then used in other processes to automate other processes, so now I'm stuck in a sea of "I can't tell which computers the connections are coming from", so I've turned to using MAC addresses (I know SQL will return them, but I'm not currently looking at my code - I'll update this post later). I'd think this would be a much more reliable method of determining which computers are connecting, but I know almost nothing of MAC addresess and haven't figured out how to tie them to an IP address without explicitly checking the MACs of each of our computers.


You should be aware that the MAC address can be changed dynamically, so a restriction based on MAC address is not foolproof.

The old DECnet protocol depended on the ability of a system to assign the MAC address to a specific address.


At this point, I'm not as much interested in creating automated restriction rules (assuming that's what you're referring to), but more of identifying what PC a given SQL authenticated process is running from. We have an application user that is intended to only be used by applications (we can't use full windows authentication at this time), but I've seen that user pop up on other machines with spoofed machine names, so I've used MAC addresses (with limited success thus far) to track where the connections are coming from and find out who is doing it.



My point was not about restrictions, but that the MAC address of a client can be set to anything, so it is possible to spoof the address.

I realize that most people wouldn't change the MAC adress and would not know how, but someone who is trying to break into a system might know how, and would have incentive to do so.
GT-403069
GT-403069
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 14
I also have this issue. As a workaround, I created a temporary table rather than using a variable which I then drop once I'm finished with it. Would be good to know if I am missing something here though. Thanks.
noeld
noeld
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6322 Visits: 2048
Great Article! I am doing this kind of auditing on all my servers.

I would *not* filter by database Id though. cross-database queries can come from the "unexpected" master,tempdb,etc... system databases. The real approach is definitely logon triggers but 2005 is a requirement for that Smile


* Noel
LICH
LICH
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 46
Great script, Thanks David.
It works on SQL 2005, but get error when running on SQL 2000:

Server: Msg 197, Level 15, State 1, Line 65
EXECUTE cannot be used as a source when inserting into a table variable.
The line:
INSERT INTO @InputBuffer(EventType,Parameters,EventInfo) EXEC (@SQL)

Also "Analyser" should be "Analyzer"



Domingo Fredes
Domingo Fredes
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 96
I have the same problem in a server with SQL 2000. But the code works fine in SQL 2005. One solution may be to replace the table variable by a temporary table (#InputBuffer instead @InputBuffer).
I had 2 minor problems too, here are (and his solution):
1.
... ON P.sid = L.sid
My server is in case sensitive mode, the alias is L, not l

2.
... OR P.program_name LIKE 'SQL Query Analyzer%'
In the original code appears "Analiser"
Hope be useful
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search