SQLServerCentral / Discuss Content Posted by David Poole / Article Discussions / Article Discussions by Author / Tracking Illicit Users / Latest Posts

RE: Tracking Illicit Users

Eric Wahner-345205 — Sat, 02 Feb 2008 09:36:50 GMT

There are still other ways that can both secure the environment as well as allow for existing applications to run as originally designed. The real problem here is that instead of suggesting that the user fix his original problem which is a big security hole, we are telling him how he can monitor that hole. The emphasis should be how to migrate out of that situation into a more secure environment.When we had a similar issue we forced all of the developers to connect via a different firewall which can easily be done by adding a route statement to their ports. Once they were going through that firewall the access restrictions were put into place. The developers had a test machine that they could use to test their applications but the security on that machine was limited so they could not install their own software with out the Administrator password. This way developers could only hit the sandbox environment and could only test production apps with the same security and interface that the users had. It was more elegant and no one had to worry about them running some dangerous query on our production environment.On the whole people are generally good natured and not out to do harm, but if you give them access then they can mistakenly use that access. DBA's tend to have this belief that when there is something wrong with the system it must be a rogue query from a dev that caused it. I used to be this way too and that's when I realized this is not the best way to build relationships with people.

RE: Tracking Illicit Users

David.Poole — Wed, 23 Jan 2008 13:11:34 GMT

In the ideal world I would agree with you entirely. Block them at the firewall and remove db_datareader/db_datawriter access.However the problem described in the article was of a business critical legacy application that used standard security so the normal methods of securing the database were off limits.The application used a mix of stored procedures and dynamic SQL so removing any privileges would break the application.

RE: Tracking Illicit Users

Eric Wahner-345205 — Wed, 23 Jan 2008 05:56:48 GMT

I don't think that you need to track this kind of behavior. There are many ways to prevent developers from having access to production. As was stated earlier you could use your firewall and put access restrictions on port 1433 or whatever port your SQL Servers are running on if you never want an developer to have access to it.If you need them to have access but not free reign, then I would suggest removing the datareader, datawriter roles and only allow your users on that server to have execute privileges on whatever procedures you have deployed. I guess I don't agree with the whole entrapment method of management.

RE: Tracking Illicit Users

David.Poole — Fri, 18 Jan 2008 13:31:15 GMT

I wasn't being xenophobic when I mentioned Analyser, it is what the app is called in the hostname field.I guess it is one of those "localization" things. Shades of grey/gray and all that.

RE: Tracking Illicit Users

Domingo Fredes — Thu, 17 Jan 2008 14:17:44 GMT

[quote][b]Dallas Martin (1/17/2008)[/b][hr]Also to identify users who are using SQL 2005 StudioAdd this program name to the filters'Microsoft SQL Server Management Studio - Query'[/quote]It's not necessary, because is covered by:P.program_name LIKE 'Microsoft SQL[b][u]%[/u][/b]'And for the interesting previous discussion about Analyzer or Analyser, I am spanish spoken, and I find the north american form better (spanish: Analizador), but the true thing is: what's the name that the query returns when this tool is being used?, and in my servers (english version) the answer is: SQL Server Query Analyzer.So I will use:OR P.program_name LIKE 'SQL Query Analy[b][u][zs][/u][/b]er%' ;)

RE: Tracking Illicit Users

Dallas Martin — Thu, 17 Jan 2008 13:02:49 GMT

Also to identify users who are using SQL 2005 StudioAdd this program name to the filters'Microsoft SQL Server Management Studio - Query'

RE: Tracking Illicit Users

Malcolm Daughtree — Tue, 15 Jan 2008 19:18:29 GMT

Beer !;) - Always ! I don't care what language you use, its still beer. :D . Opps I think we digress ... Oh Well :)CodeOn:P

RE: Tracking Illicit Users

Jeff Moden — Tue, 15 Jan 2008 19:12:23 GMT

[quote][b]David.Poole (1/15/2008)[/b][hr][quote][b]LICH (1/15/2008)[/b][hr]Also "Analyser" should be "Analyzer"[/quote]Not if you're British it shouldn't.[/quote]Well, at least not until we've had a couple of "P's & Q's" :D Got beer? :w00t:

RE: Tracking Illicit Users

Malcolm Daughtree — Tue, 15 Jan 2008 17:02:45 GMT

Analyse actually has no root ending -izo in greek as the greek 'usis' suffix precludes its use. The y is the english transposition of the greek 'u' [Medieval Latin, from Greek analusis, a dissolving, from analūein, to undo : ana-, throughout; see ana- + lūein, to loosen; see leu- in Indo-European roots.]The 'ize' is neither an adapation from the french or from the English.from 'Fowlers - Modern English Usage'Quote."[b][i]analyse [/i][/b]is better than [b]analyze[/b], but merely as being the one of two equally indefensible forms that have won. The correct but now impossible form would be [b]analysize [/b]( or analysise), with analysist for the the existing analyst"Language is a living thing, so is code !CodeOn :P

RE: Tracking Illicit Users

David.Poole — Tue, 15 Jan 2008 16:27:13 GMT

One aspect I forgot to include was when someone uses MS Office to connect to your databases. I'm thinking of MS Excel and the pivot report/charts. I haven't tried it yet but I seem to remember that the hostname does show up as coming from an office app.

