January 15, 2008 at 8:54 am
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.
January 15, 2008 at 9:35 am
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.
January 15, 2008 at 11:38 am
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 🙂
* Noel
January 15, 2008 at 1:28 pm
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"
January 15, 2008 at 2:34 pm
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
January 15, 2008 at 4:21 pm
Sorin Petcu (1/15/2008)
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:
On SQL2005 you can use table variables with EXECUTE.
On SQL2000 you cannot. You have to use temporary tables instead.
January 15, 2008 at 4:24 pm
LICH (1/15/2008)
Also "Analyser" should be "Analyzer"
Not if you're British it shouldn't.
January 15, 2008 at 4:27 pm
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.
January 15, 2008 at 5:02 pm
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 analuein, to undo : ana-, throughout; see ana- + luein, 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.
"analyse is better than analyze, but merely as being the one of two equally indefensible forms that have won. The correct but now impossible form would be analysize ( or analysise), with analysist for the the existing analyst"
Language is a living thing, so is code !
CodeOn 😛
January 15, 2008 at 7:12 pm
David.Poole (1/15/2008)
LICH (1/15/2008)
Also "Analyser" should be "Analyzer"
Not if you're British it shouldn't.
Well, at least not until we've had a couple of "P's & Q's" 😀 Got beer? :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2008 at 7:18 pm
Beer !;) - Always ! I don't care what language you use, its still beer. 😀 . Opps I think we digress ...
Oh Well 🙂
CodeOn:P
January 17, 2008 at 1:02 pm
Also to identify users who are using SQL 2005 Studio
Add this program name to the filters
'Microsoft SQL Server Management Studio - Query'
January 17, 2008 at 2:17 pm
Dallas Martin (1/17/2008)
Also to identify users who are using SQL 2005 StudioAdd this program name to the filters
'Microsoft SQL Server Management Studio - Query'
It's not necessary, because is covered by:
P.program_name LIKE 'Microsoft SQL%'
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[zs]er%' 😉
January 18, 2008 at 1:31 pm
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.
January 23, 2008 at 5:56 am
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.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply