Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

CREATE ENCRYPTED FILE statement

This statement encrypts unencrypted databases, transaction logs, or dbspaces. It can also be used to change the encryption key for an encrypted database, or for a database with table encryption enabled.

CREATE ENCRYPTED FILE newfile
FROM oldfile
{ KEY key | KEY key OLD KEY oldkey }
[ ALGORITHM { 'AES' | 'AES_FIPS' } ]

FROM clause    Specifies the name of the existing file (oldfile) on which to execute the CREATE ENCRYPTED FILE statement.

KEY clause    Specifies the encryption key to use.

OLD KEY clause    Specifies the current key with which the file is encrypted.

ALGORITHM clause    Specifies the algorithm used to encrypt the file. If you do not specify an algorithm, AES is used by default.

Use the CREATE ENCRYPTED FILE statement to:

• take an unencrypted database, transaction log, or dbspace and create a new file encrypted with the specified key

• take an encrypted database, transaction log, or dbspace and create a new file encrypted with a new encryption key

The CREATE ENCRYPTED FILE statement produces a new file (newfile), and does not replace or remove the previous version of the file (oldfile).

If a database is encrypted using this statement, you must encrypt the corresponding transaction log file (and any dbspaces) using the same encryption key to use the database. You cannot mix encrypted and unencrypted files, nor can you mix encrypted files with different encryption algorithms or different keys.

If a database requiring recovery is encrypted, its transaction log file must also be encrypted and recovery on the new database will still be necessary.

The name of the transaction log file remains the same in this process, so if the database and transaction log file are renamed, then you need to run dblog -t on the resulting database.

You can also encrypt an existing database or change an existing encryption key by unloading and reloading the database using the dbunload -an option with either -ek or -ep.

If you have a database on which table encryption is enabled, you cannot encrypt the database using this statement. However, you can use this statement to change the key used for table encryption.

This statement is not supported in procedures, triggers, events, or batches.

Must be a user with DBA authority.

On Windows CE, the FIPS algorithm is only supported with ARM processors.

None.

• Encrypting a database
• CREATE DECRYPTED FILE statement
• Unload utility (dbunload)

• SQL/2003    Vendor extension.

The following example encrypts the contacts database and creates a new database called contacts2 that is encrypted with AES_FIPS encryption.

CREATE ENCRYPTED FILE 'contacts2.db'
FROM 'contacts.db'
KEY 'Sd8f6654*Mnn'
ALGORITHM AES_FIPS;

The following example encrypts the contacts database and the contacts log file, renaming the both files. You will need to run dblog -ek abcd -t contacts2.log contacts.db, since the log has been renamed and the database file still points to the old log.

CREATE ENCRYPTED FILE 'contacts2.db'
FROM 'contacts.db'
KEY 'Sd8f6654*Mnn'
CREATE ENCRYPTED FILE 'contacts2.log'
FROM 'contacts.db'
KEY 'Te9g7765*Noo';

The following example encrypts the contacts database and the contacts log file, leaving the original log file name untouched. In this case, you do not need to run dblog, since the name of the file remains the same.

CREATE ENCRYPTED FILE 'newpath\contacts.db'
FROM 'contacts.db'
KEY 'Sd8f6654*Mnn'
CREATE ENCRYPTED FILE 'newpath\contacts.log'
FROM 'contacts.log'
KEY 'Sd8f6654*Mnn';

The following example changes the encryption key of the contacts database.

CREATE ENCRYPTED FILE 'newcontacts.db'
 FROM 'contacts.db' 
 KEY 'newkey' OLD KEY 'oldkey';
 DEL contacts.db
 RENAME newcontacts.db contacts.db;