Enabling SQL audit Logging for SELECT queries

Hi all,
We have a requirement in our project where we need to audit log any query
(SELECT queries inclusive) that are fired on a specific set of objects (Tabl
e
and views) in our database. We need to capture information like Who fired th
e
query, When and the actual query itself.
The approach we have thought of is:
Run SQL Profiler and log the output of the trace into a SQL table
Create an INSERT trigger on the SQL table.
Trigger should write data into a custom Audit table with limited information
.
However the divantages we see here are performance issues due to profiler
being run continuously, maintenance overhead to clear the SQL table where th
e
trace is written etc.
Can anyone suggest any other better alternative for this requirement?
Thanks
GSNot sure if it ius an option for you, but SQL 2005 contains DML
triggers which allow you to audit Select statements.
Markus|||How about using any of the 3:rd party tools put there? Check the log reader
tools, they tend to have
this support (possibly in special versions): http://www.karaszi.com/SQLServer/link
s.asp
These tools does use the transaction log to audit modifications and Profiler
to audit SELECT.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GS" <GS@.discussions.microsoft.com> wrote in message
news:6A359D6A-CCD1-4EC5-AEA2-C74C7F551971@.microsoft.com...
> Hi all,
> We have a requirement in our project where we need to audit log any query
> (SELECT queries inclusive) that are fired on a specific set of objects (Ta
ble
> and views) in our database. We need to capture information like Who fired
the
> query, When and the actual query itself.
> The approach we have thought of is:
> Run SQL Profiler and log the output of the trace into a SQL table
> Create an INSERT trigger on the SQL table.
> Trigger should write data into a custom Audit table with limited informati
on.
> However the divantages we see here are performance issues due to profil
er
> being run continuously, maintenance overhead to clear the SQL table where
the
> trace is written etc.
> Can anyone suggest any other better alternative for this requirement?
> Thanks
> GS
>

Enable/disable logging dynamically

Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?

The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.

I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.

The behaviour of the logging cannot be modified at runtime. Also, logging different events in different log providers for same executable is not possible as well. It's a great idea for a future version tough.

