CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
SQL Server 2008 Books Online (November 2009)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

Creates an encryption key that is used for transparently encrypting a database. For more information about transparent database encryption, see Understanding Transparent Data Encryption (TDE).

Topic link icon Transact-SQL Syntax Conventions

Syntax

CREATE DATABASE ENCRYPTION KEY
       WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
   ENCRYPTION BY SERVER 
    {
                CERTIFICATE Encryptor_Name |
        ASYMMETRIC KEY Encryptor_Name
    }
[ ; ]
Arguments

WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }

Specifies the encryption algorithm that is used for the encryption key.

ENCRYPTION BY SERVER CERTIFICATE Encryptor_Name

Specifies the name of the encryptor used to encrypt the database encryption key.

ENCRYPTION BY SERVER ASYMMETRIC KEY Encryptor_Name

Specifies the name of the asymmetric key used to encrypt the database encryption key.

Remarks

A database encryption key is required before a database can be encrypted by using Transparent Database Encryption (TDE). When a database is transparently encrypted, the whole database is encrypted at the file level, without any special code modifications. The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database.

Database encryption statements are allowed only on user databases.

The database encryption key cannot be exported from the database. It is available only to the system, to users who have debugging permissions on the server, and to users who have access to the certificates that encrypt and decrypt the database encryption key.

The database encryption key does not have to be regenerated when a database owner (dbo) is changed.

Permissions

Requires CONTROL permission on the database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.

Examples

For additional examples using TDE, see Understanding Transparent Data Encryption (TDE) and How to: Enable TDE Using EKM.

A. Creating a Database Encryption Key

The following example creates a database encryption key named PacificSales10 by using the AES_256 algorithm, and protects the private key with a certificate named MyServerCert.

USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
See Also

Reference

ALTER DATABASE SET Options (Transact-SQL)
ALTER DATABASE ENCRYPTION KEY (Transact-SQL)
DROP DATABASE ENCRYPTION KEY (Transact-SQL)
sys.dm_database_encryption_keys (Transact-SQL)

Other Resources

Understanding Transparent Data Encryption (TDE)
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)
Encryption Hierarchy

Help and Information

Getting SQL Server 2008 Assistance
© 2010 Microsoft Corporation. All rights reserved.   Terms of Use | Trademarks | Privacy Statement
Page view tracker
Rate the Lightweight library
x
Lightweight builds on ScriptFree (loband) by adding features you've requested: a SearchBox and default code language selection.
Do you like the SearchBox?
Do you like the tabbed code blocks?
How useful is this topic?
Tell us more.
Thanks
x
You're helping to improve MSDN Online.
Feedback
Switch View
Classic
Lightweight Beta
ScriptFree
Switch View