Re: Trace Log - Failure to capture known SQL activity

Is it SQL Server 2000 or 2005?

select identity(int,1,1) as traceid, a.name as [Database],
ltrim(rtrim(convert(varchar,b.spid))) as spid,
ltrim(rtrim(b.loginame)) as loginame,ltrim(rtrim(b.program_name))
as program_name,ltrim(rtrim(b.hostname))
as hostname into #audittrace from master.dbo.sysprocesses b (nolock) ,
master.dbo.sysdatabases A where
a.dbid = b.dbid and ltrim(rtrim(loginame)) not in
('DBA1','domain\systemaccount','DBA2','domain\administrator') and
ltrim(rtrim(left(program_name,8))) in ('MS SQLEM','SQL Quer')



"Digital Slug" <DigitalSlugnospamalias@xxxxxxxxxxxxxxxxx> wrote in message
news:45767E07-9443-4867-8298-BE206EAEC034@xxxxxxxxxxxxxxxx
Hello,

I am capturing SQL Server trace log activity (daily basis) on a generic
RDBMS production server. It captures ordinary user traffic just fine.
However, it fails to capture certain types of known RDBMS activity.

Is it possible for an administrator to sidestep SQL Server activity logs
and
traces?

Can you suggest additional ?sp_trace_setevent? events that should be
captured?

The problem and my basic trace log settings are listed below.

Problem:
1. SQL traffic from authorized Server/RDBMS administrators is captured in
trace log.

2. Windows Server event logs captures an unauthorized administrator
(database/domain admin) logging on to server and performing RDBMS
operations.

3. SQL Server trace log does not indicate that DB activity has occurred by
unauthorized administrator.


Standard Trace Configuration:
Event 10, RPC:Completed - Occurs when a remote procedure call (RPC) has
completed.
Event 11, RPC:Starting - Occurs when an RPC has started.
Event 12, SQL:BatchCompleted - Occurs when a Transact-SQL batch has
completed.
Event 13, SQL:BatchStarting - Occurs when a Transact-SQL batch has
started.
Event 14, Audit Login - Occurs when a user successfully logs in to
Microsoft
SQL Server.
Event 15, Audit Logout - Occurs when a user logs out of SQL Server.
Event 16, Attention - Occurs when attention events, such as
client-interrupt
requests or broken client connections, happen.
Event 17, ExistingConnection - Detects all activity by users connected to
SQL Server before the trace started.


Need a little help here?..
Thanks!



.

Relevant Pages

  • RE: Problem with profiling datetime
    ... DateTime dt = cmd.ExecuteScalar; ... The resulting trace log item is as follow ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: Trace Log - Failure to capture known SQL activity
    ... Our production server uses SQL SErver 2000. ... It captures ordinary user traffic just fine. ... The problem and my basic trace log settings are listed below. ... Windows Server event logs captures an unauthorized administrator ...
    (microsoft.public.sqlserver.security)
  • Re: Connection to SQL Server works in VB.NET, not ASP.NET
    ... when trying to open an connection to the SQL server. ... Please try to check if the connection has been opened before. ... statement goes wrong from the trace log. ... "This posting is provided "AS IS" with no warranties, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Need starting advice...
    ... In the books, it is stated that ADO.NET captures not just the data, but the relations as well, however in looking at the XML schema, I find no indication of this -- I am guessing it captures relations based only on the SQL used to populate the dataset, not the relations from the underlying tables... ... You could also use CSV files, XML files, etc., but SQL Server is useful at a later step. ... The main goal here is only attempting to synch when you know you are on the right network. ... In many ways, I prefer SQL Express to MSDE, even if your main data store is SQL Server 2000, as it has a few nice features that can help you with other aspects of programming. ...
    (microsoft.public.dotnet.framework.adonet)
  • How to export an Image field from Sql Server to Access 2003
    ... We have a forms application that captures comments digitally entered ... by an inspector and saved as an image in Sql Server. ... The manager of ...
    (comp.databases.ms-access)