none
What is the difference between C2 Audit Tracing and Server Audit?

    Question

  • 1. What is the difference between C2 Audit Tracing and Server Audit?

    (do we need to check C2 audit mode when we create Server Audit?)

    2. Can we Change the C2 Audit Trace file location?


    Tuesday, June 12, 2012 12:17 PM

Answers

  • 1) No you do not enable C2 auditing to use Server Audit

    2) No - see this MS Connect item: http://connect.microsoft.com/SQLServer/feedback/details/669702/c2-audit-files-location

    Good article regarding what C2 auditing is:

    http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

    Good article on SQL Server Audit:

    http://www.bradmcgehee.com/2010/03/an-introduction-to-sql-server-2008-audit/


    Chuck Pedretti | Magenic – North Region | magenic.com



    Tuesday, June 12, 2012 12:30 PM
  • Hi,

    Differences between  C2 Audit Tracing and Server Audit:

    1. What is the difference between C2 Audit Tracing and Server Audit?

    C2 Audit

    • C2 audit mode is necessary if you are running a C2 certified system. A C2 certified system meets a government standard that defines the security level. To have a C2 certified Microsoft® SQL Server™, you must configure SQL Server in the evaluated C2 configuration. For more information about C2 certification, see the C2 Administrator's and User's Security Guide
    • C2 Audit Mode that can be configured through SQL Server Management Studio or with the stored procedure sp_configure. Selecting this option configures the server to record both successful and unsuccessful attempts to access statements and objects.
    • C2 Audit Mode always enables all event classes in the Security Audit category. For most production sites, auditing all event classes results in an unacceptable performance degradation of the SQL Server. If you use C2 Audit mode, do not enable all event classes.
    • For MS SQL 2005, C2 Audit Mode data is saved in a file in the \MSSQL\Data directory of default instances, and the \MSSQL$instancename\Data directory of named instances. When the audit log file reaches its size limit of 200 MB, the SQL Server creates a new file, closes the old file, and writes all new audit records to the new file. This auditing process continues until the audit data directory fills up or auditing is turned off.

      Important:
      C2 Audit Mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, the SQL Server shuts itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.
      Enabling C2 Audit Mode
      You must be a member of the sysadmin fixed server role to perform this procedure.

      Run the stored procedure sp_configure with Advanced Options enabled followed by the RECONFIGURE command:
      sp_configure 'show advanced options', 1;
      RECONFIGURE
      To enable auditing, run sp_configure with the c2 audit mode parameter set to 1:
      EXEC sp_configure 'c2 audit mode', 1
      RECONFIGURE
      After changing the C2 audit mode setting, restart the server.
      Note:
      If you have enabled C2 auditing, you might want to disable login auditing on the Security tab of the SQL Server Properties window in SQL Server Enterprise Manager (described previously). If you do not disable this feature, the auditing functions record the same type of event twice, unnecessarily degrading server performance.
    • Server Audit :

           -- The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server            instance level. You can have multiple audits per SQL Server instance.

          --  When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and            does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

             

           --  You can use SQL Server Management Studio or Transact-SQL to define an audit. After the audit is created and enabled, the target will receive entries.

           --   You can read the Windows event logs by using the Event Viewer utility in Windows. For file targets, you can use either the Log File Viewer in SQL Server              Management Studio or the fn_get_audit_file function to read the target file.

    The general process for creating and using an audit is as follows.
             Create an audit and define the target.
            Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification.
             Enable the audit.
           Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

    2. Can we Change the C2 Audit Trace file location?

    C2 Log files always point to the default data files location of the instance. You cannot change the path of C2 audit files but you can change the location of default data location to some other drive. For changing the default path 

    right click on server->choose database setting change the database default locations to where you want to point the files

    After SQL restart trace file will be created to the new default location provided in Database setting This will not change the existing database location. However you will need to make sure next time you create database point it to the right location.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, June 12, 2012 3:27 PM

All replies

  • 1) No you do not enable C2 auditing to use Server Audit

    2) No - see this MS Connect item: http://connect.microsoft.com/SQLServer/feedback/details/669702/c2-audit-files-location

    Good article regarding what C2 auditing is:

    http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

    Good article on SQL Server Audit:

    http://www.bradmcgehee.com/2010/03/an-introduction-to-sql-server-2008-audit/


    Chuck Pedretti | Magenic – North Region | magenic.com



    Tuesday, June 12, 2012 12:30 PM
  • Hi,

    Differences between  C2 Audit Tracing and Server Audit:

    1. What is the difference between C2 Audit Tracing and Server Audit?

    C2 Audit

    • C2 audit mode is necessary if you are running a C2 certified system. A C2 certified system meets a government standard that defines the security level. To have a C2 certified Microsoft® SQL Server™, you must configure SQL Server in the evaluated C2 configuration. For more information about C2 certification, see the C2 Administrator's and User's Security Guide
    • C2 Audit Mode that can be configured through SQL Server Management Studio or with the stored procedure sp_configure. Selecting this option configures the server to record both successful and unsuccessful attempts to access statements and objects.
    • C2 Audit Mode always enables all event classes in the Security Audit category. For most production sites, auditing all event classes results in an unacceptable performance degradation of the SQL Server. If you use C2 Audit mode, do not enable all event classes.
    • For MS SQL 2005, C2 Audit Mode data is saved in a file in the \MSSQL\Data directory of default instances, and the \MSSQL$instancename\Data directory of named instances. When the audit log file reaches its size limit of 200 MB, the SQL Server creates a new file, closes the old file, and writes all new audit records to the new file. This auditing process continues until the audit data directory fills up or auditing is turned off.

      Important:
      C2 Audit Mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, the SQL Server shuts itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.
      Enabling C2 Audit Mode
      You must be a member of the sysadmin fixed server role to perform this procedure.

      Run the stored procedure sp_configure with Advanced Options enabled followed by the RECONFIGURE command:
      sp_configure 'show advanced options', 1;
      RECONFIGURE
      To enable auditing, run sp_configure with the c2 audit mode parameter set to 1:
      EXEC sp_configure 'c2 audit mode', 1
      RECONFIGURE
      After changing the C2 audit mode setting, restart the server.
      Note:
      If you have enabled C2 auditing, you might want to disable login auditing on the Security tab of the SQL Server Properties window in SQL Server Enterprise Manager (described previously). If you do not disable this feature, the auditing functions record the same type of event twice, unnecessarily degrading server performance.
    • Server Audit :

           -- The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server            instance level. You can have multiple audits per SQL Server instance.

          --  When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and            does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

             

           --  You can use SQL Server Management Studio or Transact-SQL to define an audit. After the audit is created and enabled, the target will receive entries.

           --   You can read the Windows event logs by using the Event Viewer utility in Windows. For file targets, you can use either the Log File Viewer in SQL Server              Management Studio or the fn_get_audit_file function to read the target file.

    The general process for creating and using an audit is as follows.
             Create an audit and define the target.
            Create either a server audit specification or database audit specification that maps to the audit. Enable the audit specification.
             Enable the audit.
           Read the audit events by using the Windows Event Viewer, Log File Viewer, or the fn_get_audit_file function.

    2. Can we Change the C2 Audit Trace file location?

    C2 Log files always point to the default data files location of the instance. You cannot change the path of C2 audit files but you can change the location of default data location to some other drive. For changing the default path 

    right click on server->choose database setting change the database default locations to where you want to point the files

    After SQL restart trace file will be created to the new default location provided in Database setting This will not change the existing database location. However you will need to make sure next time you create database point it to the right location.


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, June 12, 2012 3:27 PM