What you can do is decide who can log, what log providers should each executable use, and what events for each executable you want to log at design time. (e.g.: I can log "ExecuteSQLExecutingQuery" from the ExecuteSQL task in SQL server, and WMIDataReaderOperation" from the WMI Data Reader task in a file).

Thanks,

Ovidiu

|||

You may also consider investigating creating a custom logging solution using Event Handlers. You have a great deal of control over how they operate and you can even use variables and configurations to control them.

Jamie Thomson had a blog about this sometime back... here is the link: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

Enable/disable logging dynamically

Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?

The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.

I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.

The behaviour of the logging cannot be modified at runtime. Also, logging different events in different log providers for same executable is not possible as well. It's a great idea for a future version tough.

What you can do is decide who can log, what log providers should each executable use, and what events for each executable you want to log at design time. (e.g.: I can log "ExecuteSQLExecutingQuery" from the ExecuteSQL task in SQL server, and WMIDataReaderOperation" from the WMI Data Reader task in a file).

Thanks,

Ovidiu

|||

You may also consider investigating creating a custom logging solution using Event Handlers. You have a great deal of control over how they operate and you can even use variables and configurations to control them.

Jamie Thomson had a blog about this sometime back... here is the link: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

Enable query logging

Hi all,
Is there a way to make SQL Server 2000 log every SQL query sent to it?
-Oleg.Yes, but you will need to turn on server side tracing. Doing server side
tracing will write what every you tell it to a trace file. Look in BOL at
all the sp_trace* stored procedures.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:1094nlms1ge0n74@.corp.supernews.com...
> Hi all,
> Is there a way to make SQL Server 2000 log every SQL query sent to it?
> -Oleg.
>

Enable query logging

Hi all,
Is there a way to make SQL Server 2000 log every SQL query sent to it?
-Oleg.
Yes, but you will need to turn on server side tracing. Doing server side
tracing will write what every you tell it to a trace file. Look in BOL at
all the sp_trace* stored procedures.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Oleg Ogurok" <oleg@.ogurok.com.ihatespammers.ireallydo.co> wrote in message
news:1094nlms1ge0n74@.corp.supernews.com...
> Hi all,
> Is there a way to make SQL Server 2000 log every SQL query sent to it?
> -Oleg.
>

Enable logging for merge agent

Hi,
how can I enable output logging for merge agent if subscriptions type is
anonymous (merge replication between sql server 2000 and sql server ce 2.0)?
Marius,
logging is a bit different with CE. Have a look at the section entitled SQL Server CE Server Agent Logging on this MSDN page:http://msdn.microsoft.com/library/de...bleconnect.asp
HTH,
Paul Ibison

Enable and disable websync.log

Hi, is there a way how to enable / disable logging on web merge synchronization into the file websync.log? The file exists in the ISAPI folder, on some machines there are some messages, on others there is nothing. There are Timeout messages on the client during synchronization and we need to find where is the problem, if it is on the server side or if it is a network error or something else.
Thanks, Pavel

The web sync log should be enabled by default.

On the IE replisapi.dll?diag page, what do you see the status as?

ReplIsapi Settings:

Property

Value

SNAC version (sqlncli.dll)

2005.90.1399.0

Logging Enabled

TRUE

Log Severity

2

Current Log Size

673542

Maximum Log Size

10485760

Log FileName

websync.log

Log Dir

D:\myVirtualDirectory

|||

ReplIsapi Settings:

Property

Value

SNAC version (sqlncli.dll)

2005.90.1399.0

Logging Enabled

FALSE

|||

Can you create a new virtual directory and try with that?

Also how did you come into this situation. Did anything change after you created the virtual directory.

|||

Yes, I have tried to create virtual directory few times and nothing has changed. I can't realize any change on the server, which could switch off logging.

Another thing is, that we need to monitor production system in which we cannot change virtual directory. In the test enviroment we have not any problems.

|||What about uninstalling and reinstalling the IIS side repl components? Could you try that?|||That's problem, It's production in which a thousands of subscribers are synchronizing and we cannot stop the system. All is working fine, but sometimes, say 1 day in months, there is problem with timeouts on some subscribers and the problem can be repeated on the client side in that moment. So we would like to enable logging in that moment to say, that the timeout is or is not on the server.|||

I dont know how you got into this situation, but here is what you can try:

Check if you have this key in your registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Replication\WebSyncLoggingOff

Is this value 1?

If it is, reset it to 0. Then do iisreset.

That should solve your problem

Enable and disable websync.log

Hi, is there a way how to enable / disable logging on web merge synchronization into the file websync.log? The file exists in the ISAPI folder, on some machines there are some messages, on others there is nothing. There are Timeout messages on the client during synchronization and we need to find where is the problem, if it is on the server side or if it is a network error or something else.
Thanks, Pavel

The web sync log should be enabled by default.

On the IE replisapi.dll?diag page, what do you see the status as?

ReplIsapi Settings:

Property

Value

SNAC version (sqlncli.dll)

2005.90.1399.0

Logging Enabled

TRUE

Log Severity

2

Current Log Size

673542

Maximum Log Size

10485760

Log FileName

websync.log

Log Dir

D:\myVirtualDirectory

|||

ReplIsapi Settings:

Property

Value

SNAC version (sqlncli.dll)

2005.90.1399.0

Logging Enabled

FALSE

|||

Can you create a new virtual directory and try with that?

Also how did you come into this situation. Did anything change after you created the virtual directory.

|||

Yes, I have tried to create virtual directory few times and nothing has changed. I can't realize any change on the server, which could switch off logging.

Another thing is, that we need to monitor production system in which we cannot change virtual directory. In the test enviroment we have not any problems.

|||What about uninstalling and reinstalling the IIS side repl components? Could you try that?|||That's problem, It's production in which a thousands of subscribers are synchronizing and we cannot stop the system. All is working fine, but sometimes, say 1 day in months, there is problem with timeouts on some subscribers and the problem can be repeated on the client side in that moment. So we would like to enable logging in that moment to say, that the timeout is or is not on the server.|||

I dont know how you got into this situation, but here is what you can try:

Check if you have this key in your registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Replication\WebSyncLoggingOff

Is this value 1?

If it is, reset it to 0. Then do iisreset.

That should solve your problem