August 9, 2008 at 5:56 pm
Ok, we have our SQL servers, they have their usual group of DBAs. There is also a domain account that ANY system administrator in the organization can use.
This has access to the SQL server for random reasons, but the idea being that if the DBAs are all on a plane to Europe and it goes down, the other system admins can login with that account to do... stuff I guess.
ANYWAY, I need to keep an eye on that account and make DARN SURE it isn't being used "willy nilly" to poke around.
I was thinking about just looking at the login events and when that account is used kicking an email to the DBA going "Heads up, someone is logging in that isn't you..."
However, that won't really do much other than just say the login happened.
Being as how it is an account with full privs they could do whatever they wanted. I would like some way to capture the TSQL being sent to the server, write it to a table, log it, maybe send it in emails (since a log could be wiped but once the email is sent they can't get it back or destroy it).
Anyway, suggestions for that?
The account should NEVER be used except in the most extreme situations, so if someone does login and run 50 megs worth of text TSQL commands, I'd rather get 50,000 emails with the contents of those commands and be "sure" rather than not know at all.
August 10, 2008 at 5:02 pm
Set up a server side trace that captures activities by DBAs.
August 11, 2008 at 7:18 am
Just so I'm clear, I should use SQL Profiler to run on the server itself and then filter by the login name and have it only capture data / trace data for that set of login names?
I assume then the way to go would be to write it to a table on the server, and then setup an insert trigger (or something) to watch that table for activity then start archiving it off to emails, a network file location, etc.... someplace OFF the server so if the server is being "abused" it isn't as easy to just wipe the trace logs in that table?
Could you please advise on how to get SQL Profiler to run on the server constantly?
Is there a way to set it as a service and execute traces with specific filters, settings, etc... when the server boots?
What I'm saying is: How do I get it to keep the trace running after the server is rebooted, or after I log off the server? Is there a way to have it run as a service so I don't need to manually log in and start the trace or leave my account running to keep the trace running?
Thank you again for your help.
EDIT:
Wasn't paying attention yet this morning it seems:
http://www.mssqltips.com/tip.asp?tip=1035
Google to the rescue as always, I assume that is the answer to all my problems? 🙂
August 12, 2008 at 10:32 pm
You can also set up a DDL trigger which fires and checks to see if it's that login. If it is, it executes a stored procedure which starts a server side trace.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 5:42 am
That is an interesting point, now how would I know when to end the trace? Would it time out after their connection ends?
August 13, 2008 at 12:50 pm
I believe you can have a DDL trigger fire on a logout event. You'd have to check to see if there were any others in with elevated rights (which you would have to do on the first trigger anyhow) and then check for the trace and stop it.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 11:46 am
Would it make more sense for me to use the DDL to look not at the login itself but the rights all logins have and if a login has a specific role on the server (sysadmin) then it would start the trace?
(Would you happen to have an example by chance, I haven't used DDL yet as I've never had a need until now)
Thank you
August 14, 2008 at 1:39 pm
Actually, this is a good way to do it. Here is a code sample from the presentation I gave at SQL Saturday Jacksonville. And it's actually a Logon Trigger (showed up in SP2) not a DDL trigger. They are similar and in my sleep-deprived state I'm making silly mistakes:
CREATE TRIGGER trigLogon_CheckForSysAdmin
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT srm.role_principal_id
FROM sys.server_role_members srm
JOIN sys.server_principals sp
ON srm.member_principal_id = sp.principal_id
WHERE role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE NAME = 'sysadmin')
AND ORIGINAL_LOGIN() = sp.NAME)
BEGIN
INSERT INTO [SQLSaturday_Triggers].dbo.ChangeLog
(DBName, EventTime, ServerLogin, DBUser, [Event], ObjectName, TSQL)
VALUES
('master', GETDATE(), ORIGINAL_LOGIN(), 'dbo', 'sysadmin role login!', 'Server', 'N/A')
END;
END;
K. Brian Kelley
@kbriankelley
August 14, 2008 at 1:50 pm
K. Brian Kelley (8/14/2008)
Actually, this is a good way to do it. Here is a code sample from the presentation I gave at SQL Saturday Jacksonville. And it's actually a Logon Trigger (showed up in SP2) not a DDL trigger. They are similar and in my sleep-deprived state I'm making silly mistakes:
CREATE TRIGGER trigLogon_CheckForSysAdminON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT srm.role_principal_id
FROM sys.server_role_members srm
JOIN sys.server_principals sp
ON srm.member_principal_id = sp.principal_id
WHERE role_principal_id = (SELECT principal_id FROM sys.server_principals WHERE NAME = 'sysadmin')
AND ORIGINAL_LOGIN() = sp.NAME)
BEGIN
INSERT INTO [SQLSaturday_Triggers].dbo.ChangeLog
(DBName, EventTime, ServerLogin, DBUser, [Event], ObjectName, TSQL)
VALUES
('master', GETDATE(), ORIGINAL_LOGIN(), 'dbo', 'sysadmin role login!', 'Server', 'N/A')
END;
END;
So then this creates a logon trigger that will wait until someone with sys admin privs touches the server.
Then it writes a record to the SQLSaturday datagbase's changelog and at that point you have an insert trigger waiting for records to be written so AFTER The record is inserted it can grab the server login and then kick off a server side trace to start tracing their activity.?
Is that the general concept there?
So if more than one person with sysadmin rights connects at once then multiple traces are started, but each one specific to that person.
Then another logout event to wait for them to logout to end their trace.
So AdminA logs in
record written to that table
insert trigger sees that write and starts a trace for AdminA
AdminB logs in
record written to that table
insert trigger sees that write and starts a trace for AdminB
Admin B logs out
logout trigger catches it and ends the trace
Admin A's computer crashes... after whatever period of idle time the connection is closed by the server...
Does that still count as a logout event or would that trace keep running?
August 14, 2008 at 2:02 pm
I would instantiate only one trace. The logon trigger can see if a trace is running (or you could write to a semaphore table or something) and if so, it doesn't start a new one. I believe logon triggers can fire on logouts, too. Same idea. Check to see if any other sysadmin level accounts are connected. If not, shut the trace down.
Something you should definitely perfect in a dev environment. And as a word of caution... if you end up locking yourself out due a logon trigger failing, come in through the DAC. Logon triggers don't fire if the DAC is used.
K. Brian Kelley
@kbriankelley
August 14, 2008 at 5:28 pm
K. Brian Kelley (8/14/2008)
I would instantiate only one trace. The logon trigger can see if a trace is running (or you could write to a semaphore table or something) and if so, it doesn't start a new one. I believe logon triggers can fire on logouts, too. Same idea. Check to see if any other sysadmin level accounts are connected. If not, shut the trace down.Something you should definitely perfect in a dev environment. And as a word of caution... if you end up locking yourself out due a logon trigger failing, come in through the DAC. Logon triggers don't fire if the DAC is used.
All good points, thank you.
Question about the trace itself: I was planning to filter the trace to ONLY get events from a given logon name.
Can I modify that on the fly while the trace is running or how can I tell a trace that is set for AdminA to update for AdminB?
I could preconfigure the trace for BOTH AdminA and AdminB, except what if a new admin user is added, in that case if I preconfigure the trace then I'd have to go back and update it instead of the trace "knowing" which logon to watch due to a trigger looking for someone to audit based off them being sysadmin. (Not sure if I'm making sense there)
August 14, 2008 at 5:43 pm
You can't modify it on the fly, no. The question, though, is are you going to severely impair the performance of your SQL Server by doing multiple traces like this?
K. Brian Kelley
@kbriankelley
August 15, 2008 at 6:28 am
K. Brian Kelley (8/14/2008)
You can't modify it on the fly, no. The question, though, is are you going to severely impair the performance of your SQL Server by doing multiple traces like this?
Not from what I am seeing so far. The MOST traces we would EVER have would be 6, and I can't imagine that ever really happening.
2 max at any given time really would be the normal "max" amount.
Currently the server is not utilizing more than 2% of its system resources, I honestly don't ever see the load reaching 50% except for after hours when more intensive scheduled jobs run to handle some maintenence and number crunching for reports to be displayed the following morning.
HOWEVER, you have a very good point and it is most certainly something I'd need to keep an eye out for regardless.
The catch though is I don't see how else to get a trace to run that can update itself to capture new admins if someone else is added to the sysadmin role.
I'd also like to keep the traces seperate by user, but I suppose that isn't REALLY needed since the trace logs will say which user is doing what anyway...
The logon trigger you provided makes sense and can detect any sysadmin role user connecting, but I want to filter the trace so I don't capture ALL activity on the server, JUST activity from sysadmin users and that's where I'm getting "lost".
August 15, 2008 at 7:09 am
Are you building by hand our are you allowing Profiler to build the template for you?
K. Brian Kelley
@kbriankelley
August 15, 2008 at 8:26 am
I have the trace with the column filters on LoginName setup in profiler and had it export it out to script trace definition for MSSQL 05.
Though if the script that starts the trace also specifies the filter parameters then I imagine I'd adjust the settings for that there.
I was also planning on excluding the domain account that SQL Server itself runs under as well as a few of its services since there's no need to trace though. I saw in SQL 2000 you could exclude system accounts it seemed, but I didn't see that option here. (I think it excluded SPIDs for system accounts?)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply