CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Creates an encryption key that is used for transparently encrypting a database. For more information about transparent database encryption, see Transparent Data Encryption (TDE).
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Data Warehouse Public Preview.
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.
For additional examples using TDE, see Transparent Data Encryption (TDE), Enable TDE Using EKM, and Extensible Key Management Using Azure Key Vault (SQL Server).
The following example creates a database encryption key by using the AES_256 algorithm, and protects the private key with a certificate named MyServerCert.
USE AdventureWorks2012; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyServerCert; GO