RE: Tracking Illicit Users

David.Poole — Tue, 15 Jan 2008 16:24:15 GMT

[quote][b]LICH (1/15/2008)[/b][hr]Also "Analyser" should be "Analyzer"[/quote]Not if you're British it shouldn't.

RE: Tracking Illicit Users

David.Poole — Tue, 15 Jan 2008 16:21:47 GMT

[quote][b]Sorin Petcu (1/15/2008)[/b][hr]The line:INSERT INTO @InputBuffer(EventType,Parameters,EventInfo) EXEC (@SQL)gave me an error:Server: Msg 197, Level 15, State 1, Line 65EXECUTE cannot be used as a source when inserting into a table variable.What happens?:crying:[/quote]On SQL2005 you can use table variables with EXECUTE.On SQL2000 you cannot. You have to use temporary tables instead.

RE: Tracking Illicit Users

Domingo Fredes — Tue, 15 Jan 2008 14:34:07 GMT

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 = [b][u]L[/u][/b].sidMy server is in case sensitive mode, the alias is L, not l2.... OR P.program_name LIKE 'SQL Query Analy[b][u]z[/u][/b]er%'In the original code appears "Analiser"Hope be useful

RE: Tracking Illicit Users

LICH — Tue, 15 Jan 2008 13:28:27 GMT

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 65EXECUTE 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"

RE: Tracking Illicit Users

noeld — Tue, 15 Jan 2008 11:38:50 GMT

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 :)

RE: Tracking Illicit Users

GT-403069 — Tue, 15 Jan 2008 09:35:44 GMT

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.

RE: Tracking Illicit Users

Michael Valentine Jones — Tue, 15 Jan 2008 08:54:22 GMT

[quote][b]Joseph Hicks (1/15/2008)[/b][hr][quote][b]Michael Valentine Jones (1/15/2008)[/b][hr][quote][b]Joseph Hicks (1/15/2008)[/b][hr][quote][b]Andy Warren (1/15/2008)[/b][hr] ... The other point is that hostname can be spoofed on purpose or accidentally ... [/quote]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.[/quote]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.[/quote]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.[/quote]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.

RE: Tracking Illicit Users

Joseph Hicks-446980 — Tue, 15 Jan 2008 08:46:14 GMT

[quote][b]Michael Valentine Jones (1/15/2008)[/b][hr][quote][b]Joseph Hicks (1/15/2008)[/b][hr][quote][b]Andy Warren (1/15/2008)[/b][hr] ... The other point is that hostname can be spoofed on purpose or accidentally ... [/quote]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.[/quote]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.[/quote]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.

RE: Tracking Illicit Users

Matt Miller (#4) — Tue, 15 Jan 2008 08:43:47 GMT

[quote][b]Michael Valentine Jones (1/15/2008)[/b][hr][quote][b]Joseph Hicks (1/15/2008)[/b][hr]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.[/quote]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 [b]NewSequentialID()[/b] 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.

RE: Tracking Illicit Users

Joseph Hicks-446980 — Tue, 15 Jan 2008 08:40:43 GMT

The [b]sysprocesses.net_addres[/b] 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.

RE: Tracking Illicit Users

Michael Valentine Jones — Tue, 15 Jan 2008 08:38:02 GMT

[quote][b]Joseph Hicks (1/15/2008)[/b][hr][quote][b]Andy Warren (1/15/2008)[/b][hr] ... The other point is that hostname can be spoofed on purpose or accidentally ... [/quote]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.[/quote]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.

RE: Tracking Illicit Users

Steve Jones - SSC Editor — Tue, 15 Jan 2008 08:15:40 GMT

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.

RE: Tracking Illicit Users

Sorin Petcu — Tue, 15 Jan 2008 08:12:10 GMT

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

RE: Tracking Illicit Users

Sanjeev Jha-475652 — Tue, 15 Jan 2008 07:16:51 GMT

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

RE: Tracking Illicit Users

Joseph Hicks-446980 — Tue, 15 Jan 2008 07:08:35 GMT

[quote][b]Andy Warren (1/15/2008)[/b][hr] ... The other point is that hostname can be spoofed on purpose or accidentally ... [/quote]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

RE: Tracking Illicit Users

Andy Warren — Tue, 15 Jan 2008 05:31:23 GMT

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.

RE: Tracking Illicit Users

Jeff Moden — Tue, 15 Jan 2008 05:15:03 GMT

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

RE: Tracking Illicit Users

GilaMonster — Tue, 15 Jan 2008 01:12:51 GMT

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. :D

RE: Tracking Illicit Users

Christian Buettner-167247 — Tue, 15 Jan 2008 00:53:22 GMT

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!

RE: Tracking Illicit Users

Krissy — Mon, 14 Jan 2008 22:19:30 GMT

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 - [b]KILL @SPID[/b] :D

RE: Tracking Illicit Users

Jeff Moden — Mon, 14 Jan 2008 21:47:29 GMT

Heh... I take the low road... disable all logins (except mine), answer the phone to see who's squawkin' ;) (just kidding... in [i]most [/i]cases :P )Great article, David!

Tracking Illicit Users

David.Poole — Mon, 14 Jan 2008 21:44:47 GMT

Comments posted to this topic are about the item [B]Tracking Illicit Users[/B]