CREATE ENCRYPTED FILE statement

Creates a strongly encrypted copy of a database file when you cannot use the CREATE ENCRYPTED DATABASE statement. Also creates encrypted copies of the transaction log, transaction log mirror, and dbspace files.

Syntax

CREATE ENCRYPTED FILE newfile
FROM oldfile
KEY newkey
[ ALGORITHM algorithm ]
[ KEY DERIVATION ITERATIONS number ]
[ OLD KEY oldkey ]

algorithm : 
   'AES' 
   | 'AES256' 
   | 'AES_FIPS'
   | 'AES256_FIPS'

Parameters

FROM clause
Specifies the name of the original database file (oldfile).
KEY clause
Specifies the encryption key to use for newfile. The key can be either a string or a variable name. This key must be specified.
ALGORITHM clause
Specifies the algorithm used to encrypt newfile. If you do not specify an algorithm, AES (128-bit encryption) is used by default.
KEY DERIVATION ITERATIONS
Specifies the number of times that the encryption key is hashed. Specify a whole number between 1 and 1000. The default value is 2, which is 2000 iterations. The higher the number, the better security. If you are running the database on a slow machine, then a very high number of iterations could result in the database taking longer to start (once the database is running, there is no performance impact).
OLD KEY clause
Specifies the encryption key for oldfile, if it is encrypted. The key can be either a string or a variable name.

Remarks

The CREATE ENCRYPTED FILE statement is provided for the situation when you need to encrypt a database for technical support purposes and you cannot use CREATE ENCRYPTED DATABASE statement The CREATE ENCRYPTED DATABASE statement is the recommended statement for encrypting a database. However, if the CREATE ENCRYPTED DATABASE statement fails, then you can use the CREATE ENCRYPTED FILE statement. You can also use the CREATE ENCRYPTED FILE statement to create encrypted copies of a transaction log, transaction log mirror, and dbspace files. If you execute the CREATE ENCRYPTED FILE statement against an encrypted database, then you create an encrypted copy of the database with a different encryption key and algorithm.

When encrypting a database, you must execute the CREATE ENCRYPTED FILE statement against the database file as well as against each of the database-related files independently (transaction log, transaction log mirror, dbspace files, if any).

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

If the database has table encryption enabled, you cannot use the CREATE ENCRYPTED FILE statement; use the CREATE ENCRYPTED DATABASE statement instead.

The CREATE ENCRYPTED FILE statement is not supported in procedures, triggers, events, or batches.

When encrypting the database or enabling table encryption in the database, specify an encryption key. When encrypting the database-related files, specify the same algorithm, key, and iteration count for all files related to the database. The software uses Password-Based Key Derivation Function #2 (PBKDF2), which is part of the PKCS#5 standard to protect the key from brute-force attacks. The software repeatedly applies a cryptographic hash to the encryption key. Use the KEY DERIVATION ITERATIONS clause to specify the number of times to apply the hash.

You can change the number of iterations that are applied to the encryption key by using the -kdi option. The minimum number of iterations is 1000 and the maximum is 1,000,000. The more iterations that you apply the longer it takes to create the encryption key and the longer it takes for a brute-force attack to test a candidate password.

If oldfile has dbspaces or transaction log files associated with it and you encrypt those too, you must ensure that the new name and location of those files is stored with the new database. To do so:

Run dblog -t on the new database to change the name and location of the transaction log.
Run dblog -m on the new database to change the name and location of the transaction log mirror.
Execute an ALTER DBSPACE statement on the new database to change the location and name of the dbspace files.

To execute the CREATE ENCRYPTED FILE statement, you must connect to a different database from the one that you are encrypting. For example, connect to the utility database. The database that you that you are encrypting must not be running.

If disk sandboxing is enabled, then database operations are limited to the directory where the main database file is located.

Privileges

Your ability to execute this statement depends on the setting for the -gu database option, and whether you have the SERVER OPERATOR system privilege.

Side effects

None.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following example encrypts the sample database demo.db and creates a new database called demo2.db that is encrypted with AES_FIPS encryption. The new database file is placed in the server's current working directory.

CREATE ENCRYPTED FILE 'demo2.db'
FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\demo.db'
   KEY 'Sd8f6654*Mnn'
   ALGORITHM 'AES_FIPS';

The following example encrypts the sample database demo.db and its transaction log file demo.log.

CREATE ENCRYPTED FILE 'demo3.db'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\demo.db'
   KEY 'Sd8f6654*Mnn';

CREATE ENCRYPTED FILE 'demo3.log'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\demo.log'
   KEY 'Sd8f6654*Mnn';

The new database file and transaction log are placed in the server's current working directory. At a command prompt, use the Transaction Log utility (dblog) to set the new transaction log name since the new database file demo3.db still references the old transaction log file.

dblog -ek Sd8f6654*Mnn -t demo3.log demo3.db

To change the encryption key for a database, create a copy of the database file and transaction log using the new key, as shown in the following example:

CREATE ENCRYPTED FILE 'c:\\temp\\demo.db'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\demo.db'
   KEY     'Sd251072*Mnn'
   OLD KEY 'Sd8f6654*Mnn';

CREATE ENCRYPTED FILE 'C:\\temp\\demo.log'
   FROM 'C:\\Users\\Public\\Documents\\SQL Anywhere
          17\\Samples\\demo.log'
   KEY     'Sd251072*Mnn'
   OLD KEY 'Sd8f6654*Mnn';

The new database file and transaction log are placed in the specified directory. Now you can archive the old database file and its transaction log, and then move the new database file and transaction log to the same directory where the old files were located.