Solved

SQL server 2008 R2 Audit

Posted on 2013-06-19
25
850 Views
Last Modified: 2013-08-16
Dear all,

 I am right now tried to turn on the C2 audit feauture of SQL server 2008 R2 but there is one more feature since SQL 2008 that called SQL server Audit and it is hightly customerizable.

 what is the way to turn it on and off? any pre and post configuraiton needs to be done?

 is that following link for SQL server 2008 R2:?

http://msdn.microsoft.com/en-us/library/cc280426(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/cc280425(v=sql.105).aspx

The C2, which use SQL trace, will create too much workload? any example on real world on how much the SQL server degraded?

Any restart needed after tunning on both:
1) C2 audit
2) SQL audit
0
Comment
Question by:marrowyung
25 Comments
 
LVL 21

Accepted Solution

by:
Haresh Nikumbh earned 500 total points
ID: 39259063
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39259620
the SQL server 2008 R2 standard edition can't setup SQL audti ?

aFter enabling hte C2, need to restart ?
0
 
LVL 21

Expert Comment

by:Haresh Nikumbh
ID: 39259652
Both C2 and Common Criteria audit modes are enabled or disabled using sp_configure. To enable C2 auditing, configure the C2 Audit Mode option to a value of 1. For Common Criteria, configure the Common Criteria Compliance Enabled option to a value of 1. In both cases, a service restart is required for the auditing to actually start.


so yes you need to reboot server once

http://technet.microsoft.com/en-us/library/cc293615.aspx
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39259699
"so yes you need to reboot server once "

I think just restart the MSSQL service, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39259703
SQL 2008 seems do not suppot Common Criteria Compliance, I don't see the option.

what security domain the C2 and common criteria compliance cover, which one is the superset ?
0
 
LVL 21

Expert Comment

by:Haresh Nikumbh
ID: 39259723
link which i has posted earlier was saying some just need to restart SQL instance that will start auditing ..

 if you cant reboot server then only restarting services will do.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39259757
Yeah, I believe so.

I tried that before long long time ago but forget it as this time we have no staging to test but a production server which is not busy at all.

what security domain the C2 and common criteria compliance cover, which one is the superset ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39259856
for C2 audit mode, how to change the default DB file location for the SQL trace file ?

once it is changed, will the SQL server log and SQL agent log moved/save to the new location ?
0
 
LVL 21

Expert Comment

by:Haresh Nikumbh
ID: 39259883
Sorry i got confused you mean to say
Can we Change the C2 Audit Trace file location?

for above question answer is No.

http://connect.microsoft.com/SQLServer/feedback/details/669702/c2-audit-files-location
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39261396
"http://connect.microsoft.com/SQLServer/feedback/details/669702/c2-audit-files-location "

That one is for SQL 2012 but not for SQL server 2008 R2, right?

This link said: "but you can change the location of default data location to some other drive. For changing the default path
 "

this mean yes ?

by "right click on server->choose database setting change the database default locations to where you want to point the files"?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39261890
for C2 and Common Criteria audit log, are they going to write to the same trc file ?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39261977
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39262002
That one is the enterprise edition  ? I am using standard eidtion and I don't have this option.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39262008
"http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_8399-Auditing-in-SQL-server.html"

only for enterprise edition, I can't turn this feature on and I tried that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39273892
The C2 Auit file will be write to the same file as the SQL server and SQL agent log ?
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39276860
I seems that the Audit trace file and the SQL server and SQL agent log is not write to the same physical location
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39287744
done.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39328596
hi all,

I tried to zip the trc file but it seems keep saying I don't have the permission, the Zip is just right click the file and select send  to -> compressed folder.

I try to install Winrar and try to go inside the folder I select for the trc file output and it still get the similiar result.

What permission under Windows server 2008 R2 I need to enable ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39376445
one thing, if I want to do data level audit, like during these days, who/login (SQL or application wise) has change the data of a data cell and what is the value(s) they did change, what can I do ?

Create table trigger for a table to log down only insert/update/delete action and what data and colume of that table has been done, followed by time stamp and the login do this. Then insert this EVENT to an table create only to record this kind of event ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39384701
but one thing, Oralce has some kind of fFine-grained auditing (FGA), introduced in Oracle9i, allowed recording of these row-level changes, so we know what data has been write to a data cell.

for example,
update SCOTT.EMP set salary = 12000 where empno = 123456;
How do you track this activity in the database?

usually Audit trial lets you know that Joe updated the table EMP owner by SCOTT, but it does not show that he updated the salary column for the table for employee number 123456. It does not show the value of the salary column before the change, either¿ to capture such detailed changes.

Can C2 Audit trial do this on this to show what that the value 123456 is updated by Joe and the colume he/she udpate is the "salary" one ?

or only the SQL server Audit can do this ?

or we need trigger for all insert/update/delete/select on a table anyway and write to a table created for this kind of information?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39410372
Any third party tools that can help?

http://www.apexsql.com/sql_tools_audit_testimonials.aspx?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39413109
Perhaps I am stating the obvious, but you do realize the question is closed right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39413929
closed can be reopen you mean  ?(this is a good answer)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now