Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 264

SQL Server 2008 and SQL Server 2008 R2 have a flurry of manageability features in the
database administration arena. In this chapter, we will go through the important features
such as auditing, security, compression, change tracking, policy-based management, data-tier
applications, and SQL Server PowerShell Provider Cmdlets .
Implementing auditing and service level
security features
SQL Server provides various tools and methods to maintain a secure environment and several
methods to involve tracking and logging of events that occur in the system. It is essential for
DBAs and System Administrators to implement a defensive strategy to avoid any breach of
security and meet the necessary compliance to audit the data platform.
The security is an integral part of SQL Server 2005 and 2008. SQL Server 2008 introduces
the native capability of auditing from instance level to database-scoped activity in addition to
C2 audit mode. In this recipe, we will go through the essential steps to implement auditing
and service level security features using SQL Server 2008 R2.
Getting ready
The most common Security features within an Enterprise are classified into the
following methods:
F C2 compliant audit mode
F SQL Server audit mode (fine-grained auditing)
However, the additional security features that are important in implementing auditing are
SSL for login, Transparent Data Encryption (TDE), Certificate-Based Login, and Policy-Based
Management Framework.
In this recipe, we will go through the C2 compliant audit mode that is available in all editions
of SQL Server 2008 R2 including the Express edition.
The fine-grained auditing mode is only available in the Datacenter or Enterprise edition,
for production purposes and in the Evaluation Enterprise or Developer edition, for
testing purposes.
The traditional auditing technique is where an auditing table is created for each base table
that needs to be audited. The underlying mechanism for this method is using a trigger-based
process, which might be cumbersome when the application is mission-critical and highly
transactional. Auditing can also be accomplished whereby a generic table stores the data
needed in conjunction with a lookup table to identify the original column for the audited data.
The following steps are different than the previously mentioned traditional approach, which
is driven on the native capability of SQL Server 2008 R2 features to audit the SQL Server
instance and data-scoped activity.