This topic has not yet been rated - Rate this topic

ALTER DATABASE ENCRYPTION KEY (Transact-SQL)

SQL Server 2008 R2

Alters an encryption key and certificate 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


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

Arguments

REGENERATE 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 certificate 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

The certificate or asymmetric key that is used to encrypt the database encryption key must be located in the master system database.

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

After a database encryption key has been modified twice, a log backup must be performed before the database encryption key can be modified again.

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

The following example alters the database encryption key to use the AES_256 algorithm.

USE AdventureWorks2008R2;
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
GO

Reference

Encryption Hierarchy

ALTER DATABASE SET Options (Transact-SQL)

CREATE DATABASE ENCRYPTION KEY (Transact-SQL)

DROP DATABASE ENCRYPTION KEY (Transact-SQL)

sys.dm_database_encryption_keys (Transact-SQL)

Concepts

Understanding Transparent Data Encryption (TDE)

SQL Server Encryption

SQL Server and Database Encryption Keys (Database Engine)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD