Once a security policy is defined, it then needs to be monitored and enforced to ensure compliance. SQL Server provides two features for this: policy-based management and audit.
Introduced with SQL Server 2008, policy-based management (PBM) allows administrators to define conditions for various facets of the database environment. These conditions, combined to form policies, are then evaluated against policy targets on-demand, on-schedule, or automatically as changes take place. For more information on PBM, check out this document in Books Online.
NOTE Many of the facets of interest from a security perspective only support on-demand or on-schedule evaluation. To automate evaluation, consider using the on-schedule option and defining alerts as described in the Configuring Alerts to Notify Policy Administrators of Policy Failures section of the previously referenced document.
SQL Server allows you to define custom policies but also provides access to a number of policies based on established best practices, many of which are security related. Information on importing and employing these best practice policies can be found in this tutorial.
NOTE Many of these best practices are also evaluated through the the SQL Server Best Practices Analyzer and System Center Advisor. If these tools are not part of your administrative toolkit, it’s is well worth exploring them through the provided links.
Finally, PBM, as implemented in SQL Server 2008 and 2008 R2, provides some multi-server capabilities. Still, the Enterprise Policy Management Framework, available on Codeplex and documented in this white paper, extends PBM, making it a more effective tool for administrators of larger environments. The Framework has the added advantage of bringing PBM capabilities to SQL Server 2005 and 2000 deployments.
SQL Server audit allows information related to various events occurring within a database or SQL Server instance to be recorded for later review. Audit works through the definition of an audit (log) to which data is recorded and the identification of events to record to that log through audit specifications. For a more in-depth review of SQL Server audit, please review this document.
SQL Server audit supports three different audit logs: a binary file, the Windows Application log, and the Windows Security log. The Windows Security log is the most secure of these three options as special permissions are required to write to this log. These permissions also make configuration of the Windows Security log as the audit destination a bit more challenging. To read more about configuring the Windows Security log to accept audit records, please review this document.
NOTE If the Windows Security or Application log is used as the audit log, tools such as System Center Operations Manager can be used to consolidate audit information from across multiple servers.
When configuring audit specifications, keep in mind that recording event data to the logs adds overhead to the audited actions. While event data can be queued in memory before being written to the audit log, the best option for minimizing the performance overhead is to simply be selective of the events being audited. With some event types, those events recorded to the audit log can be restricted to those associated with a specific database object and/or user or role.