Auditing - Any help welcome!

Question

Auditing - Any help welcome!

bckaur

SSC Enthusiast

Points: 144
More actions
August 23, 2002 at 5:16 am

#58583

Hi,
I am fairly new to SQL Server and will be helping administer a production server.
We have an environment where developers on occasion need to have emergency access to a production database in order to make a data fixes.
Not all fixes can be made via the application.
I need to audit when somebody goes into the live database using the emergency logins (which will be set-up in advance). Is there anyway this can be done?
There is a requirement that auditing is only triggered when the emergency logins (1 for each database) login to the server and is switched off when the emergency login has logged out. Is this possible?
Any tips on how to set-up auditing to monitor DDL and data changes would be appreciated.
Thank you
- B Kaur }:)

Viewing 14 posts - 1 through 13 (of 13 total)

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

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

If you audit logons on the sql server you can add an alert (eg add an alert on error no 18454/55) which runs only when a named account logs on.

This alert can also fire off a script which could turn on a server side trace to record all the information you need.

Steven

bckaur SSC Enthusiast Points: 144 More actions · Answer 2

Stephen,

Thanks for the tips. I hope you could help me clarify a few related queries.

Is there any way I ensure that the trace triggered does not run longer than it needs to? Is there possibly another event that records when a given user logs out of the server?

Otherwise I can always run a script nightly to switch this off if it is activated.

I have used the trace wizard to create a trace activity by user trace. How can I acticvate this trace?

Thanks in advance

- B Kaur

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

What version of sql server are you using?

I would suggest a server side trace, which in sql 2000 you create with the following stored procuedures

sp_trace_create

sp_trace_setevent

sp_trace_setstatus

books online (the online help manual) is a good place to lookup these stored procedures.

Steven

Antares686 SSC Guru Points: 125444 More actions · Answer 4

In 7 the items start xp_trace... and can be found in BOL. Also you can turn on security auditing in SQL in general. This can be found by right clicking the server in EM and choosing properties. Then I believe you will find under security tab.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

bckaur SSC Enthusiast Points: 144 More actions · Answer 5

Thanks for the help Guys.

Just one more question, I'm using SQL Server 7.0, is it possible to save a Trace profile created using the profiler and then being able to activate this profile using one the xp_trace* stored proc's in a job?

Thanks again for your help

Bali 🙂

Antares686 SSC Guru Points: 125444 More actions · Answer 6

To the best of my knowledge that is a no or at least right off I cannot find a way. However you might try creating a trace and using another profiler look for application 'SQL Server Profiler%' instead of excluding, you might can catch the definition queue handle which is all you need to start a trace queue, just make sure it was created at the server with an output file. Reason for at server is difference in folders on a client machien and server may cause to fail.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Edited by - antares686 on 08/27/2002 04:03:21 AM

bckaur SSC Enthusiast Points: 144 More actions · Answer 7

Thanks for this, I will try and set-up auditing using the extended profiler stored procs.(xp_trace_addnewqueue and such like)

Can anybody tell me what are the core differences with respect to the below. They all appear to do the same thing but there must be advantages to that each has over the other.

-SQL Profiler

-Stored Proc xp_trace

-Stored Proc xp_trace_addnewqueue

-DBCC TRACEON (Id)

Any clarification on the above would be much appreciated.

Thanks again

-Bali

Antares686 SSC Guru Points: 125444 More actions · Answer 8

SQL Profiler is similar to xp_trace

Stored Proc xp_trace this is what all the extended stored procedures start with.

Stored Proc xp_trace_addnewqueue - Adds a new trace queue and sets trace queue configuration values, including the size of the trace queue (the number of items to be held in the trace queue at one time). This is a Profiler style trace definition.

DBCC TRACEON (Id) these TRACE FLAGS are predefined.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

bckaur SSC Enthusiast Points: 144 More actions · Answer 9

Sorry I had meant xp_sqltrace not xp_trace. In 7.0 is says somnething about this being phased out and to use the xp_trace procs.

So where a trace flag is not available I assume you create either a profile definition in profiler or you set up a queue definition using the xp_trace* procs.

Thanks for your help, it has made this whole area a little clearer.

-B Kaur

Antares686 SSC Guru Points: 125444 More actions · Answer 10

quote:
So where a trace flag is not available I assume you create either a profile definition in profiler or you set up a queue definition using the xp_trace* procs.

Yes and no, depends on if you are getting enough data from the trace.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

bckaur SSC Enthusiast Points: 144 More actions · Answer 11

Hi,

Is there some reason as to why when I use the xp_trace_addnewqueue stored proc set to show all columns (as below) it will not display the text column.

EXEC xp_trace_addnewqueue 1000, 5, 95, 90, 0, @queue_handle OUTPUT

I have now named only those columns I need.

Also, when you delete a queue and create a new trace queue it appears to increment the queue no, rarther than re-use released no's. Would anybody know this is the case.

Any info would be welcome

Thanks

B Kaur

Antares686 SSC Guru Points: 125444 More actions · Answer 12

How are you stopping the queue. Are you destroying the queue? I believe you have to xp_trace_deletequeuedefinition to get rid of the queue and thus release the queue handle.

As for "it will not display the text column." I will have to take a look when I am at work, I don't recall any issues with this, unless maybe the event classes you specified have no text data to output. Text is for T-SQL text that is generated. But for instance a login event does not have text data for it at all.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

bckaur SSC Enthusiast Points: 144 More actions · Answer 13

Hi,

Each time the alert fires to say the emergency login is logged in I run the below statements:-

*******************************************

EXEC xp_trace_addnewqueue 1000, 5, 95, 90, @column_value, @queue_handle OUTPUT

EXEC xp_trace_seteventclassrequired @queue_handle, 12, 1 --12 SQL:BatchCompleted

......

EXEC xp_trace_setuserfilter @queue_handle,'Emerg%',NULL

EXEC xp_trace_setappfilter @queue_handle,NULL,'SQLAgent%'

..............

.

EXEC xp_trace_setqueuedestination @queue_handle, 2, 1, NULL,@EmgncyTraceFileName

--@EmgncyTraceFileName = The file name. One per day as separated by date.

EXEC xp_trace_savequeuedefinition @queue_handle, 'Audit Emergency Logins', 1

EXEC xp_trace_startconsumer @queue_handle

*******************************************

Does the above constitute a full queue definition when saved?,

If so, can I reload the saved definition using 'EXEC xp_trace_loadqueuedefinition' and somehow acquire the queue handle?

By the same token I need to stop all pending traces once there are no emergency logins logged into the server. The idea is that I check sysprocesses for the given user and then drop all queue handles. Does this seem viable?

EXEC xp_trace_deletequeuedefinition does not release the queue handles so that they can be reused, only the restart of the server seemed to have this affect?

Thanks in advance

Bkaur