Server and Database Auditing in SQL 2008

[patris1]

http://www.sqldbatips.com/showarticle.asp?ID=133

Overview


In SQL Server 2008 Enterprise Edition, Instance and Database level audit is now a built in function of the Database Engine with its own set of instance and database level objects, Server Audit and Server Audit Specification at the instance level and Database Audit Specification at the Database level (on a side note I'm not sure why they used the prefix Server since these are Instance level objects)

These new objects also have their own DDL commands (CREATE, ALTER, DROP) which we will examine in the later articles. The generation of audit events is extremely lightweight compared to previously available mechanisms and is based on the new Extended Events infrastructure which is designed to have an extremely low overhead even for large numbers of events. It also allows much finer grained filtering of events.

Whilst we had the ability to audit a large number of DDL actions in SQL Server 2005 using the Event Notification infrastructure (I have a sample tool that allows auditing of all schema changes across multiple instances and centralised storage and reporting available here) not all actions were auditable, it was not that straightforward to configure and there was no tool support within SSMS (SQL Server Management Studio).

In SQL Server 2008, all events are auditable including those not available via Event Notifications and configuration is creatly simplified. As we'll see later on, there is also built in tool support for this in SSMS.

The diagram below gives an overview of the various audit objects

Server Audit

objects define the properties of an audit (Queue Delay, Action on Audit Failure) as well as the output Target (File, Windows Application Log or Windows Security Log). You can create multiple Server Audits each of which defines its own Target.

Server Audit Specification

objects define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 Server Audit Specification per Server Audit. You can create multiple Server Audit Specifications as long as each one uses a separate Server Audit.

Database Audit Specification

objects define the individual audit actions or action groups that you want to audit at the Database level including any filters and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for the same database but they need to belong to separate Server Audits.
In the following series of articles I'll cover the three core objects in detail: Server Audit, Server Audit Specification and Database Audit Specification

---

موضوعات مشابه:

• Database Audit Specifications in SQL 2008
• Auditing in Windows Server 2008
• How To Convert Database of SQL Server 2008 to SQL Server 2005

---

[patris1]

http://www.sqldbatips.com/showarticle.asp?ID=134

Overview
In this article I'll cover how to create the Server Audit Object itself and the configuration for the targets of the audit (Application Event Log, Security Event Log or File)
Download Code for this Article
Server Audit Object
The Server Audit object is the first object you create when enabling auditing on an instance of SQL Server 2008. It defines the target for audit events generated by Audit Specifications. In SQL Server 2008 there are 3 possible audit outputs

• Windows Application Event Log
• Windows Security Event Log
• File (local or remote)

When you specify the Application or Security Log, the only options available for configuration are

• Queue Delay - the amount of time in milliseconds that events are buffered before being forced to be processes. To enable synchronous event delivery you would set this to 0. The default value for this is 1000 (1 second)
• Shutdown on Failure - if this option is selected then the instance will shutdown if audit events cannot be written to the target

There are some additional taks that need to be performed to enable SQL Server to write audit events to the Windows Security Log which I'll cover in the example below. They are also well documented in Books Online. Note that writing to the Security Log is not supported on Windows XP.
When you specify File you can configure the folder that the audit files will be generated in, the maximun number of rollover files, the maximum size of each file and whether to reserve the disk space for the audit file if a maximum size is specified. We'll cover the actual DDL for this later on in the example code.
When a Server Audit object is created it is initially in a disabled state. In order for audit events to be written to the target it must first be enabled.
There can be a maximum of 1 Server Audit Specification per Server Audit (you can of course create multiple Server Audits) and 1 Database Audit Specification per database per Server Audit (again you can create multiple Database Audit Specifications for a database but they need to use multiple Server Audits)
Example Walkthrough
In this example we'll configure three Server Audit objects utilising each of the available targets (Application Log, Security Log, File). I'll demonstrate how to do this via TSQL and via SSMS.

1. For the first object we will create a Server Audit object that targets the Windows Application Event Log
   CREATE SERVER AUDIT [ApplicationLog]
   TO APPLICATION_LOG
   WITH
   ( QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
   )
   GO
   
   In this example, we have specified a Queue Delay of 2000 milliseconds and to not shutdown if audit records cannot be written to the application log. Initially this Server Audit is created in a disabled state. To enable it we can use the following command
   
   ALTER SERVER AUDIT [ApplicationLog] WITH(STATE=ON)
   
   To perform the same action using SSMS we would connect to our instance using Object Explorer and expand the Security node, right click on Audit and choose New Audit from the context menu
   
   
   
   This will bring up the Create Audit dialog where we can specify the parameters for our Server Audit Object as shown below
   
   
   
   This will create the Server Audit object in a disabled state exactly as if we created it via TSQL so to enable it via SSMS simply select the Server Audit Object, right click and choose Enable Audit from the context menu
   
   
2. For the second object we will create a Server Audit object that targets the Windows Security Event Log. Before we can do this however, we need to configure a couple of windows settings to allow SQL Server to write events to the Security Event Log. The example below is for Windows 2003, the procedure for Windows 2008 is slightly different and is documented in Books Online. Note that the options that need to be set here may well be set by Group Policy in a domain envrionment in which case local settings could be overwritten. You would need to discuss these settings with the group that manages Group Policy to ensure the required settings remain in effect for SQL Servers where you want to be able to write events to the Security Log.
   
   First we need to enable Audit object access for Success and Failure. We do this using the Local Security Policy mmc snapin (Start>Run>secpol.msc)
   
   
   
   Then we need to assign the Generate Security Audits right to the SQL Server service account. This is done through the same mmc snapin as above by selcting the User Rights Assignment node and double clicking on the Generate Security Audits entry in the right hand pane and adding in the SQL Service account. In this example I'm using a local user account as my server is not part of a domain.
   
   
   
   Once these 2 changes have been made, restart the SQL Server service and you will then be able to create a Server Audit targetting the Windows security log as shown below. Again, this will be created in a disabled state so you will need to enable it exactly the same as for the Application Log example. Note SQL Sever 2008 RC0 has a bug with regard to viewing events written to the Security Log in Event Viewer. See this post for the workaround.
   
   CREATE SERVER AUDIT [SecurityLog]
   TO SECURITY_LOG
   WITH
   ( QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
   )
   GO
   
   
3. For the third object we will create a Server Audit object that targets a local file. There are some additional options available when specifying a file target inluding the folder that the audit files will be generated in, the maximun number of rollover files, the maximum size of each file and whether to reserve the disk space for the audit file if a maximum size is specified as shown below
   
   CREATE SERVER AUDIT [ServerAuditFile]
   TO FILE
   ( FILEPATH = N'C:\Audit\Server\'
   ,MAXSIZE = 100 MB
   ,MAX_ROLLOVER_FILES = 2147483647
   ,RESERVE_DISK_SPACE = OFF
   )
   WITH
   ( QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
   )
   
   In this example we are specifying a target folder of C:\Audit\Server, a maximum individual file size of 100MB, an unlimited number of rollover files and not to reserve disk space when creating the audit files. Note that you can also specify a UNC path for the FILEPATH if you want to write to a remote share as long as the SQL Service account has permissions to the share. Because of the increased latency due to the network you may need to increase the QUEUE_DELAY parameter to avoid impacting the instance. Also with a remote target it may not be wise to specify ON_FAILURE = SHUTDOWN (you can increase the availability of the share by creating it on a cluster
   
   
4. There is one more option available when creating a Server Audit and that is AUDIT_GUID. All Server Audits have an audit_guid (which you can find by querying sys.server_audits) . When you create a Database Audit Specification (covered in Part III) you associate it with a Server Audit. If you restore or attach the database to another instance for example when configuring database mirroring, the Database Audit Specification becomes orphaned because there is no Server Audit on the new instance with a matching audit_guid. Thus by providing the option to specify the audit_guid, you can create the required Server Audit on the new instance. For a Server Audit that uses a File target in a database mirroring environment you will probably want to use a file share rather than a local file and will need to make sure poth the principal and mirror SQL service accounts have access to the share.

So as we have seen from these examples, it is extremely straightforward to create Server Audit objects in SQL Server 2008 using TSQL or SSMS. The Security Event Log target is a good choice to ensure that audit records cannot be tampered with and should also integrate well with ACS (Audit Collection Services) in SCOM 2007 (System Center Operations Manager) or other security log based audit collection systems. Again, as with a number of the new features in SQL Server 2008 it will be interesting to see the performance impact (if any) of the various audit configurations and I'm sure some best practice white papers will be available at some point post RTM.
Download Code for this Article

[patris1]

http://www.sqldbatips.com/showarticle.asp?ID=135

Overview
In this article I'll cover how to create a Server Audit Specification to audit Instance level events. This article is part of a series so if you missed Part I you can find it here
Download Code for this Article
Server Audit Specification
Server Audit Specifications define the audit action groups that you want to audit at the Instance level and the Server Audit it belongs to. There can be a maximum of 1 Server Audit Specification per Server Audit. You can create multiple Server Audit Specifications as long as each one uses a separate Server Audit.
At the instance level you can specify one or more audit action groups (for Database Audit Specifications you can specify individual audit actions and filters as well). Note that actions that modify the audit itself (e.g. disabling or altering audit objects) are automatically audited. There are a large number of audit action groups, to find details on all of them check Books Online.
Example Walkthrough
In the following example we will create a couple of Server Audit Specifications using the Server Audits we created in Part I

1. For the first example we'll create a Server Audit Specification to audit changes to logins using the SERVER_PRINCIPAL_CHANGE_GROUP and use the ApplicationLog Server Audit. If you don't specify the WITH clause then the Server Audit Specification will be created in a disabled state.
   
   CREATE SERVER AUDIT SPECIFICATION [AuditLoginChanges]
   FOR SERVER AUDIT [ApplicationLog]
   ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
   WITH (STATE = ON)
   
   
2. Now lets generate a couple of events that will be audited. We will create a login, alter it and then drop it.
   
   -- create some events
   CREATE login AuditLoginDemo
   WITH password = 'sdkfds*)&(9kdsafk',
   check_policy=OFF
   GO
   ALTER login AuditLoginDemo WITH
   DEFAULT_DATABASE = model,
   DEFAULT_LANGUAGE = British
   GO
   DROP login AuditLoginDemo
   GO
3. To view the audit events that are written to the Application Log we can either use Windows Event Viewer or SQL Server Management Studio. When viewing events using Event Viewer it is helpful to create a filter for only Success and Failure audits to cut down the number of records. When viewing the audit in SSMS this is automatically done for you. To view the events in SSMS, expand the Security top level node, expand the Audits node, right click the Application Log Server Audit and choose View Audit Logs as shown below.
   
   
   
   This launches the SSMS Log Viewer which enables you to easily review audit events written to any of the available Server Audit targets
   
   
4. In the next example we'll create a Server Audit Specification using SSMS that audits the SERVER_OPERATION_GROUP and SERVER_STATE_CHANGE_GROUP audit action groups and uses the ServerAuditFile Server Audit. To create a new Server Audit specification, expand the Security top level node, right click the Server Audit Specifications node and choose New Server Audit Specification. This will open the Create Server Audit Specification dialog as shown below. You need to supply a name and select the Server Audit you want to use. Choose ServerAuditFile from the drop down list. You can then add one or more audit action groups as shown below. Click OK to create the Server Audit Specification.
   
   
   
   As with all the audit objects, the Server Audit Specification will be created in a disabled state. To enable it using SSMS, right click on the Server Audit Specification and choose Enable Server Audit Specification as shown below
   
   
   Here is the equivalent TSQL command for the actions above which demonstrates how to add multiple audit action groups to a Server Audit Specification
   
   CREATE SERVER AUDIT SPECIFICATION [ServerOperationAndState]
   FOR SERVER AUDIT [ServerAuditFile]
   ADD (SERVER_OPERATION_GROUP),
   ADD (SERVER_STATE_CHANGE_GROUP)
   WITH (STATE = ON)
5. Now lets generate some audit events, in this case we will use sp_configure to modify some configuration settings to trigger events in the SERVER_OPERATION_GROUP action group
   
   EXEC sp_configure 'show advanced options',1
   RECONFIGURE
   GO
   EXEC sp_configure 'clr enabled',1
   RECONFIGURE
   GO
6. In order to view the audit events we could use the SSMS Log viewer as in Step 3 above but since the ServerAuditFile Server Audit has a file target, we can also use the new function fn_get_audit_file to view the contents of the file from TSQL as shown below. First we query the sys.server_file_audits catalog view to get the audit folder and then pass that to the fn_get_audit_file function.
   
   DECLARE @folder VARCHAR(255)
   SELECT @folder = log_file_path + '*'
   FROM sys.server_file_audits WHERE name = 'ServerAuditFile'
   
   SELECT * FROM sys.fn_get_audit_file(@folder,DEFAULT,DEFAULT)
   ORDER BY event_time DESC
7. There are a number of new catalog views and DMV's that allow us to query the new audit objects and these are show below as well as a query that will list all Server Audit Specifcations, the audit action groups defined and the asoosciated Server Audit details. For details of the individual views see Books Online.
   
   -- new catalog views and DMV's
   SELECT * FROM sys.server_audits
   SELECT * FROM sys.server_file_audits
   SELECT * FROM sys.dm_server_audit_status
   SELECT * FROM sys.server_audit_specifications
   SELECT * FROM sys.server_audit_specification_details
   
   -- query to list all server audit specifications with details of
   -- the audited action groups, server audits and audit files (if applicable)
   SELECT sp.name AS ServerAuditSpecification,
   CASE WHEN sp.is_state_enabled =1
   THEN 'Y' ELSE 'N' END AS SpecificationEnabled,
   d.AuditActions,
   a.name AS ServerAudit,
   a.type_desc AS ServerAuditType,
   CASE WHEN a.is_state_enabled =1
   THEN 'Y' ELSE 'N' END AS AuditEnabled,
   st.status_desc AS AuditStatus,
   a.queue_delay AS QueueDelay,
   a.on_failure_desc AS OnFailure,
   st.audit_file_path AS CurrentFile,
   f.max_file_size AS MaxFileSize,
   f.max_rollover_files AS MaxRolloverFiles,
   CASE WHEN f.reserve_disk_space = 0 THEN 'N'
   WHEN f.reserve_disk_space = 1 THEN 'Y' END AS ReserveSpace
   FROM sys.server_audit_specifications AS sp
   JOIN sys.server_audits a
   ON sp.audit_guid = a.audit_guid
   JOIN sys.dm_server_audit_status st
   ON a.audit_id = st.audit_id
   LEFT JOIN sys.server_file_audits f
   ON a.audit_id = f.audit_id
   LEFT JOIN (SELECT server_specification_id,
   STUFF((SELECT ',' + audit_action_name AS [text()]
   FROM sys.server_audit_specification_details AS d2
   WHERE d2.server_specification_id = d1.server_specification_id
   ORDER BY audit_action_name
   FOR xml path('')), 1, 1, '') AS AuditActions
   FROM sys.server_audit_specification_details AS d1
   GROUP BY server_specification_id) AS d
   ON sp.server_specification_id = d.server_specification_id
   ORDER BY ServerAuditSpecification

That concludes our examination of Server Audit Specifications. In Part III we will examine Database Audit Specifications
Download Code for this Article

[patris1]

http://www.sqldbatips.com/showarticle.asp?ID=136

Overview
In this article I'll cover how to create Database Audit Specifications to audit database level events. This article is part of a series so if you missed Part I you can find it here and Part II here.
Download Code for this Article
Database Audit Specification
Database Audit Specifications define the audit action groups, individual audit actions and filters that you use to audit events at the database level and the Server Audit it belongs to. There can be a maximum of 1 Database Audit Specification per Database per Server Audit. You can create multiple Database Audit Specifications for a database as long as each one uses a separate Server Audit.
At the instance level you can specify one or more audit action groups. For Database Audit Specifications you can also specify individual audit actions and filters. Note that actions that modify the audit itself (e.g. disabling or altering audit objects) are automatically audited. There are a large number of audit action groups, to find details on all of them check Books Online.
Example Walkthrough
In the following examples we will create Database Audit Specifications for the Adventureworks database using a new file based Server Audits (I won't cover creating the Server Audits in any detail, if you want more details on Server Audits see Part I). You will need the latest version of Adventureworks which can be downloaded from Codeplex here.

1. For the first example, we will create a Database Audit Specification to audit all DML on the Sales.CreditCard and HumanResources.EmployeePayHistory tables. First we will create a new file based Server Audit that we will use for our Database Audit Specification
   
   CREATE SERVER AUDIT [AWDMLAudit]
   TO FILE
   ( FILEPATH = 'C:\Audit\DB\Adventureworks\AWDMLAudit'
   ,MAXSIZE = 100 MB
   ,MAX_ROLLOVER_FILES = 2147483647
   ,RESERVE_DISK_SPACE = OFF
   )
   WITH
   ( QUEUE_DELAY = 2000
   ,ON_FAILURE = CONTINUE
   )
2. We will now create a new Database Audit Specification to audit the DML events for the Sales.CreditCard and HumanResources.EmployeePayHistory tables. Notice that as well as the audit action groups available in Server Audit Specifications, we can also specify individual audit actions and filter them based on individual objects and the user or role accessing them. In this example we will specify the public database role so that DML for all users is captured.
   
   CREATE DATABASE AUDIT SPECIFICATION AWSensitiveDMLAudit
   FOR SERVER AUDIT AWDMLAudit
   ADD (SELECT , INSERT , UPDATE, DELETE
   ON HumanResources.EmployeePayHistory
   BY PUBLIC),
   ADD (SELECT , INSERT , UPDATE, DELETE
   ON Sales.CreditCard
   BY PUBLIC)
   WITH (STATE = ON)
   GO
3. Now lets generate some events and then view the audit file to ensure that the events have been audited
   
   USE AdventureWorks
   GO
   SELECT TOP 10 * FROM Sales.CreditCard
   GO
   BEGIN TRAN
   UPDATE Sales.CreditCard
   SET ExpYear = 2009,ModifiedDate = GETDATE()
   WHERE CreditCardID = 6
   ROLLBACK TRAN
   GO
   SELECT * FROM HumanResources.EmployeePayHistory
   GO
   
   USE master
   GO
   -- get the audit file
   DECLARE @filepattern VARCHAR(300)
   DECLARE @folder VARCHAR(255)
   DECLARE @auditguid VARCHAR(36)
   SELECT @auditguid = audit_guid,@folder = log_file_path
   FROM sys.server_file_audits WHERE name = 'AWDMLAudit'
   
   SELECT @filepattern = @folder + '*_' + @auditguid + '*'
   
   -- view the results
   SELECT a.name AS Action,c.class_type_desc AS ObjectType,
   f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement
   FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f
   JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type
   JOIN sys.dm_audit_actions a ON f.action_id = a.action_id
   AND c.securable_class_desc = a.class_desc
   WHERE f.action_id <> 'AUSC'
   ORDER BY event_time DESC,sequence_number
   
   
4. There are a couple of interesting points raised by the results. As you can see, the update statement is present (twice in fact since the table needs to be read to be able to qualify rows for the update which is why poth the SELECT and UPDATE appear for the UPDATE statement) even though the transaction it was in was rolled back. Also notice that the statement for the update is the auto parameterised version rather than the actual statement text. Database audits do not capture any before or after data images for DML actions.
5. Here are a few more examples demonstrating the additional filtering available for Database Audit Specifications. Note that if you try and create these without deleting the previous Database Audit Specification they will fail because of the limit of 1 Database Audit Specification per Database per Server Audit. Also note that in order to DROP or ALTER a Database Audit Specification if must first be disabled. You can of course simly alter the existing Database Audit Specification by adding the new audit actions and groups.
   
   -- some more examples of database audit specifications
   USE AdventureWorks
   GO
   
   -- audit all execution of stored procedures
   CREATE DATABASE AUDIT SPECIFICATION Example1
   FOR SERVER AUDIT AWDMLAudit
   ADD (EXECUTE
   ON DATABASE::AdventureWorks
   BY PUBLIC)
   WITH (STATE = ON)
   GO
   
   -- audit all updates in the Sales schema
   CREATE DATABASE AUDIT SPECIFICATION Example2
   FOR SERVER AUDIT AWDMLAudit
   ADD (UPDATE
   ON SCHEMA::Sales
   BY PUBLIC)
   WITH (STATE = ON)
   GO
   
   -- audit all schema changes in the database
   CREATE DATABASE AUDIT SPECIFICATION Example3
   FOR SERVER AUDIT AWDMLAudit
   ADD (SCHEMA_OBJECT_CHANGE_GROUP)
   WITH (STATE = ON)
   GO
6. As for Server Audit Specifications, there is built in tool support for Database Audit Specifications in SSMS. If you expand a database node in Object Explorer you will see the new Database Audit Specifications node. To create a new Database Audit Specification, right click on the Database Audit Specifications node and choose New Database Audit Specification as shown below
   
   
   
   This will bring up the Create Database Audit Specification dialog which allows you to select the audit actions and groups and any applicable filtering
   
   

That concludes our examination of Database Audit Specifications and the new audit features of SQL Server 2008 Enterprise Edition. As you have seen, it is extremely easy to configure and administer via TSQL or SSMS. It is also designed to be extremely lightweight and have less overhead than the existing mechanisms (SQL Trace, DDL Triggers, Event Notifications).
Download Code for this Article