sqldbatips.com blog

Updated build of ExpressMaint utility now available

2008-07-04T10:34:00Z

An updated version (1.5.0.0) of the ExpressMaint utility for automating database maintenance operations for SQL 2005 Express Edition is now available. This new version includes the following additions and fixes

Added 2 new database operation types for updating database statistics
Added new switch to allow multi database operations to continue if one or more databases fail
Added fix for SMO Index Reorganise bug (Connect Item 339570) which caused reorg to fail when indexes had non default fill factor

Download Latest Version and View Updated usage notes from sqldbatips.com

Download Source Code from Codeplex

Updated Release of SQL 2005 Service Manager for Vista now available

2008-07-02T14:00:00Z

A new build of SQL 2005 Service Manager is now available for Windows Vista (2.0.0.5). This releases adds compatability for SQL 2008 RC0 and filters the SQL Agent service from being managed for SQL 2008 Express Edition (see why here)

Enabling Hyper-V on Dell XPS Laptop

2008-07-02T12:08:00Z

Now that Hyper-V is RTM (download update here) I figured I'd take the plunge and dual boot my laptop by installing a Windows 2008 x64 partition. All went well and I installed the Hyper-V role and the RTM update. When it came to starting up a VM however I found that it wouldn't start because the Intel-VT extensions were not enabled (they are Off by default). After quite a few scans through the BIOS settings I finally found the correct option (it's under POST Behavior which to me seems to be not very intuitive!). The instructions for enabling the required hardware settings for Hyper-V are below (Dell XPS M1730 with A06 BIOS)

Boot into Setup (F2)
Navigate to Security>CPU XD Support and ensure it is Enabled (it is Enabled by default)
Navigate to POST Behavior>Virtualization and ensure it is Enabled (it is Off by default)

Hope this helps, it took me a while to find this and searching google didn't help (in fact most of the dell forum posts seemed to indicate it required a BIOS update past A06 or wasn't possible).

Updated Release of SQL 2005 Service Manager now available

2008-06-30T08:48:00Z

A new build of SQL 2005 Service Manager is now available for XP, Windows 2003 and Windows 2008 (1.0.0.13). This releases adds compatability for SQL 2008 RC0 and filters the SQL Agent service from being managed for SQL 2008 Express Edition (see why here). Updated sourcecode is available from the Codeplex site. A new build for Vista will be available shortly.

SQL Server 2008 Express installs SQL Agent (but don't bother trying to start it)

2008-06-28T09:00:00Z

If you have installed either of the Express Editions available in the SQL Server 2008 RC0 download you may have noticed that as well as the SQL Server service for the Express instance, setup also installs the SQL Agent service. Great news you might think but no, the service is installed in a disabled state and will error if you try and start it manually. This is a change in behaviour from SQL 2005 Express Edition which did not create the SQL Agent service (or even include sqlagent.exe) and is far from ideal and may be comfusing to customers as confirmed by Microsoft in my Connect Bug for this issue however it also won't be fixed in SQL 2008.

Why do I care (a) because I think its confusing for customers and (b) because I maintain the SQL 2005 Service Manager tool on Codeplex which despite its name does support SQL 2008 and this makes my life more difficult because I now have to check the instance editions and filter out SQL agent for 2008 Express instances (so for totally selfish reasons then!). Along with the reappearance of the FTS service under a new name in RC0 it's making adding SQL 2008 compatability more painful than I had hoped :-)

Workaround for Audit issue when targetting the Security Log in SQL 2008 RC0

2008-06-27T17:30:00Z

In RC0, the registry entry that specifies the message file for audit events that are written to the security event log is incorrect (Connect Item 352787). This means that when viewing audit events written to the security log in Event Viewer you will see the following text in the description (I'm sure you've seen these type of messages before!)

The description for Event ID ( 33205 ) in Source ( MSSQLSERVER$AUDIT ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event:

This should be fixed for RTM however to correct this in RC0, open regedit and browse to the following key (the example below is for a default instance, for a named instance the key format is MSSQL$<INSTANCE NAME>$AUDIT)

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Security\MSSQLSERVER$AUDIT

If you examine the EventMessageFile value you will notice that the path is similar to the one below (this is for a default instance in the default location, if you installed in a different location or have a named instance then the path will be different)

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\1033\Resources\sqlevn70.rll

The highlighted section is actually the wrong way round i.e. the path should actually be

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Resources\1033\sqlevn70.rll

Once you have corrected this and restarted the SQL Server service then the spurious messages will no longer be displayed when viewing the audit events in the security event log.

New in SQL 2008: Server and Database Auditing Part III

2008-06-27T17:15:00Z

In this post I'll cover how to create Database Audit Specifications to audit database level events. This post is part of a series so if you missed Part I you can find it here and Part II here.

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 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 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.

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
)

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

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

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.

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

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 brings us to the end of this introduction to the new built in auditing in SQL Server 2008. 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).

