April 15, 2008 at 6:49 am
Dear all,
Our software shop exclusively uses SQL Server 2000 for database backends. We configure the server (ours as well as clients) for Mixed Mode authentication and use a single login acount across all client connections (mostly because we are not very familiar with administering SQL Server and Windows Active Directory).
I would like to be able to implement activity logging for each INSERT / UPDATE / DELETE statement at the minimum (more if possible) by capturing the machine ID and date/time of each act.
How can I do that?
Also, as an afterthought, if there is a way to use Windows Authentication without implementing Active Directory, I shall be grateful to learn about it.
April 15, 2008 at 1:36 pm
Assuming that all user updates, deletes, inserts are implemented via
stored procedures Here is an idea for you to consider:
1. Create a table to record the data collected, such as:
CREATE TABLE [dbo].[Use_History](
[Performedat] [datetime] NOT NULL CONSTRAINT [DF_Use_History_Performedat] DEFAULT (getdate()),
[Application] [varchar](100) NOT NULL CONSTRAINT [DF_Use_History_Application] DEFAULT (app_name()),
[Machine] [varchar](50) NOT NULL CONSTRAINT [DF_Use_History_Machine] DEFAULT (host_name()),
[UserName] [varchar](50) NOT NULL CONSTRAINT [DF_Use_History_UserName] DEFAULT (suser_sname()),
[ProcName] [varchar](100) NOT NULL
[Action] [varchar(50) NULL
) ON [PRIMARY]
Then using a SP such as:
CREATE PROCEDURE dbo.UseHistory
/*
To use, simply add the following line to stored procedures whose useage history you want to record
Execute dbo.Use_History @@Procid,'xxx' where xxx is Update, Insert or Delete
*/
@Procid INT
@Action VARCHAR(50)
AS
IF EXISTS (select xtype from dbo.sysobjects WHERE Id = OBJECT_ID('Use_History'))
BEGIN
INSERT INTO Use_History (AppName,ProcName,Action)
VALUES (RTRIM(App_name()), RTRIM(object_name(@procid),@Action)
END
But be careful this history table could become very large very quickly.
April 16, 2008 at 12:02 am
You can also use profiler for the same which is more accurate yet consume lots of space. If space is not an issue then go ahead with it.
"More Green More Oxygen !! Plant a tree today"
April 16, 2008 at 3:47 am
Phew, this is more complicated than I'd bargained for. No, the INSERT, UPDATE, DELETE statements are not executed via sprocs but via ADO in VB6 instead.
My thinking was to have six extra columns on each table (two each for INSERT, UPDATE, and DELETE for the userid and datetime) which could be updated simultaneously the statement is executed.
The headache is how to get the userid (more like the machineid in my case as all users use one generic SQL login via ODBC).
I have columns for WorkStationName, TimeSignIn and TimeSignOut on USERS table. I save the computer name against a user's record (and the start and stop times when he/she logs in and out. I update the respective pair of (InsertedBy & InsertedOn) / (ModifiedBy & ModifiedOn) / (DeletedBy & DeletedOn) field by passing the two values to the SQL statement in VB6/ADO.
P.S. DELETE does not actually delete a record, it just flags it.
However, rewriting all existing code is a very daunting exercise, so I was looking for implementing a common DB-wide trigger which would do the same. The problems are:
1. How to write TSQL code to obtain the user name from the USERS table (using the machine name as a filter).
2. How to write the trigger itself.
April 16, 2008 at 6:40 am
Hello, you can try to activate the C2 Audit level, i found thes informations on this article :
Basics of C2 Auditing
By Dinesh Asanka, 2004/11/03
Audit your Servers
What ever the system that we are taking about, whether it is an ERP system or CRM tool or any database system auditing has become essential part of that system.. Auditing will allow you to:
Detect the misuse and prevent it being happened again.
Take legal actions against the people who are responsible for the misuse.
Take recovery action from the point where something has gone wrong.
There are many ways in which you can setup an auditing trail and many places in which to get it up. However, we SQL Server guys (sorry, gals as well) have a somewhat easy way of doing this thanks to a new feature of Microsoft SQL Server 2000, which is called the C2 audit mode option.
What is C2
The US Department of Defense has established a set of ratings applicable to security levels of computer systems, based on their capabilities in regard to auditing and discretionary access control. Accordance with this ratings Microsoft SQL Server 2000 has C2 audit mode option. Every version of SQL Server 2000 is C2-certified (provided it is run on a C2-certified computer and network). Accordingly, SQL Server guarantees that its auditing procedures satisfy C2 requirements -- for example, storing generated data only on a NTFS partition.
C2 auditing records information goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements.
The audit information contains the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user's application and Server process ID of the user's connection and name of the database .
How it is done
I can't believe why there is no UI is given to enable this option. However, Microsoft might have fair reason for doing this. There are some serious implications if you enable this option.
Anyway it can be done via my favorite tool, SQL Query Analyzer. Before you enable (or disable) this option you must be a member of sysadmin group. Before attempting to set the 'c2 audit mode' configuration option, you must enable the 'show advanced options' configuration option. This is performed using the following command:
USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
To enable the feature, set 'c2 audit mode' to 1 using the following command:
sp_configure 'c2 audit mode', 1
go
To disable the feature, set 'c2 audit mode' to 0 using the following command:
sp_configure 'c2 audit mode', 0
go
After setting the value, you must stop and restart the server for C2 audit mode to be take effect. Now whenever you perform an Insert, Select or anything other statement, it will log the event in the trace files. These files reside in the \mssql\data directory for default instances of SQL Server 2000, or the \mssql$instancename\data directory for named instances of SQL Server 2000 as AuditTrace_yyyymmddhhmmss.trc, where the second part of the name indicates date and time when the log file was created. For example, some file names on my system are audittrace_20041007153315.trc and audittrace_20041007163855.trc. The size of a log is limited to 200MB, but new traces files are generated whenever the old one is full. Also a new one is generated on the shutdown of SQL Server with another one on startup.
One thing to be aware of is that SQL Server will stop if it cannot write log entries, i.e. you log so much that you run out of disk space. In emergency situations, where no space can be immediately freed for new log files, you can restart SQL Server with the -f flag, which will disregard auditing settings.
How to View the Audit Records
There are two ways of displaying the auditing records. Easy method would be clicking on the trace file. It will open this in SQL Profiler.
The second method would be using T-SQL.
SELECT *
FROM ::fn_trace_gettable(
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\audittrace_20041007153315.trc', default
)
GO
Limitations
The main limitation of the auditing is that it reduces the performance of the SQL Server. This happens due to saving the every action to the file. Second limitation is the hard disk space. These auditing files grow rapidly, which will reduce the disk space. According to the C2 , if it is not able to write to the trace file, SQL Server will be shutdown.
Conclusion
If you need your server to maintain a detailed audit trail, then the C2 audit option may be a good choice. However, you might have to consider few things before configuring it to be sure that you don't cause yourself some issues over time. Be sure that you read the documentation carefully before enabling this option.
References
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/prodtech/dbsql/sqlc2.asp
http://www.microsoft.com/technet/prodtechnol/winntas/maintain/security/c2secgde.mspx
By Dinesh Asanka, 2004/11/03
Total article views: 14095 | Views in the last 30 days: 238
best regards
April 16, 2008 at 6:29 pm
April 16, 2008 at 6:46 pm
Second thoughts ... myself being a VB programmer ... I can understand how your apps were written ... to convert them can be a daunting prospect... in general what T-SQL is hard coded in an app can about 99.9% of the time be converted to parameterized stored procedures. Examine the code ... create the stored procedures, including the parameters and look at ADO code for creating a parameter here is an example:
Ado_Cmd.CommandType = adCmdStoredProc
Ado_Cmd.CommandText = "dbo.Admin_Get_Value_By_itemName"
Set Ado_Param01 = Ado_Cmd.CreateParameter("@ItemName", adChar, adParamInput, 25) ', "Select")
Ado_Cmd.Parameters.Append Ado_Param01
Ado_Param01 = "IED Process Index"
Ado_Cmd.ActiveConnection = Cnn
Set Rs = New ADODB.Recordset
Rs.Open Ado_Cmd, , adOpenStatic
or if you do not want to open a recordset use the ado_cmd object as ado_cmd.execute and if you have defined a returned paramter in the SP you can examine that after command execution to test for success or failure ... the choice is yours.
Using SP you control what they do and how they do it .
Now for the daunting part ... begin to substitute the code that
is currently creating the T-SQL statement with parameterized SPs
Suprisingly with the co-operation of the developers a lot can be done in a very short period of time. By the way if you are using dot net it is even easier and quicker.
April 17, 2008 at 12:29 am
April 17, 2008 at 10:33 am
Goodguy your answer "Thanks, I think I figured out the solution I need."
Heck we all can learn from these posts how about telling us what / why / how you decided to go?
April 17, 2008 at 11:29 pm
I decided against triggers and instead will extend all my INSERT and UPDATE statements to include the app.user.name and date.time values for the InsertedBy and InsertedOn columns which I can do easily with VB6. It is actually a backward solution, for I was looking for an automatic trigger solution but could not figure out how to get SQL Server to determine the app.user.name (NOT the SQLServerLogin as that is common to all users).
Thanks for the prod.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply