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
CREATE SERVER AUDIT [AppLogShutDown_Audits]
( QUEUE_DELAY = 1000,
ON_FAILURE = SHUTDOWN
The important fields you need to input here are the following;
- Audit Name – Your name for the server audit
- Queue Delay – delay in milliseconds before audit actions are processed. Default value is 1000 ms.
- 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.
- 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.
CREATE SERVER AUDIT SPECIFICATION [InstanceAudits]
FOR SERVER AUDIT [AppLogShutDown_Audits]
WITH (STATE = ON)
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.
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.
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.
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.
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.