All the code examples are attached to this post.

New in SQL 2008: Server and Database Auditing Part II

2008-06-27T17:10:00Z

In this post I'll cover how to create a Server Audit Specification to audit Instance level events. This post is part of a series so if you missed Part I you can find it here

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.

Example Walkthrough

In the following example we will create a couple of Server Audit Specifications using the Server Audits we created in Part I

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)
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

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

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)

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

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

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

All the sample code is attached to this post. In Part III I'll examine Database Audit Specifications

New in SQL 2008: Server and Database Auditing Part I

2008-06-27T17:05:00Z

In this post 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)

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 actuall 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.

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

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

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)
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.

All the code for the examples in this post are in the attached zip file. In Part II we'll cover how to create Server Audit Specifications and relate them to the Server Audits we created.

New in SQL 2008: Server and Database Auditing Overview

2008-06-27T17:00:00Z

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 code examples. 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 posts I'll cover the three core objects in detail: Server Audit, Server Audit Specification and Database Audit Specification

Microsoft SQL Server 2008 Feature Pack RC0 (June 2008)

2008-06-25T08:00:00Z

The June feature pack for SQL Server 2008 RC0 allows you to download individual componets of the RC0 release (e.g. SMO, command line utilities, SQL Native Client) as well as some additional tools. Most notably, you can download Report Builder 2.0 and SQL 2008 Upgrade Advisor. Just go to the link below and scroll down the page. Note that to run SQL 2008 Upgrade Advisor from a PC that does not have RC0 installed you will need to install Windows Installer 4.5. There are links to the hotfix for this on the feature pack page.

http://www.microsoft.com/downloads/details.aspx?FamilyId=089A9DAD-E2DF-43E9-9CD8-C06320520B40&displaylang=en

New in SQL 2008 : Transparent Data Encryption Part II

2008-06-24T20:11:00Z

In Part I we created the required keys and certificate to enable Transparent Data Encryption for the TDE database. In this post I'll cover how to restore an encrypted database to another instance (e.g. to set up database mirroring or log shipping). In order to be able to restore the now encrypted backup of the TDE database, we need to create the server certificate used to secure the Database Encryption Key for the TDE database (DEKCert). In order to do this we need the backup files we created in Step 4 of Part I.

Example Walkthrough

First we will try and restore the TDE database to another instance without first creating the server certificate. As you will see below, this fails because the server certificate used to secure the database encryption key cannot be found on the new instance.

-- backup database if not already done
BACKUP DATABASE TDE
TO DISK = 'c:\TDE_Encrypted.bak' WITH init
GO

-- try and restore to a different instance
RESTORE DATABASE TDE
FROM DISK = 'c:\TDE_Encrypted.bak'
WITH move 'TDE' TO 'c:\TDE.mdf',
move 'TDE_log' TO 'c:\TDE.ldf'
GO

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xCA741797B81ED8D1305EAE65A747BA51E1DAB80D'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

On our new instance, we need to create the server certificate used to secure our database encryption key for the TDE database (DEKCert). We do this by creating a certificate from the backups we created in Step 4 of Part I (if you don't have the backups simply run the backup certificate part again on the original instance). We need to create the database master key for the master database as in Part I if it doesn't exist before we can create the certificate.

USE master
GO
-- create database master key if it doesn't exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'sdfsdfysd092735kjn$&adsg'
GO
--create TDE certificate from backup
CREATE CERTIFICATE DEKCert
FROM FILE = 'c:\DEKCert'
WITH PRIVATE KEY (FILE = 'c:\DEKCertPrivKey',
DECRYPTION BY PASSWORD = 'sd092rhhh4735kjn$&adsg')
GO

Now that the server certificate used to encrypt the database encryption key for the TDE database is present on the new instance, we can successfully restore the TDE database.

RESTORE DATABASE TDE
FROM DISK = 'c:\TDE_Encrypted.bak'
WITH move 'TDE' TO 'c:\TDE.mdf',
move 'TDE_log' TO 'c:\TDE.ldf',
recovery
GO

Just to make sure, you can query the test table to make sure you can see the data on the new instance

SELECT * FROM TDE.dbo.t1

You can find more details about Transparent Data Encryption in the SQL Server 2008 Books Online. I haven't had a chance to do any performance test of this feature yet but it will be interesting to see what the impact is both for the encrypted database and the instance as a whole (due to tempdb being automatically encrypted)

The sample code for this example is attached to this post