[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 895
  • Last Modified:

SQL server 2008 R2 Audit

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
marrowyung
Asked:
marrowyung
1 Solution
 
marrowyungAuthor Commented:
the SQL server 2008 R2 standard edition can't setup SQL audti ?

aFter enabling hte C2, need to restart ?
0
 
Haresh NikumbhCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
marrowyungAuthor Commented:
"so yes you need to reboot server once "

I think just restart the MSSQL service, right?
0
 
marrowyungAuthor Commented:
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
 
Haresh NikumbhCommented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
Haresh NikumbhCommented:
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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
for C2 and Common Criteria audit log, are they going to write to the same trc file ?
0
 
marrowyungAuthor Commented:
That one is the enterprise edition  ? I am using standard eidtion and I don't have this option.
0
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
The C2 Auit file will be write to the same file as the SQL server and SQL agent log ?
0
 
marrowyungAuthor Commented:
I seems that the Audit trace file and the SQL server and SQL agent log is not write to the same physical location
0
 
marrowyungAuthor Commented:
done.
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
Any third party tools that can help?

http://www.apexsql.com/sql_tools_audit_testimonials.aspx?
0
 
Anthony PerkinsCommented:
Perhaps I am stating the obvious, but you do realize the question is closed right?
0
 
marrowyungAuthor Commented:
closed can be reopen you mean  ?(this is a good answer)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now