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
Download Latest Version and View Updated usage notes from sqldbatips.com
Download Source Code from Codeplex
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)
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)
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).
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.
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 :-)
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.
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.
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
-
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
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
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 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.
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
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
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
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
-
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(a 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
-
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
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
-
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
-
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
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
-
We now have all the prerequisites for enabling TDE so we can now enable database encryption
ALTER DATABASE TDE SET ENCRYPTION ON
-
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_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN <