Page tree

Auditing your SQL Server instances and databases is the first step in ensuring your SQL Server environment remains in continuous compliance with federal and corporate security and privacy policies. You can also generate reports on the audit data you collect, allowing you to demonstrate compliance on demand. For more information, see Report on Audit Data.

Auditing checklist

Use the following checklist to help you prepare your environment to successfully audit your SQL Server instances and databases. If you plan to audit virtual SQL Servers running in Microsoft failover clusters, see Audit a virtual SQL Server instance for detailed installation and configuration tasks. 

  1. Gather the information necessary to set up your auditing.

    (tick)TaskDescriptionFor more information ...
    (tick)Verify privileges on your Windows login accountEnsure that your Windows login account has sysadmin privileges on all SQL Server instances you want to audit.Permissions requirements
    (tick)Review the list of auditable eventsReview how the audit process works and which SQL events you can audit. Note that you can audit events at the server or database level.How auditing works
    (tick)Identify the items you want to audit on your SQL Server instancesIdentify the audit settings you want to apply to individual instances in your SQL Server environment. These settings should specify which server events you want to collect and report. Remember that the more data you collect, the more overhead is required. SQL Compliance Manager allows you to change your auditing settings at any time to help you make sure you collect exactly what an auditor needs.Server-level audit settings
    (tick)Identify the items you want to audit on your databasesIdentify the audit settings you want to apply to individual databases in your SQL Server environment. These settings should specify which database events you want to collect and report. Remember that the more data you collect, the more overhead is required. SQL Compliance Manager allows you to change your auditing settings at any time to help you make sure you collect exactly what an auditor needs.Database-level audit settings
    (tick)Identify excluded eventsIdentify any events you want to exclude from your audit data.Event Filters
  2. Register your SQL Server instances.

    (tick)TaskDescriptionFor more information ...
    (tick)Register your SQL Server instancesRegister each SQL Server instance that hosts the databases you want to audit.Register your SQL Servers
  3. Enable auditing.

    (tick)TaskDescriptionFor more information ...
    (tick)Enable server-level auditingIf you want to audit your SQL Server instances, enable auditing at the server level.Enable auditing on a SQL Server
    (tick)Enable database-level auditingIf you want to audit your databases, enable auditing at the database level.Enable auditing on a database
  4. Apply regulation guidelines.

    (tick)TaskDescriptionFor more information ...
    (tick)Apply regulation guidelinesApply regulation guidelines to the appropriate audited databases.Comply with specific regulations

     

    Configure filters and test your settings.

    (tick)TaskDescriptionFor more information ...
    (tick)Configure Event FiltersConfigure the appropriate Event Filters, depending on which event category you want to exclude from your audit data.Event Filters
    (tick)Test your audit settingsTest your audit settings to ensure you will collect the SQL Server events you need.Test your audit settings
  5. Monitor your settings.

    (tick)TaskDescriptionFor more information ...
    (tick)Monitor event collection and adjust if necessaryMonitor how many events are collected on a daily basis. Depending on the growth rate of your audit data, consider creating Event Filters to better manage audit data in large environments.Event Filters
    (tick)Monitor the Repository database growthMonitor the growth of the SQL Compliance Manager Repository databases. If the databases are growing too fast, change your auditing settings to limit growth and optimize performance.Reduce audit data to optimize performance
    (tick)Determine whether you need alertsDetermine whether you need to alert on the events you are collecting. SQL CM allows you to build rules that provide real-time alert notifications to help you quickly identify and resolve security issues.Alert on Audit Data and Status
    (tick)Determine whether you need to capture before-and-after object valuesIf you are auditing DML activity, determine whether you want to capture the value of the database object before and after a specific transaction.Audited Database Properties window - Before-After Data tab
    (tick)Determine who needs access rights to administer or report on audit dataDetermine which SQL users should have access rights to administer or report on audit data. This security feature is important as both sensitive and audit data should be secure.Secure Audit Data
  6. Implement reports.

    (tick)TaskDescriptionFor more information ...
    (tick)Review report implementationReview how you can implement Reports in your SQL Server environment using SQL Server Reporting Services.Report on Audit Data
  7. Archive events.

    (tick)TaskDescriptionFor more information ...
    (tick)Archive collected eventsConfigure how you want SQL CM to archive audit data. Note that SQL CM creates an archive database for each registered SQL Server instance.Archive collected events

 

SQL Compliance Manager audits all activity on your server. Learn more > >