Tracking Illicit Users

Question

Tracking Illicit Users

David.Poole

SSC Guru

Points: 76000
More actions
January 14, 2008 at 9:44 pm

#91487

Comments posted to this topic are about the item Tracking Illicit Users
LinkedIn Profile

Viewing 15 posts - 1 through 15 (of 31 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

Heh... I take the low road... disable all logins (except mine), answer the phone to see who's squawkin' 😉 (just kidding... in most cases 😛 )

Great article, David!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Krissy Ten Centuries Points: 1185 More actions · Answer 2

I pretty much use an identical script to stop developers accessing the live server.

Unfortunately I can't disable their logins (as much as I would like to :P) as they are allowed (by the business for 'support' purposes) to use the standby/logshipping server to access data and since permissions are replicated across, they have to remain in place.

The main reason we don't want them accessing the live server is so they don't change data (in the case where they know the account password from a legacy system) and also so they don't put load on the server if they run a massive query.

The best part of my script - KILL @SPID 😀

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

Hello,

could you please tell my why you would not use profiler to audit logins?

Is this putting too much burden on the server?

Thanks!

Best Regards,

Chris Büttner

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I had much the same problem some months back. Group of developers felt they were above the rules and were fiddling on Prod using a SQL account for a messaging application that they were maintaining.

Couldn't disable the account, cause the messaging is a critical process (inter-bank money transfers). Could change the password, but would have had to tell developers so that they could configure the messaging app with new password (and the app keeps the password clear-text in it's properties) :crazy:

The security officer threatened them with diciplinary hearing and possible dismissal, but management did nothing more than slap them on the wrists. They were 'valuable employees'

When SQL 2005 SP2 came out, I dumped a login trigger on the prod box that rolls back any connections that cme from the app's login that use a querying tool.

The screams were soooo pleasant to hear. 😀

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Clever... effective... and vengeful... all in the same action... I LIKE IT! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andy Warren SSC Guru Points: 119902 More actions · Answer 6

Good stuff as always David!

My two cents; firewall rules to block developers from direct access to production is a very cool thing, and probably easier to sell in the SOX era than killing the legacy apps. Doesn't eliminate the problem, but makes it hard for them to abuse. The other point is that hostname can be spoofed on purpose or accidentally. I don't know if it still does it, but when you linked a table in Access it would store the host name as part of the connection, so if you copied the MDB to someone else it looked like they were running from the original machine.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Joseph Hicks-446980 SSC Enthusiast Points: 148 More actions · Answer 7

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 (sysprocesses.net_address). 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.

/// Edit to add source of MAC addresses

Sanjeev Jha-475652 Say Hey Kid Points: 704 More actions · Answer 8

lOVE the article. I am going to practice and learn from your expertise. Thanks for making us wiser at what we do.

-Sanjeev

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 9

The line:

INSERT INTO @InputBuffer(EventType,Parameters,EventInfo) EXEC (@SQL)

gave me an error:

Server: Msg 197, Level 15, State 1, Line 65

EXECUTE cannot be used as a source when inserting into a table variable.

What happens?

:crying:

In Theory, theory and practice are the same...In practice, they are not.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 10

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

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 SSC Enthusiast Points: 148 More actions · Answer 12

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) SSC Guru Points: 124210 More actions · Answer 13

Michael Valentine Jones (1/15/2008)

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 SSC Enthusiast Points: 148 More actions · Answer 14

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.