70-457 Reviewer #09, Auditing SQL Server 2012

This review topic is still covered under the Optimize and Troubleshoot SQL Server lesson which covers 14% of the exam.

Auditing gives us, the DBA, the means to see who’s doing what and where they’re doing whatever on our SQL Servers. Auditing will enable you to track any activity on a SQL Server instance.

SQL Server Audit

SQL Server Audit enables you to configure auditing at the instance and database level. SQL Server Audit needs to forward this audit results to a target which may be a file or the event log in the host server. If you do select Security Event log, remember that access to this is limited to users who are usually member of the local administrator group. For the Application log however, any authenticated user can read and write to this log but do remember that this is also a vulnerable risk depending on the activity you are auditing.

If you want to use the Windows Security log as target for you audit results, you need to be extra friendly with your domain administrator group as they need to do additional configuration for this in Group Policy. They will need to add additional user rights assignment and configure  Audit Object Access policy for read and write access.

If you do want to save your audit results to a file, the steps will be simpler and you can do it on your own. You just need to provide read and write access to your SQL Service account on the file. Users assigned the Audit Administrator role will need a read and write access while the users assigned the Audit Reader role must only have read access to the log file.

It is highly recommended that you place your audit results target to a separate instance from the you are auditing to minimize the chance that server admins can tamper with the results. Then on the separate instance that you have, make sure to setup only the Audit Administrators and Audit Readers have the appropriate access.

Creating a Server Audit

You can create a server audit from the SSMS or simply run the equivalent script below

( QUEUE_DELAY = 1000,

Create Audit

The important fields you need to input here are the following;

  1. Audit Name – Your name for the server audit
  2. Queue Delay – delay in milliseconds before audit actions are processed. Default value is 1000 ms.
  3. On Audit Log Failure – As with my script above, I selected SHUTDOWN. Here you must enter what must the server do in the event of audit log failure.
  4. Audit Destination – You need to specify the target where you want your audit results to be, options are Application log, Security log or a log file.

Creating a Server Audit Specification

After creating the server audit, you need to create the server audit specifications to include the group and actions which to audit. You can do this in two ways, one is via SSMS or simply run the script below, script is based on the created AppLogShutDown_Audits from the server audit script above.

FOR SERVER AUDIT [AppLogShutDown_Audits]

In the object explorer, from Security > Server Audit Specifications > right click on New Server Audit Specifications, see screenshot below. This is still based on the AppLogShutDown_Audits we created above.

Create Server Audit Specifications

Based on the server audit, you can select from the drop down list which actions you want to include in your audit results. If you select BACKUP_RESTORE_GROUP, all activities with regards to backup and restore operations in your server will be included in your audit result.

With SQL Server 2012 Enterprise Edition, you can specify audit actions at database level. You can use both audit action groups and audit actions with database audit specifications. For more information on different server and database audit specifications you may visit http://technet.microsoft.com/en-us/library/cc280663.aspx.

Important exam tip: To create, alter or drop a database audit specification, security principals must have permission to connect to the database and be assigned either the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permission on the database.

Configuring Login Auditing

To configure login auditing on the Security page of the Server Properties dialog box, right click on the Server instance in the object explorer and go to Properties, as show below. Select which event during login you want to audit.

Configuring Login Auditing

Using c2 Audit Mode

c2 Audit Mode configures the instance to record both successful and failed attempts to access statements and objects as defined by the c2 security standard. On the same Security dialog box (above screen shot), check the Enable c2 Audit Tracing option box. Or you can also run the script below;

sp_configure “show advanced options”, 1;
sp_configure “c2 audit mode”, 1;

Common Criteria Compliance

The c2 audit mode will be deprecated in the future version of SQL Server and will be superseded by common criteria compliance. Common criteria compliance enables security options including login auditing information in the database instance. You can access this login information via DMV sys.dm_exec_sessions but you need to enable the common criteria compliance by running the script below.

sp_configure “show advanced options”, 1;
sp_configure “common criteria compliance enabled”, 1;

Policy Based Management

Policy based management provides you with the means of managing instances across multiple servers. These policies are then you use to enforce configuration standards in all your database objects and properties.

Facets are collection of properties for an object. So for the database this would be auto shrink, auto close auto what have you property. Policies are the set of rules we want to enforce in our servers for the facets we like to enforce on.

Steps to create a policy;

1. From the object explorer drill down to Management > Policy Based Management > Facets

2. Right click on the Facet you want to create a policy based on. Click on New Policy.

Create New Policy

3. Enter the Policy name.

4. Create new condition based on the policy. Click OK to save your new policy.

To test this NoAutoShrinkPolicy we created, try to create a new database with the Auto Shrink property set to TRUE. Then go to your list of policies and open the dialog box for NoAutoShrinkPolicy. You should be able to open a similar dialog box as below.

Evaluate Policy

Your newly created database with the property Auto Shrink set to TRUE should appear in this list as they failed your policy. To correct this Auto Shrink property, click on APPLY. Then check your database property again to verify that the Auto Shrink property is now set to FALSE.

Important exam tip: There is already a policy named Database Auto Shrink so you don’t need to create this one on your own.

If you are using Central Management Servers in your administration task, you can also create policies to delegate this across multiple servers. Also you can transfer policies between instances by using the import and export method.

Learn more about policy based management at http://technet.microsoft.com/en-us/library/bb510667.aspx.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.


Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: