June 2008 - Posts

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.

 

Posted by sqldbatips | with no comments
Filed under: ,

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

Posted by sqldbatips | 5 comment(s)
Filed under: ,

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.

Posted by sqldbatips | with no comments
Filed under: , ,

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.

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

Posted by sqldbatips | 1 comment(s)
Filed under: , ,

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.

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 =

               
    THEN 'Y' ELSE 'N' END AS SpecificationEnabled
    ,            
          
    d.AuditActions
    ,
          
    a.name AS ServerAudit
    ,
          
    a.type_desc AS ServerAuditType
    ,
          
    CASE WHEN a.is_state_enabled =

               
    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 THEN 
    'N' 
               
    WHEN f.reserve_disk_space 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('')), 11''AS 
    AuditActions
               
    FROM sys.server_audit_specification_details AS 
    d1
               
    GROUP BY server_specification_idAS 
    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

Posted by sqldbatips | with no comments
Filed under: , ,

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.

  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.

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.

Posted by sqldbatips | 3 comment(s)
Filed under: , ,

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

 

Posted by sqldbatips | 1 comment(s)
Filed under: , ,

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

 

Posted by sqldbatips | with no comments
Filed under: , ,

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

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

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


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


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

Posted by sqldbatips | 4 comment(s)
Filed under: , ,

Overview

Transparent Data Encryption (TDE) provides a way to encrypt all the data in a database with no changes to applications that access that data. It provides protection for the data at rest by performing encryption as data is written to database files and the transaction log and decryption when it is read from those files. This is done automatically once a database has been encrypted using TDE and no changes need to be made to applications that access the data (hence "Transparent").

A symmetric key called the database encryption key (DEK) is used to perform the encryption/decryption and this is stored in the database itself. This key is secured either with a certificate in the master database or by an asymmetric key protected by an EKM (Extensible Key Management) module. In the example code below we'll use a certificate in the master database to secure the database encryption key.

The following diagram (taken from Books Online) demonstrates the key hierarchy used for encryption and in the example code below we will go through the steps required to enable TDE. It is vitally important to backup and the certificate used to secure the database encryption key. This backup can be used to recreate the certificate on another instance which is required in order to restore or attach an encrypted database.

Key Points

  • Encrypts data as it is written to disk
  • Decrypts data as it is read from disk
  • Encryption/Decryption for data files is performed at the page level
  • FILESTREAM data is NOT encrypted
  • Enabling encryption on a database does NOT increase its size
  • Enabling TDE will prevent instant file initalization which may affect performance during file autogrow
  • Enabling encryption on any database automatically encrypts tempdb which may have a performance impact
  • Transparent to client applications
  • Backups of databases encrypted using TDE are also encrypted
  • Works with log shipping and database mirroring
  • Encrypted data does not compress as well as unencrypted data so backup compression is not very useful for encrypted databases however data compression can still be used effectively
  • You cannot restore or attach an encrypted database to another instance without the certificate used to secure the DEK being present in the master database
  • The same server certificate can be used to secure multiple database encryption keys but a database encryption key is only ever associated with one certificate

Example Walkthrough

  1. In this example we will create a database called TDE which we will then encrypt using a database encryption key secured with a server certificate. First lets create the database and a table with some simple data and then backup the database.

    USE master
    GO
    CREATE DATABASE 
    TDE
    GO
    USE 
    TDE
    GO
    CREATE TABLE dbo.t1(VARCHAR(100
    ))
    INSERT t1(a

    VALUES ('This is some plain text'),('This is some more plain text'
    )
    GO
    BACKUP DATABASE TDE TO DISK = 'c:\TDEUnencrypted.bak' WITH 
    init
    GO
      


  2. Now open the backup file in Visual Studio 2008 and search for "This is some plain text". You'll see (as below) that you are able to find the text in the backup file



  3. Now we need to create our server certificate that will be used to secure our database encryption key. In order to create the server certificate we need to create a database master key in the master database (if it doesn't already exist)

    USE master
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD 
    'sd092735kjn$&adsg'
    GO
    BACKUP MASTER KEY TO FILE = 
    'c:\masterkey' 
        
    ENCRYPTION BY PASSWORD 
    'sfg56565kjn$&adsg213'
    GO
      

  4. We can now create and backup the server certificate that we will use to secure the database encryption key

    USE master
    GO
    CREATE CERTIFICATE DEKCert WITH SUBJECT 
    'DEK Certificate'
    GO
    BACKUP CERTIFICATE DEKCert TO FILE = 
    'c:\DEKCert'
        
    WITH PRIVATE KEY FILE = 'c:\DEKCertPrivKey' 

        
    ENCRYPTION BY PASSWORD 'sd092735kjn$&adsg' 
    )
    GO
      

  5. Now that we have our server certificate, we can create the database encryption key for the TDE database

    USE TDE
    GO
    CREATE DATABASE ENCRYPTION 
    KEY
    WITH 
    ALGORITHM 
    AES_128
    ENCRYPTION 
    BY 
    SERVER CERTIFICATE DEKCert
    GO
      

  6. We could also use SSMS to do this using a new option on the Database context Menu - Manage Database Encryption



    This will bring up the Manage Database Encryption dialog enabling the selection of the server certificate or asymmetric key to secure our DEK with




  7. We now have all the prerequisites for enabling TDE so we can now enable database encryption

    ALTER DATABASE TDE SET ENCRYPTION ON

  8. By setting encryption on, a background task will start encrypting all the data pages and the log file. Since our TDE database is so small this will take very little time however on a larger database this will take considerably longer. BOL has details of the database maintenance operations that are disallowed when this encryption scan is running. To query the status of the database encryption and its percentage completion we can query the new sys.dm_database_encryption_keys DMV

    SELECT DB_NAME(e.database_idAS DatabaseName,
                
    e.database_id
    ,
                
    e.encryption_state
    ,
        
    CASE 
    e.encryption_state
                    
    WHEN THEN 
    'No database encryption key present, no encryption'
                    
    WHEN THEN 
    'Unencrypted'
                    
    WHEN THEN 
    'Encryption in progress'
                    
    WHEN THEN 
    'Encrypted'
                    
    WHEN THEN 
    'Key change in progress'
                    
    WHEN THEN 
    'Decryption in progress'
        
    END AS encryption_state_desc
    ,
                
    c.name
    ,
                
    e.percent_complete
        
    FROM sys.dm_database_encryption_keys AS 

        
    LEFT JOIN master.sys.certificates AS 

        
    ON e.encryptor_thumbprint c.thumbprint

  9. As you can see from the results below, the TDE database is already fully encrypted. Notice also that tempdb is in the result set. When TDE is enabled for any user database on an instance, tempdb is automatically encrypted. This can have a performance impact and is important to take into consideration when implementing TDE




  10. Now that our database is encrypted, if we take another backup and open it in Visual Studio 2008 as in step 2 you will no longer be able to find the string "This is some plain text" as the data is encrypted!

In Part II we'll look at how to restore or attach our encrypted database to another instance by creating a server certificate from the backup we took in Step 4

The complete code for this example is attached to this post

Posted by sqldbatips | 5 comment(s)
Filed under: , ,